Update Query will not show Changes in FORM!

[coffeeman]

I have a production form that I enter data into daily. On this form, I have a combobox that autopopulates a lot of the fields. I have also designed a command button for each material used that I can click and update some of my receiving of materials tables.

I will click this command button when a certain material's lot code (based on earliest date received) is completed. This query does an update to the receiving table and knows to go to the next lot code of the material for next use.

When I do this on the form and go to the table, the table is updated correctly. If I then close the form, and reopen it, the selected material shows the new "updated lot code"

All of it works fine and update. The problem is when I open the form and enter data in to it, I "go to next record" when I enter in one product's info and materials used. If I click the Completed Lot Code button, the query runs. When I go to next record, the form doesn't open and close. If I need to use the same material that was selected in the previous record, the old lot code that should be completed comes up.

It is like everything updates, but in order for the form to register the update, I have to close and open it back up.


Does anyone know how I can avoid this or "refresh the form"?

THANKS!

 

[missinglinq]

If I understand you correctly, you need to requery the form's recordsource after your update. The syntax is simply

Me.Requery

 

[coffeeman]

Alright, I understand what you are talking about.

I am using the multiple records recordset I believe. I have a table that has records saved in it from 10 years of history. When I open the form and enter in (for example, 4 records), a number at the bottom of the form will say 4 of 4. I can go back and forth through the records that I have entered in for that day (unless I close the form and reopen it).

I understand what you both are saying, but where and what do I need to enter to get this to work?

THANKS AGAIN FOR THE HELP!

 

[Tanya]

Coffeeman, can you upload a copy of your db? it would be a lot easier to work out where the problem is.

 

[coffeeman]

OK, I cannot put my db on here for confidentiality reasons.

I did do the Me.Requery event After Update and the form is definitely reloading and pulling the next available lot code for a completed selection.

I no longer have 4 of 4 records or can go back to a previous record for a change. This is not a huge deal, but is there a way around this?

Also, I didnt create this particular form, but there is a message error I am getting now stating "You can't go to the specified record" after I click on the button that records the data into a table. I think it has to do with the fact that the ENTER button I would click would do a procedure to go to next record and now there is no record. Is there a way to change this so the error does not come up?

THANKS FOR ALL THE HELP!

 

[coffeeman]

That is fine, I dont have to have the access to a previous record to make changes. I can always just go to the table it is populating into and manually change it.

What about the error? Is there a way to change the ON CLICK button to not try and go to next record? (Since now it is running a REQUERY, there is no next record)

Thanks!

By the way, I am adding to all of your reputation! Very helpful!!

 

[coffeeman]

Yea, not sure where it is. Do you know of an example of what the code may look like and what I should change it to?

I still want the ENTER button to record to table. Here is the code for the ON CLICK of the button:

Private Sub Record_Production_To_Table_Click()
On Error GoTo Err_Record_Production_To_Table_Click

DoCmd.GoToRecord , , acNewRec
Exit_Record_Production_To_Table_Click:
Exit Sub
Err_Record_Production_To_Table_Click:
MsgBox Err.Description
Resume Exit_Record_Production_To_Table_Click

End Sub

I am guessing it is just the part I put in bold above. But I wanted to make sure.

Thanks alot, PAT!

 

[coffeeman]

THANKS PAT!!! It works just like you said it would.

 

[coffeeman]

Hey guys,

It has been awhile. For some reason, I cannot get this to work. It is something I thought I had working, but now I cannot get the fields in the form to update if I complete a certain lot code number for a certain selection unless I close, then reopen it.

I have attached my database. If you go to the switchboard and select "Daily production" then "Daily Production Entry". The form that I am talking about will come up.

Just simply select an item code and enter in a total units produced number. Then go to film and hit the completed button. When you ENTER the data, the lot code for the film that was selected should change in the drop down list. It doesn't. If you click the drop down list, it will say NO next to it, meaning it is not the current lot code, but it doesn't show the next lot code that would be available. It will however change once you exit and reload the form up.

Thanks again for everything!

 

[coffeeman]

Well. I never got a response so I decided, at least for the time being, to make the Enter Data to Table button to close the form and then reopen it. Here is a sample of the code if any one has a similar issue and needs to reload their form to have their entries update:


Private Sub Record_Production_To_Table_Click()
On Error GoTo Err_Record_Production_To_Table_Click
Me.Requery
DoCmd.Close
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Daily Production Report"
DoCmd.OpenForm stDocName, , , stLinkCriteria
Exit_Record_Production_To_Table_Click:
Exit Sub
Err_Record_Production_To_Table_Click:
MsgBox Err.Description
Resume Exit_Record_Production_To_Table_Click


End Sub