Using Search Combo Box after Requery

[Guest]

Form 1 (record source is a table) has a combo box that that is used to search
for records. The row source for the combo box is a query. If the desired
record is not found, Form 2 (record source is same table as Form 1) opens to
allow entry of the new record. After adding the new record, Form 2 closes
and Form 1 once again becomes the active form. The combo box on Form 1 is set
to requery after the addition of a new record and Form 2 closes.

When Form 1 is first opened, using the combo box to search for a record and
then display the recordset works fine.

After adding a new record and closing Form 2, the record source table
contains the new record and the requery works fine; the new record is added
to the drop down list of the search combo box in the correct alphabetic
order. So far everything seems to be working perfectly.

What doesn't work at this point, however, is that if the newly added record
is selected from the drop down list in the search combo box, the wrong
recordset is displayed. Instead of the correct recordset, record #1 is
displayed. If another record is selected the correct recordset is displayed,
just as when Form 1 was first opened but selecting the new record always
causing record #1 to be displayed. Of course, once Form 1 is closed and then
reopened, selecting the new record from the search combo box works as it
should.

Can anyone tell me how to correct this? I have tried Refresh but either am
using it on the wrong event, using incorrectly or it is the entirely wrong
thing to use.

 

[Guest]

if you are using Me.Refresh or Forms!Form1.Refresh from form1 (vba code based
on event or your own code as form2 closes) this does not re-load the
recordset from the table (local in access or linked). in general you need to
re-load the recordset (which is why it works after the form is closed).
Assuming that your combo box is 2 column (key id and description) after you
close form to and "re-filter" form one to the new combo box key id, a record
for that key doesn't yet exist in the recordset of form1 so you either a) get
all records or b) get none. for this to work you need to execute a re-load by

DoCmd.RunCommand (acCmdRefresh)

Use Form1.Refresh to redisplay the CURRENT RECORD values if you change them
programmatically; not to reload the recordset.

btw, you i bet aren't doing a requery by using your combo box, i would be
you are executing a filter (using Form1.Filter and Form1.FilterOn). A
"re-filter" does NOT re-load the forms current recordset.

comment: one big reason why access consumes so much PC memory! ugh. is
because it keeps "hidden" tables for each form recordset and each table used
as a record source for each form. check out a filter after filtering by form
if you want to see what i mean (ex: "Lookup_mycombobox.desc = 'Option 1'"
where Lookup_mycombobox is a hidden table)

 

[Guest]

Thank you JamesK for the response. It got me thinking on the right track.
What I finally did was add "Forms![Form1].Requery" to the Close event on Form
2 (the subform). I already was using "Forms![ComboBox].requery on the Close
event for Form 2. Now the drop down list of the search combo box requeries
to show the added record and when selected displays the correct recordset.

THANK YOU, THANK YOU, THANK YOU