requery

  • Thread starter Thread starter Eric Curry
  • Start date Start date
E

Eric Curry

I have a combo box that lists a few options for a group.
The user either selects or types in a new value. I've
tried a macro with a requery of that combo box and
putting the macro name in the AfterUpdate field of the
form, of the subform, of whereever I can put it. Still
nothing. I'd like the new value that someone types in to
be accessible next time so you don't have to retype that
new value again, just select it from the pull down. Any
thoughts?
thanks
 
Eric said:
I have a combo box that lists a few options for a group.
The user either selects or types in a new value. I've
tried a macro with a requery of that combo box and
putting the macro name in the AfterUpdate field of the
form, of the subform, of whereever I can put it. Still
nothing. I'd like the new value that someone types in to
be accessible next time so you don't have to retype that
new value again, just select it from the pull down.
Click to expand...


You have to add that data to the table that is the basis of
the combo box's RowSource.

This normally done using the combo box's NotInList event.
where you can open another form to allow the user to add
all the relevant data or you can just insert the new record
directly. Be sure to set the NotInList procedure's Response
argument to acErrDataAdded so that you don't get an Access
message pop up. Check NotInList in Help for details and
post back if you have a specific question.
 
That seems like a lot more work than what I've seen in
the Northwind's database. Does this article make any
sense?
www.microsoft.com/accessdev/articles/nwindfrm.htm
Go down to the section "Provide an up-to-date list of
valid values to select from"

That article makes it sound like it's just a macro...but
I can't figure it out!
thanks
Eric
 
eric said:
That seems like a lot more work than what I've seen in
the Northwind's database. Does this article make any
sense?
www.microsoft.com/accessdev/articles/nwindfrm.htm
Go down to the section "Provide an up-to-date list of
valid values to select from"

That article makes it sound like it's just a macro...but
I can't figure it out!
Click to expand...


That article only makes sense when the combo box's RowSource
is based on the same table as the form. Even then, the new
entry would not show up in the combo box until you move to
a new/different record (or do aomething else that saves the
current record.

In a more general situation, a combo box's NotInList
procedure with the Response set as I said would requery the
combo box automatically.

The article I referred you to is a general and complete
solution, but the specific code you would need depends on
how your combo box is set up (i.e RowSource, bound column,
etc).

I refuse to use macros and consequently am unfamiliar with
their idiosyncrasies. They are far too limiting and
relatively incomprehensible compared to VBA.
 
Hello Eric

I use that same method for City, State and zip code.
on my Customers Form

It is a comboBox with column count one and one bound column
and the Row source is
(for City)
SELECT DISTINCT Customers.City FROM Customers;
(for State)
SELECT DISTINCT Customers.State FROM Customers;
(for postalCode)
SELECT DISTINCT Customers.PostalCode FROM Customers;

I don't use the Macro
I use the AfterUpDate Event of the Form and just requery
the comboBoxes when I move to the next
record the new data is there

Private Sub Form_AfterUpdate()
Me!City.Requery
Me!State.Requery
Me!PostalCode.Requery
End Sub

If the New data is not accessible the next time the form
was closed and reopened it might be a unbound
comboBox check and see what the Control Source of
your comboBox is. If the comboBox is unbound
the new data will not be inserted in to the table


Hope this helps
Thomas
 
Back
Top