Refreshing a combo box based upon a database file w/o exiting the

[Guest]

I created a combo box on a form based upon a query over the employee master
file, listing the employee number and employee name. The employee number
updates a field in the Continuing Education Detail file.

The problem I am having is when they are working on entering the continuing
education detail and come across a new employee. It happens quite often.
They open the employee master file form and enter the new employee, but it
does not come up in the combo box to choose it in the continuing education
detail form without closing out of both the employee master file form (even
though the record has been saved to the employee master file) and continuing
education detail form and reopening the continuing education form.

Is there a way that the combo box can be refreshed each time it is chosen on
the field in the continuing education detail subform without having to exit
and reopen both forms?

I have looked in my books and can not find the answer.

 

[Dirk Goldgar]

I created a combo box on a form based upon a query over the employee
master file, listing the employee number and employee name. The
employee number updates a field in the Continuing Education Detail
file.

The problem I am having is when they are working on entering the
continuing education detail and come across a new employee. It
happens quite often. They open the employee master file form and
enter the new employee, but it does not come up in the combo box to
choose it in the continuing education detail form without closing out
of both the employee master file form (even though the record has
been saved to the employee master file) and continuing education
detail form and reopening the continuing education form.

If the record has truly been saved on the Employee Master form, I doubt
that you actually have to close that form. Of course, closing that form
is a good way to ensure that the record has been saved, but there are
lots of other ways. The problem is really with the Continuing Education
Detail form, and finding a way to requery the combo box without closing
the form.

Is there a way that the combo box can be refreshed each time it is
chosen on the field in the continuing education detail subform
without having to exit and reopen both forms?

I have looked in my books and can not find the answer.

This could make the combo box a bit slow to respond, if there are lots
of employees. But if you want to do that, you could use code in the
combo box's GotFocus event, along these lines:

'----- start of example code -----
Private Sub EmployeeNumber_GotFocus()

Me!EmployeeNumber.Requery

End Sub
'----- end of example code -----

Note that, if the focus has already entered the EmployeeNumber combo
box, and then they open the Employee Master form, add the employee, and
close that form so that the Continuing Education Detail form gets the
focus again, then the GotFocus event won't fire again (and hence requery
the combo box) unless the user moves to some other control on the form
and then back again.

What I usually do in cases like this is have code in the combo box's
DblClick event that does the following:

1. Open the master form in dialog mode, so they can add the record. By
opening it in dialog mode, I suspend execution of code in the combo
box's event procedure until the master form is closed again.

2. The next line after the call to DoCmd.OpenForm (with the acDialog
argument) requeries the combo box.

So if they have to add a new employee, they double-click the combo box
to open the employee master form. When that form is closed, the combo
is then requeried.

 

[Guest]

I put the code in the GetFocus event as you suggested. I did resolve the
issue I was having. However, I do like the idea of using the DblClick event
to open the Master File for new additions. I am new to access and coding
access. Would you mind assisting me with the coding for that. I looked in
my books and on the online help and was unable to find an example.

Thanks for you help. I appreciate it.

 

[Guest]

Please disregard my last question. I did figure it out and get it to access
the Employee Master File from the Continuing Education Form.

Thanks so much for the help. It works great.

 

[Dirk Goldgar]

Please disregard my last question. I did figure it out and get it to
access the Employee Master File from the Continuing Education Form.

Very good.

Thanks so much for the help. It works great.

You're welcome.