Dynamic Insert Query

[swk003]

Hi this may be a stupid question but need some assistance. I am developing a form to validate 174 records. When all 174 records have been cross checked with logging table (table will conatin more than 174 records each record will be referenced to a particular batch number), I then need to insert a ref number to all 174 records once validated. Now I am hoping that this can be done dynamically. Can anyone suggest a solution for this?

thanks

swk003

 

[PHV]

How are the 174 records validated ?
How is the ref number calculated ?
A starting point :
DoCmd.RunSQL "UPDATE theTable SET theField=" & lngRefNum & " WHERE fldValidated=True")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

You may want to approach this from a design perspective.

Include a "ReferenceNo" as a field in the table design. When a "record" is cross checked and validated, an event process can be used to update the record to include the batch number.

PHV has given one way to approach this, but more specifics would be helpful to provide a more detailed answer.

Consider the following scenario.

The records are displayed in a contineous form, perhaps as a subform. The "BatchNo" is accessable, perhaps from the header or main form.

The end-user then double clicks on each "record" after verification. The double click event procedure is used to copy the "BatchNo" to the "ReferenceNo" field on the "record".

Or if you are using a "batch update", a command button that runs a command similar to the ne PHV presented would again update / copy the "BatchNo" to the "ReferenceNo" field for all records in the specific batch.

Richard

 

[swk003]

Thanks for speedy response. I will try and clarify what I need to do and add some of my basic code:

User opens logging form and bleeps 174 PID numbers into form, each PID number is cross checked with a basetable using beforeUpdate event and DLookup(), if PID not in basetable programme will put the PID into an errorlog table and user continues bleeping records until reaches 174. The 174 records are counted using an infinite counter and box number (batch number) and box position are derived from the counter and inserted into a logging table along with the validated PID. Once 174 PID saved to logging table, a new validation form is opened and all 174 records are validated again (so none are missed!!). Once all 174 have been validated I need to bleep a barcode (External Box number) that can be inserted to all 174 records. This is where I am a lttle stuck as not quite sure how to go about this. Should I use a text box or any other suggestions???

swk003