Refreshing items in a combo box list based on a query 1

[kbdci]

I have a user entry form in Access 2007 that is causing me difficulty. I am having a problem refreshing the data in a combo box that is based on a query that lists all of the current items in a field in the table on which the form is based. An example of the query is shown below:

SELECT DISTINCT Catalogs.Composer
FROM Catalogs
WHERE (Catalogs.Composer) Is Not Null;

The query shows the list of current unique entries in the Composer field of the Catalogs table. The user can enter a new composer in the list by typing the composer name into the combo box field. The problem is that the new data doesn’t show up in the list until the query runs again. I have tried using the “requery” method in the After Update event to refresh the list, but that doesn’t work.

However, if I use the Home tab in the ribbon and select Refresh (not Refresh All) in the Refresh All dropdown of the Records section, the effect is exactly what I am looking for. The new entry is displayed in the dropdown list and the user is still on the same record. (If Refresh All is used, the first record is displayed instead of the new record.)

How can I produce the same effect in code so that the user doesn’t have to do anything except enter the new data?

Thanks for any help.

 

[PHV]

You may try to replace this:
Me![combo name].Requery
with this:
Me![combo name].RowSource = Me![combo name].RowSource

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kbdci]

Thanks. I tried that at your suggestion, but it doesn't work. I still don't see the refreshed list from the query.

 

[dhookom]

"requery" method in the After Update event

Which After Update event? The form or combo box or what?

Duane
Hook'D on Access
MS Access MVP

 

[kbdci]

The combo box After Update event - so that the code fires once the user finishes entering text in the combo box field.

 

[dhookom]

Please just share your code.

Duane
Hook'D on Access
MS Access MVP

 

[kbdci]

This is the code I tried.

Private Sub Composer_AfterUpdate()
Me.Composer.Requery

End Sub

I also tried the RowSource suggestion above with no luck.

 

[MasterRacker]

I assume you're tabbing out of the combo after typing the new entry?

Instead of AfterUpdate try this in the LostFocus event:

    DoCmd.RunCommand acCmdSaveRecord
    Forms!<yourform>!Composer.Requery

Might not be the best way but it worked for me on something similar.


Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[dhookom]

The record has not been saved in the Catalogs table so the new value will not show.

Duane
Hook'D on Access
MS Access MVP

 

[kbdci]

Thanks, MasterRacker. That worked. Sometimes the obvious is missed.

 

[MasterRacker]

Duane,
That's what I figured out in my case. The form was bound to that same table supplying the combobox. Changes wouldn't actually commit until I navigated to a different record at the form level. Forcing a save solved the issue.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]