Refreshing a form with VBA without full Requery

[Tskutnik]

Background:
I have a form "Form" that contains a data entry popup form "Popup". The data entered into "Popup" populates a table and through a query "Query" the important information from that table populates a field "Field" back into "Form". There are 1900 records in the database, the information from each shows up in a single "Form".


Issue:
After the data entry into "Popup" is done and "Popup" closed (using a button) the "Field" in "Form" does not update.
I tried .requery VBA in the "Popup" close button On Exit, which works, but "Form" always resets to the first of the 1900 records in the database.


What is the code to Refresh/Requery "Form" when "Popup" closes without resetting the form back to the first record?

 

[boblarson]

What is the code to Refresh/Requery "Form" when "Popup" closes without resetting the form back to the first record?

It still involves .Requery but by first setting a variable to the ID field of the record.

Dim rst As DAO.Recordset
Dim lngID As Long
 
lngID = Forms!YourFormNameHere!YourIDFieldHere
 
Forms!YourFormNameHere.Requery
 
Set rst = Forms!YourFormNameHere.RecordsetClone
 
rst.FindFirst "[IDFieldHere]=" & lngID
 
If rst.NoMatch Then
   Msgbox "An error occurred - record not found (" & lngID ") Contact your support person", vbExclamation, "Error"
Else
  Forms!YourFormNameHere.Bookmark = rst.Bookmark
End If
 
rst.Close
Set rst = Nothing

 

[Tskutnik]

OK - thanks. Before I try this is it easier to have the "Popup" close and refresh just the "Field" on the "Form"?

If the refresh/requery was done only on that one "Field" it may solve the problem since only that one field is impacted by the "popup" entry - but I dont know the code to do a field specific update from a different form.

 

[boblarson]

OK - thanks. Before I try this is it easier to have the "Popup" close and refresh just the "Field" on the "Form"?

If the refresh/requery was done only on that one "Field" it may solve the problem since only that one field is impacted by the "popup" entry - but I dont know the code to do a field specific update from a different form.

What do you mean? Is that a combo box and you are just trying to get the value added in? If so then yes you would just requery it:

Forms!YourFormNameHere.ComboNameHere.Requery

and just know that Refresh and Requery are two different things and they don't do the same thing. Refresh will show changes to existing records that existed when you opened the recordset but it will not reflect new records or deleted records. Requery will do all of that.

Also, if the popup is due to wanting to add a new item to a combo box, and you are using Access 2007 or 2010 you can do all of that without code.

 

[Tskutnik]

It is not a combo box, but a value that gets incrementally updated each time an entry is made into the "Popup". I tried:

Forms![Form].[FieldName].Requery in the On Exit event in the Popup close button and nothing happened. I know that code in the Popup's On Exit event will update the Form, so the code is in the right spot.

I can't seem to get the right code.

 

[boblarson]

How are you incrementing that value? And the requery would happen after the other code in the CLOSE button's CLICK event not an On EXIT event.

 

[Tskutnik]

The value is incrementing with data entered in the popup.
1) the popup fields are completed by the user
2) user clicks the submit button; an append query is triggered to load the entered data into a table, and the assocaited queries that read that table automatically update.

These steps work fine.

The query that the form is based on does have the updated value, but the form itself is not showing the update.

I added the update code to the end of the submit button code, after it kicks off the append and other queries.
The code in the Popup's Submit button includes the following:

Forms![FormName].[FieldNameOnFormThatNeedsUpdate].Requery