How can I use a combo box to advance records on a form?

[FZ]

Hi Gang,
I was wondering if a generous person might be able to walk me
through what I believe is a pretty simple task. I actually have
significant Access experience, but I haven't done it in about 3 years
and I'm more rusty than I thought I was. Anyway, in a nutshell:

The scenario: 2 combo boxes (comboLastName and comboStateorProvince)
on a form with 30 text boxes, the form's recordsource has been set to
a query, both combo boxes and text boxes have been bound via the form
recordsource and display correct values. OK, no problems.

The problem: I need the textboxes to advance to the corresponding
record of the combo box value that has been selected. So, if I select
"Jones" from comboLastName, I want to see all of the data in the 30
text boxes and other combo box that corresponds to the "Jones" record.
Make sense? Pretty basic, right?

Now here are my specific questions:

1) I'm assuming that I need to write VBA code in the combo boxes'
events. Which event of comboLastName and comboStateorProvince should
I place the code? Change, DoubleClick, AfterUpdate?

2) I have the recordsource of the combo boxes set to the same query as
the recordsource of the form. The controlsource of each combo box
corresponds to the same field of the query (i.e.
comboLastName.controlsource = LastName, comboStateorProvince =
StateorProvince). Are these bindings correct?

3) I've written and re-written so much VBA code it's fairly useless to
ask what should I change, could anyone give me a few general
guidelines of what I should be coding here?

4) Any other general pointers or tips here on how I should go about
this?
Thank you SO MUCH for your help in advance. This has become quite
maddening, especially since I know this is so basic, but I need a
refresher. Thanks again!!

[Allen Browne]

A1: Use the AfterUpdate event of the combo.

A2: Use unbound combos, i.e. leave their Control Source completely blank.
You need to provide combos specifically for navigating, and you do not want
to store a value into a field of the current record.

A3. Code example.
There is a combo box wizard that can generate this code, but it's not very
good. For a more complete approach, see:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"FZ" <fl*******@hotmail.com> wrote in message
news:c7**************************@posting.google.c om...

Hi Gang,
I was wondering if a generous person might be able to walk me
through what I believe is a pretty simple task. I actually have
significant Access experience, but I haven't done it in about 3 years
and I'm more rusty than I thought I was. Anyway, in a nutshell:

The scenario: 2 combo boxes (comboLastName and comboStateorProvince)
on a form with 30 text boxes, the form's recordsource has been set to
a query, both combo boxes and text boxes have been bound via the form
recordsource and display correct values. OK, no problems.

The problem: I need the textboxes to advance to the corresponding
record of the combo box value that has been selected. So, if I select
"Jones" from comboLastName, I want to see all of the data in the 30
text boxes and other combo box that corresponds to the "Jones" record.
Make sense? Pretty basic, right?

Now here are my specific questions:

1) I'm assuming that I need to write VBA code in the combo boxes'
events. Which event of comboLastName and comboStateorProvince should
I place the code? Change, DoubleClick, AfterUpdate?

2) I have the recordsource of the combo boxes set to the same query as
the recordsource of the form. The controlsource of each combo box
corresponds to the same field of the query (i.e.
comboLastName.controlsource = LastName, comboStateorProvince =
StateorProvince). Are these bindings correct?

3) I've written and re-written so much VBA code it's fairly useless to
ask what should I change, could anyone give me a few general
guidelines of what I should be coding here?

4) Any other general pointers or tips here on how I should go about
this?
Thank you SO MUCH for your help in advance. This has become quite
maddening, especially since I know this is so basic, but I need a
refresher. Thanks again!!