Update Query on Second Record

[DugyWugy]

Hello List,

I think I've hit a wall.
Here is the scenario:
I have two tables (1 storing the main information and the second storing some of the values from the first (via an update query). I have a form that populates the first table, a button on that form that updates records in the second table via an update query that has criteria specified on the form. The value selected in the combo box on the form is the criteria in the update query (matching work order numbers). Everything runs fine until I want to submit more than one record. There seems to be a delay in posting the second record to the main table. The record gets input, but the update query seems to be executing prior to it getting into the table; therefore the criteria is null and the update query fails to update records in the second table? I use DoCmd.RunCommand acCmdSaveRecord before executing the update query - but it doesn't seem to be working on any of the records other than the first one. If I close the form and go back in it works. It will let me process one record at a time the majority of the time, occassionally it permits two or three entries and then fails.

Any insight and suggestions would be appreciated.

 

[Eupher]

How about posting some of your code? Maybe we can see where it's going off track. Have you tried setting break points and stepping through one line at a time?

Ken S.

 

[IGPCS]

try to add in the apropiate location on the button click
event after the openquery command type this

Me.Refresh
DoCmd.GoToRecord , , acNewRec
Combo1.Requery

hope this will help you

I will try my best to help others so will others do!!!!!
IGPCS
Brooklyn, NY

 

[DugyWugy]

Thanks Eupher and IGPCS,
Sorry I've been tardy in replying. I've been out of the
office for a couple days. Here is a clip of my code:

Private Sub cmdNext_Click()
DoCmd.RunCommand acCmdSaveRecord
Dim qryJobPckgUpdate As String
Dim UpdateJobPckg As String

UpdateJobPckg = "qryUpdateJobPckgInJobTracking"
DoCmd.OpenQuery UpdateJobPckg
DoCmd.GoToRecord , , acNext

LstWONumber.Requery
Me.refresh
Me.Requery

End Sub

It seems as if the code is running faster than the queries
in the background. Sometimes we are able to input two or
three records in a row without having a problem. Usually
we can only input one before the update fails. Here is a
breakdown of the process:
1. Select a work order number from the combo box
2. Fill in other details
(This form is bound to a table)
3. Click a button - this is supposed to execute an update
query, sending some of the data from the bound table into
a related table (using the work order number selected in the combo box as criteria: only update the record that matches the work order number selected in the combo box).
4. Repeat the process as additional records are input.

Problem: It seems as if the record is getting posted to the table late and the update query has already executed. Like I said before, it works on the first one and occassionally two or three times in a row then fails. Usually it only allows one record to be input and the user has to close the form and re-open it to continue.

Thanks,
Doug