How to refresh all possible combo boxes

[Kurt]

My forms have several combo boxes which are populated with values from one
table, tblLookup, using a where clause to filter for values relevant for that
particular combo box. For example:

tblLookup

Label Filter
-------------------
Male SEX
Female SEX
John STAFF
Mary STAFF
Bob STAFF
…

This way I keep all my look up values in one table for ease of management.

If the user comes across a combo box on the form which doesn’t have a needed
value (e.g., they need to add a new staff member to the list), they can click
on a custom menu button which opens frmLookup. frmLookup is a continuous form
that shows all the labels and filters in tblLookup. On this form, they simply
add a new record and enter the appropriate label (e.g., “Kevinâ€) and the
appropriate filter (e.g., “STAFFâ€). Then they close the form and return to
the main form.

The Form_Load code for frmLookup sets the visible property of the main form
to False, and the Form_Close code sets it back to visible.

The way it’s set up now, when the user returns to the main form, the combo
box isn’t refreshed with the updated list of values. He has to close the form
and then reopen it in order to see the updated list.

Because I have so many combo boxes, I would rather not code each one, or put
a button next to each one. Also, because I’d like the user to return to the
screen with the focus set to the control he was working on, I’d rather not
set the code to close the main form entirely and then reopen it.

Is there a way to refresh every possible combo box when the user closes
frmLookup? Or is it a better idea to: When they open frmLookup, save the
record they were working and close the main form, and when they close
frmLookup, reopen the main form (to the same screen/subform/control they
might have been on).

Thanks. (Code for frmLookup below.)

Kurt

Private Sub Form_Load()
Form_frmPatients.Visible = False
End Sub

Private Sub lblClose_Click()
DoCmd.Close , , acSaveYes
End Sub

Private Sub Form_Close()
Form_frmPatients.Visible = True
DoCmd.Maximize
End Sub

 

[Marshall Barton]

My forms have several combo boxes which are populated with values from one
table, tblLookup, using a where clause to filter for values relevant for that
particular combo box. For example:

tblLookup

Label Filter
-------------------
Male SEX
Female SEX
John STAFF
Mary STAFF
Bob STAFF
…

This way I keep all my look up values in one table for ease of management.

If the user comes across a combo box on the form which doesn’t have a needed
value (e.g., they need to add a new staff member to the list), they can click
on a custom menu button which opens frmLookup. frmLookup is a continuous form
that shows all the labels and filters in tblLookup. On this form, they simply
add a new record and enter the appropriate label (e.g., “Kevin”) and the
appropriate filter (e.g., “STAFF”). Then they close the form and return to
the main form.

The Form_Load code for frmLookup sets the visible property of the main form
to False, and the Form_Close code sets it back to visible.

The way it’s set up now, when the user returns to the main form, the combo
box isn’t refreshed with the updated list of values. He has to close the form
and then reopen it in order to see the updated list.

Because I have so many combo boxes, I would rather not code each one, or put
a button next to each one. Also, because I’d like the user to return to the
screen with the focus set to the control he was working on, I’d rather not
set the code to close the main form entirely and then reopen it.

Is there a way to refresh every possible combo box when the user closes
frmLookup? Or is it a better idea to: When they open frmLookup, save the
record they were working and close the main form, and when they close
frmLookup, reopen the main form (to the same screen/subform/control they
might have been on).

Thanks. (Code for frmLookup below.)

Kurt

Private Sub Form_Load()
Form_frmPatients.Visible = False
End Sub

Private Sub lblClose_Click()
DoCmd.Close , , acSaveYes
End Sub

Private Sub Form_Close()
Form_frmPatients.Visible = True
DoCmd.Maximize
End Sub

Well, you could do what you ask, but requerying every combo
box is unnecessary.

Your frmLookup seems to only work with frmPatients and
knowing the form that contains the combo box is sufficient
to determine which combo box had the focus when the tool bar
button was clicked.

You could conceivably have a problem using the
Form_frmPatients type of reference. Instead you should use
the standard Forms!frmPatients kind of reference.

Another problem you have is your use of acSaveYes on the
DoCmd.Close. I suspect that you thought it saved the new
record, but instead this saves the form's design. The new
record will be saved automatically when the form is closed.

Adding all that up, I think your code should be:

Private Sub Form_Load()
Forms!frmPatients.Visible = False
End Sub

