Update Query will not show Changes in FORM!

C

coffeeman

Registered User.
Local time
Today, 12:26
Joined
Oct 13, 2008
Messages
58
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!
 
If I understand you correctly, you need to requery the form's recordsource after your update. The syntax is simply

Me.Requery
 
To expand on what missinglinq said, when you open a recordset you have a picture of those records at that point in time. Updates will be reflected in the recordset but not immediately. Forms are set to refresh at intervals so if you wait long enough, you should see the value change. Missinglinq suggested a requery. That will work fine if you are using a recordset containing a single record. However, if you are using a recordset that contains multiple records, which it sounds like you are, requery will also reposition the recordset back to the first record. That means that if you need to maintain your current record position, you need to save the PK of the current record, requery the recordsource, and then use the recordset clone to reposition to the record ID you saved.
 
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!
 
Coffeeman, can you upload a copy of your db? it would be a lot easier to work out where the problem is.
 
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!
 
Requerying a form that is in data entry view will act as though you reopened the form and so your recordset will be empty.
 
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!!
 
Remove your code that tries to go to a new record since it is no longer necessary.
 
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!
 
You don't need the code in the click event at all since the other event code is repositioning the recordset pointer.
 
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!
 

Attachments

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
 
You must log in or register to reply here.

Similar threads

H
Solved IsNull not working in query
2
Replies
21
Views
477
hrdpgajjar
H
A
Simple warehouse data entry
Replies
12
Views
694
arnelgp
arnelgp
N
Combobox requery
Replies
3
Views
269
Gasman
Gasman
D
Clearing data in a form after clicking print report
Replies
10
Views
547
Ken Sheridan
Ken Sheridan
HavingDatabaseRelations
Solved Combo Box will only work if Access is not minimized
Replies
10
Views
433
Mike Krailo
Mike Krailo

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom