Refresh data in a combo box

[KittyS]

I have a form that shows info for a single table. One of the controls
is a combo box that lists all the existing values in that field in that
table, using this SQL:
SELECT [Ads small version].Publication
FROM [Ads small version]
GROUP BY [Ads small version].Publication
ORDER BY [Ads small version].Publication;

Limit To List is set to No - the users have to be able to enter new
values, it's just for their convenience that I show what's been used
before.

The list is fine when the form is first used. When a new value is
entered, it is saved in the record. But when I go to another record,
the new value I just entered doesn't show in the list.

I'm sure it's really easy to refresh the list, but I can't figure out
how. Help?

TIA
Kitty, Newbie

 

[Ken Snell \(MVP\)]

Use the form's Current event to requery the combo box.

 

[KittyS]

Oh brother! I looked up Requery in th VB help, including the example,
and it looks so COMPLICATED! Looking at it, I can't figure out what to
plug in where. The syntax is given as
recordset.Requery newquerydef

What goes in recordset? What is newquerydef? Is there somewhere I could
go to read up on things like this? It looks like I need to know a lot
more than I do - I have to go backwards from what I want to accomplish
to find the elements I need.

Thanks Ken!
Kitty

 

[Ken Snell \(MVP\)]

It's actually pretty easy. This code would be the form's Current event
procedure (change Combo to the real name of the combo box):

Private Sub Form_Current()
Me.Combo.Requery
End Sub

 

[KittyS]

THANX!! You are right, that's ridiculously easy!

Now, what if I am taking the list from another table and want to update
the table?

The code that makes the list is
SELECT Neighborhoods.[Nabe name], Neighborhoods.[Nabe code] FROM
Neighborhoods ORDER BY Neighborhoods.[Nabe name];

The neighborhoods table has only two fields: nabe code and nabe name. I
guess I should pop up a window where they can enter the new
neighborhood. I've been reading around about this but again all the
samples I see look very complicated.

Help again would be appreciated!

Should I start a new topic in a case like this?
Kitty

 

[Ken Snell \(MVP\)]

You set the combo box's Limit To List property to Yes. This prevents anyone
from selecting an item that isn't in the list. Then you use the combo box's
Not In List event to run code that
-- opens a form in dialog mode (the form is bound
to the Neighborhoods table, and the form's
Data Entry property is set to Yes) to allow
the user to enter the new data via the form
-- requeries the combo box

 

[Guest]

I'm having trouble to use this combo requery.
1. It does not refresh the combo box. The new entry will only show up after
I select an old data then come back to the combox box again.
2. what is the difference between Me.Combo.Requery and Me!Combo.Requery?