Macro to Requery ComboBox?

[George]

I have an unbound ComboBox that gets its list from a
query. Once I select a line from the ComboBox, it passes
an ID number to the form which populates the fields from
the proper record.

I have the AfterUpdate macro working which passes the ID
number of the selected line in the list.

The problem is that the query should be rerun each time so
that the user has a complete list from which to choose.
I "think" that this is a Requery, because it is associated
with a combobox, but I've tried to attach it the OnEnter
event and the GotFocus event and neither one works. Also,
if I'm right and it is a Requery, do I specify the Unbound
Combo Box name. Help, says no if the combo box source is
a query (True).

Thanks for your help.

 

[George]

I'm still having a problem with which event to attach the
requery macro to.

I've tried a couple of events associated with the Unbound
Combo Box control (see first question) and the OnActivate
Event of the Form. The macro runs, but I get a message
saying "There is no field named '[NameLookUp]' in the
current record"

While it is true that the field is not in the current
RECORD, it is the first control on the form.

Thanks again for any help.

 

[Ken Snell]

To try to give you some ideas, I am going to need more info about your
form's setup and the actual macro actions. The error message that you're
getting suggests that what you want to do isn't what is needed?
Please..provide more info.
--
Ken Snell
<MS ACCESS MVP>

I'm still having a problem with which event to attach the
requery macro to.

I've tried a couple of events associated with the Unbound
Combo Box control (see first question) and the OnActivate
Event of the Form. The macro runs, but I get a message
saying "There is no field named '[NameLookUp]' in the
current record"

While it is true that the field is not in the current
RECORD, it is the first control on the form.

Thanks again for any help.

-----Original Message-----
Yes, specify the combo box control's name.

--
Ken Snell
<MS ACCESS MVP>




.

 

[Bob Newman]

Hey Ken,

your problem is one I struggled with for a long time, and
I knew it had to be simpler than I was making it. It is.
This is a process I use in most of my databases now and
it works great.

Create a combobox that gets it's row source from a query.

The query has only the one field in it from the table.

Double-click in the blank area of the query to bring up
the General query properties.

There is a property called Unique Values. Choose Yes.
This is so if a value is duplicated many times in the
table being queried, it only appears once in the list.

Save the query.

Here is the trick or trap I kept falling into. On your
form, there is a property (for the combobox)in the Other
tab called Name. Sounds simple enough, but I always
overlooked this. This is the name you give to the
combobox control. It is not the field name, because the
combobox is not bound to the field anyway.
What is important here, is that in the macro, you simply
use the Requery Action, and at the bottom of the macro
window where the Action Arguments are, you must enter the
name of the combobox that you have given it. Not the
fieldname.
Have the macro run in the Event - After Update. This way
after you enter a new value in the combobox, then move to
a new control on the form, the Requery action runs, and
immediately updates the list each time.

I have been using this method for a long time now. It is
simple and reliable. Try it. Let me know if I missed any
details in this explanation, and I will try to clarify it.

Good Luck. BobN