Append Query - need data from another table

[crystalreporting]

I have a simple form. When the values on the form are filled in I have a button with an event that runs a macro that contains several append/update queries.

One of the append queries adds a record containing the informtation entered on the form. Within the table being written to is a document number. The next document number is held in a separate 'control' file. The append query needs to lookup the control file to establish the next document number to use when appending the record. How do I do this?

My final query in the macro updates the control file and increments the next document number field.

I am wrting my application over an exisiting database so using an autonumber or some other method instead of the control file is not an option.

Thanks! Peter.

Peter Shirley

http://www.srhconsulting.com

 

[Remou]

Perhaps something like:

[tt]intNextDoc=DMax("DocID","tblDocuments")
strSQL="INSERT INTO tblTable ( DocID, Field1 ) " _
& "VALUES ( " & intNextDoc & ", '" & Me.txtText1 & "' )"
DoCmd.Run strSQL[/tt]