Pfthb

I have a form (frmDropDownEdit) that has a filtered table as the data. A "New" button is created that opens another form (frmDropDownNew) and the user can enter new data. When complete the new form is closed and the user is back to the original form. The code for frmDropDownNew correctly add the info to the table, then the code refreshes the frmDropDownEdit form but it does not refresh. If I click the refresh button in the ribbon, it also does not refresh. But refresh all does work.

How can I have my code refresh the data in frmDropDownEdit. I also put code me.refresh on the OnGotFocus event but that does not even run.

Here is my source code

Private Sub Command5_Click()
'Add Button

Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("tblDropDown")
rst.AddNew

rst!DdCategory = Me.txtCategory.Value
rst!DdDescription = UCase(Me.txtDescription.Value)

rst.Update
rst.Close

DoCmd.Close
Forms!frmDropDownEdit.Refresh

End Sub

On my MS Access 2010 ×64 single user PC, Forms![AnyForm] .Refresh never worked in VBA, independently where it is placed in any database's code. Form_Current() doesn't run either as it should after data are modified (verified by putting a Stop therein). Moreover, records with modified data are neither marked dirty nor refreshed before the vba code has finished. Procedures which should run without delay when data are modified don't run, even when placed into the modified fields' events.

Thus, one has to use a work-around. Many people recommend to use .Requery instead of .Refresh and then to return by vba code to the desired record, but this requires a field with a primary key.

My solution for tables without primary key is the following:

'…
' ESSENTIAL: this code must be run from ANOTHER module !
' (it runs without error in MyForm's own code [eg. in Form_Activate(),
' but then MyForm is NOT immediately refreshed as desired,)
' one still has to repeat these steps by hand afterwards…
DoCmd.GoToRecord acForm, "MyForm", acNext
DoCmd.GoToRecord acForm, "MyForm", acPrevious
' NB: Forms![MyForm].Refresh doesn't work
' at this place in "MyOtherModule" either.
'…

As mentioned in above code comments: this code must be run from another module ("MyOtherModule") - in my case, a form-independent procedure called upon closing a pop-up form opened from the first form, which interactively modifies data. These data should be updated/refreshed immediately when closing the pop-up form, reflecting all changes and their consequences (for example, automatic filling-in/deleting of other data and/or en/disabling controls or making them [in]visible, depending on the modified fields' values).