Dmax (1 Viewer)

[w0od0o]

Hi, the problem i have is i have a main form "contract" with a subform "jobs" i have an append query to add the job details from a quote to contracts which works fine but i cant get it to run the following vb

Me.job_number = DMax("[job_number]", "jobs") + 1

it works fine if i add the data direct to the jobs forms but i am unsure where to put the above command, i have tried afterupdate and afterinsert but no success, basically after i have appended the job or while im appending i want it to add the next job number.

hope this makes sense

w0od0o

 

[vbaInet]

Since you're simply adding one to the Job Number, is it not set as an Autonumber field?

 

[Mr. B]

You could put your code in the On Current event of your form but you would need to check for new record also.

If Me.NewRecord = True Then
Me.job_number = DMax("[job_number]", "jobs") + 1
End If

This will cause the code to only run if you are on a new record.

 

[w0od0o]

Mr. B i have tried what you suggested but after adding the appended data it assigns the next job number to a new blank record

 

[Mr. B]

I may not have understood just what you are doing with your form. Are you always adding a new record to the form each time you open it. The code should only try to add the new job number if you are adding a new record.

Is it possible for you to post a portion of your database so we can see what is going on?

 

[w0od0o]

added database,

 

[Mr. B]

Take a look a the On Load event of the Contacts form. I have made a modification to the code. I will now create the first number as a 1 and subsequent numbers incremented by one for each new record.

The problem is that you are not moving to a new record before trying to add the new number.

You are simply opening the form to the first record.

 

[w0od0o]

hi Mr.B thanks for that, from what i can see you have changed the code on the contracts form which makes a lot more sense than what i had although the problem i am having is assigning a "job_number" to the jobs table when i import the jobs from a quote.

many thanks

w0od0o

 

[Mr. B]

How and where are you doing the import? I did not see that part.

 

[w0od0o]

sorry probably my fault as i removed all my test data, if you add a quote to contracts then it appends the "quote_details" to the "jobs" table, also if a contract has been generated and additional work is assigned then "extras" is appended to "jobs" by clicking the import jobs button on the contracts form, it will add the details from both appends but for some reason i can not get it to give it the next "job_number"

thank you very much for your help

w0od0o

 

[Mr. B]

I'm sorry but I still do not see any Import Jobs button on the Contract form.

Do you have code that is opening a recordset and then importing those records or are you just runing a query to do the import?

 

[w0od0o]

sorry my fault again :S, the import jobs button is on form "contract_view" to import the extra jobs and when adding a quote to contracts the form "contract" runs the query "append_jobs_quote" on load,

hope this makes sense

w0od0o

 

[Mr. B]

Is it poosible for you to send a database file with some sample data? It is really hard when I don't know all about this.

 

[w0od0o]

sample data added, i have added 1 quote and then added that quote to contracts, as you can see in the jobs table it has imported the job but with a blank job and no job number

thanks

w0od0o

 

[Mr. B]

Try running your "append_jobs_quote" query in the attached version.

I now have a user defined function in a column of the query that will create an incrementing Job Number.

The best I can tell, it works, but you need to test it.

The function is defined as a Public function in the new module "modUtilities"

 

[w0od0o]

hi Mr. B, it seems to work fine but with one slight problem, it appends the data fine but for some reason it assigns the same job number to all that you append rather than each job having an unique number,

also an additional favour, i have added your method to append any extras to the contract and wondered if there was anyway to stop adding the data twice?

thank you so much for your help

w0od0o