combo box value does not change when record selector is used

[Ted W.]

I have created a combo box in a form that enables someone to select an item
number and then have the form populate all of the information (cost, date
delivered, etc) for that item number. This works perfectly. However, if
someone clicks on the standard "next" or "previous" record selction buttons
on the bottom of the form the value in the comb box field does not change,
but all of the other fields do change to the next record. So how can i make
the combo box change to the correct record if someone uses the record
selection buttons instead of the selecting an item directly from the combo
box?

 

[Wayne-I-M]

Hi

Use the AfterUpdate action

something like

Private Sub ComboName_AfterUpdate()
Me.ComboName = ""
End Sub

 

[Klatuu]

dagnabit, Wayne, that ain't gonna work.

Every time someone tries to make a selection from the combo box, it will
just make it go empty. (It would be fun to watch a user try to select
something

This is obviously an unbound combo box. Because it is an unbound control,
it knows nothing of the form's record source. The only way to keep it in
sync is if there is another control bound to the field in the record source
that the combo uses for its row source.

This is pretty typical. You have a hidden bound text box that you use to
update the record source and an unbound combo used strictly for looking up a
specific records. It is possible to use a bound combo, but it takes extra
coding if you are also using it as a lookup. What will happen if you don't,
is, let's say you are one a record for Roy Rogers. You want to Look Up the
record for John Wayne, so you select John Wayne from the combo. Then you get
a primary key violation or some other duplicate record violation because you
changed the value of the combo from Roy to John. This causes the form to
become dirty. Now the form tries to position to John's record which causes an
update, but since the combo says John it is trying to write Roy's record with
John's name. You will get a message box with something like "Now hold on
there, Pilgrim. You better smile when you try to violate them duplicate value
rules". (Texas version of Access 1897)

In short, (Roy was much shorter than John - 5' 7'' and 6' 3"), to keep a
combo in sync with the current record, use the Current event.

If Me.NewRecord Then
Me.MyCombo = Null
Else
Me.MyCombo = Me.BoundTextField
End If

 

[Ted W.]

I appreciate your help, but i am no clearer as to how to fix this. Where do
I enter the code you gave me. You are correct it is an unbound combo box
created through the combobox wizard. I am not that advanced of a user so if
you could kindly provide a litte less detail on John Wayne (although quite
humorous. Thanks!) and more detail on what to fix and where exactly to add
the fix
Thanks

 

[Klatuu]

This goes in the form's Current event:
If Me.NewRecord Then
Me.MyCombo = Null
Else
Me.MyCombo = Me.BoundTextField
End If

BoundTextField would be the name of the control that has the same data item
that you want to keep the combo in sync with.

Just so you can keep up:

http://www.jwayne.com/biography.shtml