Easy way to trigger update of controls

[Hajo]

Hello,

on my form I have a ListBox which represents a few columns of a certain
table. After selecting one row I might click on a button which open another
form and a new RecordSource is set. On this form all detailed entries of the
selected recordset of the ListBox are shown.

Now I modify some record entries and save the changes. And in fact, the
modifications are written in the database. But now the ListBox needs to be
refreshed, because there are still the old values in it.

Well, I can do that by DoCmd.Requery, but the question is when shall I set
this command. I invoke the second form (where I edit the entries) like this:

Private Sub cmdEditEntry_Click()
DoCmd.OpenForm "frmEditEntry"

strSQL= "SELECT ..."
Form_frmEditEntry.RecordSource= strSQL
End Sub

So, where to put the Requery command?

Thank you very much for your help

Hajo

 

[Graham Mandeno]

Hi Hajo

Don't use DoCmd.Requery. It is much too nonspecific.

Instead use [ListBoxName].Requery

If you want to requery when the second form closes, put this code in the
Unload event procedure of frmEditEntry:

Forms![original form name]![listbox name].Requery

HOWEVER, I'm guessing that your RecordSource SQL only varies by the WHERE
clause, so you would be better to pass a WhereCondition (4th argument) to
OpenForm:

DoCmd.OpenForm "frmEditEntry", , , "[SomeFieldName]=" & [ListboxName]

Now, if you open your form in dialog mode, the code in your calling
procedure will pause until the opened form is closed (or made invisible):

.... using named arguments so as not to count commas:

DoCmd.OpenForm "frmEditEntry", _
WhereCondition := "[SomeFieldName]=" & [ListboxName], _
WindowMode := acDialog
' when the form closes, requery the listbox
[ListboxName].Requery

 

[Hajo]

Dear Graham,

thanks a lot that was very helpful!