Refresh rowsource

[remmons]

I have a combo box whose row source is a table of company names. If the
Company the user wishes to select is not yet in the table, I have allowed for
the dblclick event to open a form (record source company table) to input the
new company's information. My question is this: after closing the company
form and returning to the main form, what is the best and cleanest way to
have the row source of the combo box reflect the new company just added?

 

[PJFry]

On the Got Focus event of the combo box, use:

Me.ComboBoxName.Requery

PJ

 

[remmons]

I've tried that code in multiple events but I don't think the combo box loses
focus... When I select another control then come back to that combobox, the
gotfocus event works but that seemed sloppy to me? Should I add some code
somewhere else to force the control to enter into the gotfocus event?

 

[PJFry]

Two other ideas would be to use the double-click event to move the focus away
from the combo box. The user is going to go back anyway, so have the focus
somewhere else should not be an issue.

Another possibility is to refresh the combo box from the company form.
Assuming the inital form remains open, you should be able to do this on the
On Close event of the company form:

Forms![NameOfInitalForm]![NameOfComboBox].Requery

The latter seems more like what you are looking for.

See if that works better.
PJ

 

[rm]

Check out this little bit of code..

Dim frm1 As Form
Dim ctl1 As Control

For Each frm1 In Forms
Debug.print frm1.name
For Each ctl1 In frm1.Controls
Debug.print ctl1.name
End If
Next
Next
...
ctl1.requery

 

[remmons]

I modified slightly and just reassigned the rowsource to the sql statement
instead of requerying (not sure why that didn't work exactly) but I was able
to do this in the gotfocus event after changing the focus on the dblclick
event of the combo box - Thanks a ton, this was driving me crazy!