Replies

Many thanks Mark and I am going to start declaring variables from now on also.

Being virtually self taught, I have established a lot of bad habits that I need to correct, this being one of them.

Many thanks Melissa for the answer. So simple when you know!

The misconceptions that these americans have!!!

Regards,

In a case of “I don’t know how I did it but I know I did it” I have achieved what I wanted.

In reference to the notinlist thing:

With a combo box, the data in it can be limited to the list, that is, to the table or query behind it. With Limit to List turned off, it allows for non list items to be entered and saved in the table behind the form but not in the data source of the combo box.

My form and associated table is for quotes. The customer is selected from the combo box or a new customer name can be added. This info is written back to the underlying table.

If I use a query to base my merge on, I can get the actual names of existing customers but new customers do not show up. (We do not want new customers to be added to the customerlist (which would solve the problem) until the quote is actually accepted and the job becomes live.)
If I pull direct from the table, I get the new customers, but only get the linking accno of the existing customers.

By playing around with the order of the fields in the combo box I was able to achieve what I wanted. (I think!)

Apologies about my short earlier post. I have JUST got back my internet access at work and I was trying to post the above on another machine. I wrote the 3 words and did a test post which worked and then attempted to edit it but the site crashed it said and then later I could edit the message.

With a combo box

Thanks all for the above pointers. I have the example and I’ll see what I make of it.

Back from a few days away, thanks for the code snippet Ricky, I will attempt to try and make sense of it and see if I can make it work.

I have set it up so that the form gets its data from a table, rather than calculating it on the fly. I had started with my 9 values on my main form being updated via dlookup, but this caused a significant pause in loading, so I was out to populate a table with values being updated at significant events other than at loadup time so that the main form loaded without a noticable pause.

I have actually achieved what I wanted, thanks to the pointers from here and the help files.

I have set up a macro to run an update of the hours totals and the average of work done over all the current jobs, that is activated when a new job is added and when a job is closed.

My problem was not knowing how to write the update query properly so that it took values from a table that was not related to the output table and performed calculation on them before updating the output table, but as I said, I finally worked it out.

I have set a timer event to refresh the values periodically.

I’m sure you are right but I don’tt know how to do it.

This is what I want.

I have a table called Original that keeps details on each job.
There is a yes/no field for [Finished] and a number field [pdone] for a value showing percentage done.

I have a bar graph that shows on the main form, that shows Total percentage of current jobs finished, that gets its value from [HoursTable].[JobPercent].

The JobPercent value is Sum([Original]![pdone])/Count([Original]![pdone])WHERE[Original].[Finished]=False.

I want to be able to run an update query that calculates the above value and updates [HoursTable].[JobPercent]

I have gone this way rather than a calculated dlookup as the dlookup takes so long on our slow machines.

The coding needed will be most appreciated.

Thanks for the input, the machine is running NT workstation.

Charlotte,

the job table has a column called totalhours that keeps a running total of the hours spent on the job. This value is updated daily from the work report from the previous day.
This table also feeds a form that allows the details for the job to be viewed.
Someone had one of the records that I wanted to update open on another machine.
The update needs to be done between 8-9am each day.

I have to learn to write error trapping into my codes for this sort of situation.

Ricky,

The solution you gave me is fine and is the one I have implemented.

My brain was working hard looking for alternative ways to achieve it while I was waiting for replies and I tried the above line of code and couldn’t work out why it didn’t work, so I posted the question.

I now know how it should have gone and I consider myself well in front with a bit more knowledge under my belt.

Sorry if you felt as if I’d gone elsewhere without coming back to you first.

(Where are the codes for all the little emoticons??)

Regards, Allan

Thankyou Charlotte for your explanation of my problem.

The C or 65 was meant to be added information, showing that the field in the table had values starting with either 65 or C. What I am trying to achieve is to find the highest value that starts with a C. Sorry if that wasn’t clear.

Once again, I have learnt a little more of the intricacies of this amazing program!

Regards, Allan

Many Thanks Jack and to Ricky as well,
That bit of code was just what I needed.

How would I cope without places like this!!!

Regards, Allan

Unfortunately, the table is well under way and the codes are mixed in together

eg
650012
651059
C00359
650126
etc

The 65???? codes are not set by me.
As cash sale customers move to Accounts, their C code is changed to a 65 code with no relation between them.

How do I write a Dlookup or SQL code to return me the highest value of the C codes. If I can do this all in code, I can increment it as above and then assign it to the text control. (Can I???)

Regards, Allan

Thanks Rory, The not in list function allows me to get the NewCustomer box up ok.

I still get the “Text not an item in list” message, is there a way to suppress this”

Also, can you help with the requery if the combo box data so that I don’t have to close the NewJob form and reopen it in order for the new customer to show.

Allan