Refresh the data in dropdown combo box

[h2fcell]

First some back ground about what I’m doing:
Using Access 2002, I have a form1 that looks at records in one table.
Form1 has a dropdown combo box that “Finds a record on my form based on the
value I selected in my combo box†.
The text boxes on form1 are all “Locked†= Yes, so the record can’t be
changed.
The dropdown combo box is not locked, so it allows me to select from the list.
I have an “Add New Record†button which opens form2 with all the fields of
form1 including the field used as the “Row Source†in form1 dropdown combo
box.
After adding a record on form2 and closing the form, the dropdown combo box
in form1 does not see the new record until I close and reopen form1.

My Solution:
I attached a macro that closes form1 and opens form2 to the “On Click†event
of the “Add New Record†button on form1. I run a macro attached to “On
Close†event of form2 that opens form1.
This action refreshes the dropdown combo box on form1.

I feel my solution is primitive.
Is there a way to refresh the data in dropdown combo box of an open form
without closing and reopening the form? Perhaps “Event Procedure†code added
to the “On Close†event of form2.

 

[Michel Walsh]

Me.ComboBox.Rowsource = Me.ComboBox.RowSource


should refresh the list of the combo box (even if it seems to be a 'do
nothing thing'; in the background, the list is requeried, so new records
added should appear ).


Vanderghast, Access MVP

 

[h2fcell]

Thanks Michel,
Would the best place to put that be the "On Click" event of the dropdown
combo box ?

 

[Michel Walsh]

on getting the focus for the combo box sounds a nice place to do it. The
list does not need to be refreshed, in general (exception of NotInList
requirements, in some cases) if it is not to be seen.

Vanderghast, Access MVP

 

[Klatuu]

Actually, there a a proper command to to this
Me.MyCombo.Requery

As to where to put it, I might suggest the form After Update event of form 2
and use:

Forms!Form1!MyComboBox.Requery

 

[h2fcell]

Michel,
The code works and refreshes the list, but when the new record is selected
the locked text boxes don't find the record on my form based on that new
value.

Do I need to do a similar refresh for each locked text box? or Is there a
way to do a similar refresh with the entire form?

Thanks for your help.

 

[h2fcell]

Thanks Klatuu,
Your suggestion worked too, but the locked text boxes still don't find the
record on my form1 based on that new value in the list. Is there a similar
way to refresh all Controls on a form?

 

[Michel Walsh]

You have to requery the form, me.requery, in order to see records added by
other users. Doing it, though, reposition the current record, so you have to
explicitly move to the record with the previously saved primary key:

Dim actualPK As ...
actualPK = Me.pk 'save the actual pk value
Me.Requery ' requery the recordset
with me.RecordsetClone 'don't miss the dots
' in the following lines of code
.FindFirst "pk=" & pk ' may need delimiter
me.Bookmark = .Bookmark 'don't miss the dot
end with

I assumed the control and the field name pk refer to a field with no dup.


Note that you cannot save the bookmark:

Dim actualBmk = me.Bookmark
Me.Requery
Me.Bookmark = actualBmk


since the bookmarks are not valid after a requery.



Vanderghast, Access MVP