Private Sub lblClose_Click()
DoCmd.Close acForm, Me.Name
End Sub

Private Sub Form_Close()
Forms!frmPatients.ActiveControl.Requery
Forms!frmPatients.Visible = True
DoCmd.Maximize
End Sub

 

[Kurt]

Well, you could do what you ask, but requerying every combo
box is unnecessary.

Your frmLookup seems to only work with frmPatients and
knowing the form that contains the combo box is sufficient
to determine which combo box had the focus when the tool bar
button was clicked.

You could conceivably have a problem using the
Form_frmPatients type of reference. Instead you should use
the standard Forms!frmPatients kind of reference.

Another problem you have is your use of acSaveYes on the
DoCmd.Close. I suspect that you thought it saved the new
record, but instead this saves the form's design. The new
record will be saved automatically when the form is closed.

Adding all that up, I think your code should be:

Private Sub Form_Load()
Forms!frmPatients.Visible = False
End Sub

Private Sub lblClose_Click()
DoCmd.Close acForm, Me.Name
End Sub

Private Sub Form_Close()
Forms!frmPatients.ActiveControl.Requery
Forms!frmPatients.Visible = True
DoCmd.Maximize
End Sub

Your code will definitely put me on the right track, but I neglected to
mention that the main form, frmPatients, contains a subform, fsubReferrals;
fsubReferrals contains 2 subforms; and these 2 subforms each contain 2
subforms. So the user might be on any given form or subform when he clicks on
the toolbar button. Consequently, I suspect ...

Forms!frmPatients.ActiveControl.Requery

.... will work only if the active control was on frmPatients.

Perhaps I could repeat that line of code for each subform (substituting the
subform and its location, of course)? Another option might be to handle this
with an If Else statement, along the lines of:

If ActiveControl is on frmPatients Then
Requery it
ElseIf ActiveControl is on fsubReferral Then
Requery it
... etc. for each subform
End If

Thoughts? Thanks.

Kurt

 

[Marshall Barton]

Your code will definitely put me on the right track, but I neglected to
mention that the main form, frmPatients, contains a subform, fsubReferrals;
fsubReferrals contains 2 subforms; and these 2 subforms each contain 2
subforms. So the user might be on any given form or subform when he clicks on
the toolbar button. Consequently, I suspect ...

Forms!frmPatients.ActiveControl.Requery

... will work only if the active control was on frmPatients.

Perhaps I could repeat that line of code for each subform (substituting the
subform and its location, of course)? Another option might be to handle this
with an If Else statement, along the lines of:

If ActiveControl is on frmPatients Then
Requery it
ElseIf ActiveControl is on fsubReferral Then
Requery it
... etc. for each subform
End If

That's not really going to go anywhere.

As long as you are using a tool bar to open frmLookup, you
can keep track of the specific control that has the focus
when the tool bar button is clicked.

Instead of using a macro to open the form, create a public
function in a standard module to do the job. the general
idea is something like:

Public Function OpenLookup(ctl As Control)
Forms!frmPatients.Visible = False
DoCmd.OpenForm "frmLookup", WindowMode:=acDialog
ctl.Requery
Forms!frmPatients.Visible = True
DoCmd.Maximize
End Function

With that in place set your tool bar button's Action to
=OpenLookup(Screen.ActiveControl)

Since the function is taking care of the requery, etc, you
can now remove all that code in frmLookup's Load and Close
events. Also, since this requires that frmLookup be opened
in dialog mode, you may want to skip making it invisible.

 

[Kurt]

That's not really going to go anywhere.

As long as you are using a tool bar to open frmLookup, you
can keep track of the specific control that has the focus
when the tool bar button is clicked.

Instead of using a macro to open the form, create a public
function in a standard module to do the job. the general
idea is something like:

Public Function OpenLookup(ctl As Control)
Forms!frmPatients.Visible = False
DoCmd.OpenForm "frmLookup", WindowMode:=acDialog
ctl.Requery
Forms!frmPatients.Visible = True
DoCmd.Maximize
End Function

With that in place set your tool bar button's Action to
=OpenLookup(Screen.ActiveControl)

Since the function is taking care of the requery, etc, you
can now remove all that code in frmLookup's Load and Close
events. Also, since this requires that frmLookup be opened
in dialog mode, you may want to skip making it invisible.

This solution works great. I need to brush up on functions because I sense
that I'm overlooking a gold mine of possibilities. Thanks again.

Kurt