Refreshing a control's listbox

[George]

I have a form, lets call it Form1, that contains a listbox called LenderID
with the ID's of a database table called Lenders. I also have a button on
Form1.cmdLender that when clicked brings up the Lenders maintenance form
that displays fields from Lenders, and I can add a new Lender and enter the
fields for the new Lender.

When I close the Lenders form and go back to Form1, the new Lender entry is
not displayed so I can't select an ID to Store in Form1.LenderID. How can I
cause the ControlSource list for LenderID to refresh and include the
LenderID I just added during the processing for cmdLender?

 

[Dirk Goldgar]

I have a form, lets call it Form1, that contains a listbox called LenderID
with the ID's of a database table called Lenders. I also have a button on
Form1.cmdLender that when clicked brings up the Lenders maintenance form
that displays fields from Lenders, and I can add a new Lender and enter the
fields for the new Lender.

When I close the Lenders form and go back to Form1, the new Lender entry
is not displayed so I can't select an ID to Store in Form1.LenderID. How
can I cause the ControlSource list for LenderID to refresh and include the
LenderID I just added during the processing for cmdLender?

You can go about this either of two (main) ways. You can have Form1 open
the Lenders form in dialog mode, so that further execution of the code on
Form1 is suspended until the Lenders form is closed. Then when it resumes,
the code in Form1 can proceed to requery the list box. Code for this
approach would look like this:

'------ start of code for approach #1 ------
Private Sub cmdLender_Click()

DoCmd.OpenForm "Lenders", WindowMode:=acDialog

Me.LenderID.Requery

End Sub
'------ end of code for approach #1 ------

Alternatively, you can have code in the Close event of the Lenders form
check to see if Form1 is open, and if it is, requery its list box. Code for
this approach would look like this:

'------ start of code for approach #2 ------
'**** CODE IN LENDERS FORM ***
Private Sub Form_Close()

If CurrentProject.AllForms("Form1").IsLoaded Then
Forms!Form1!LenderID.Requery
End If

End Sub
'------ end of code for approach #2 ------

There are a couple of other possibilities, including requerying the list box
(as in approach #2) In the AfterUpdate and AfterDelConfirm events of the
Lenders form, instead of the Close event, so that the list box reflects
changes as they are made. But these are the main ones.

 

[Jack Leach]

Ideally, when you click your button to call up the maintence form, you open
the form in Dialog mode (meaning no code gets executed and the user has to
finish on that form before moving on)...


Private Sub btnAdd_Click()
DoCmd.OpenForm "mainform", , , , , acDialog
End Sub

If you put a line of code after the line that opens the form, the form will
open, and the code will wait until the form closes to continue. There, add
Me.Listboxname.Requery to requery the records of the list box, and you should
see the newly added record.

Private Sub btnAdd_Click()
DoCmd.OpenForm "mainform", , , , , acDialog
Me.ListboxControlName.Requery
End Sub


hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)