Combo box selection to find record

[Guest]

Also posted in Forms forum as wasn't sure which to post in , please don;t
complain. and ignore if viewed in other forum.

My last post may no have been that clear, so I will atempt to simplify.
Thanks to those who responded, you information will be used in other ways and
was very helpful.

I have a customer form. I want to be able to put a combo box that contains a
hidden CustId and cisible CustomnerName on it so that I can select a customer
from the combo box and the pointer will jump (or refresh) to that customer.
The functionality I want is exactly the same as selecting a record number in
the record navigator bar. That is, I don't particularly want to filter the
recordset, just go to the approriate record in the recordset. That is "where
CustId = 20" for example based on the users selection in the combo box.

My last post had a reply that stated there was a wizard that asked this
question when you put a combo box on your form, and MS help states this as
well for when you put list boxes or combo boxes on a form, however I don't
seem to be getting this. This was not the case, the wizard did not ask about
opening a specific record based on my choice.

MS Access Help topic states
"
Find a record by selecting a value from a list
On a form, you can create a list box or a combo box that finds a record when
you select a value from the list.

Open the form in Design view.
Click Control Wizards in the toolbox if it's not already pressed in.
In the toolbox, click List Box or click Combo Box .
On the form, click where you want to place the list box or combo box.
In the first wizard dialog box, click the option to find a record based on
the value you selected in the list box or combo box. (THIS STEP DOES NOT
OCCUR ON MY WIZARD!!!)
Follow the directions in the wizard dialog boxes. In the last dialog box,
click Finish to display the list box or combo box in Design view.
If you want to see the event procedure that makes the list box or combo box
work, open the box's property sheet, and then click the Build button next to
the AfterUpdate property box.

As the wizard doesn't seem to be doing as it's told ;-) VBA code to find
the record based on my selection would be great and a recommendation for the
event to attach to.

Again your patience and assistance is greatly appreciated.

 

[Van T. Dinh]

Access generally offer this option only if the Form is bound, i.e. you have
assigned a RecordSource on the Form.


Have you?

 

[Guest]

Yes, the form contains only the details form the table customers (tblCustomer).
It has a recordsource that selects the relevant fields from the table.
I want a combo box to select and navigate to that customer when selected.
there are subforms on the form but htat is irrelevant to my need.
Any help is appreciated.

Access generally offer this option only if the Form is bound, i.e. you have
assigned a RecordSource on the Form.


Have you?

 

[Allen Browne]

Then there is a combo box wizard that can write this code for you, or you
can copy the code from this article:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Yes, the form contains only the details form the table customers
(tblCustomer).
It has a recordsource that selects the relevant fields from the table.
I want a combo box to select and navigate to that customer when selected.
there are subforms on the form but htat is irrelevant to my need.
Any help is appreciated.

Access generally offer this option only if the Form is bound, i.e. you
have
assigned a RecordSource on the Form.


Have you?

 

[Jeff Conrad]

in message:

Then there is a combo box wizard that can write this code for you, or you
can copy the code from this article:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html

FYI Allen,

The combo box wizard third choice will not appear if the form
is based on a direct SQL statement as opposed to a saved query
or table name. That is probably the reason Dylan is not seeing
the option appear.

 

[Guest]

After assistance from so many helpful people, my solution is found.

Thanks to everyone who assisted.

Van - thanks.

Allen, as usual your help and effort you put into assisting other in the
forum is most appreciated. And thanks for providing the link to your code.

Jeff, Thanks for the tip. You were 100% correct and identified the qwerk. I
had based my form on an SQL query (in order to not have as many queries on my
query page), and therefore the combobox wiizard was not showing the third
option to "Find a record on my form based on the value I selected in my combo
box". I only had the two options. However, I change the record source to a
saved query and BINGO, there it is. Thanks for sharing your knowledge.

Much better customer form as a result.
I now have the combo box and a search button, rather than just the search.

 

[Allen Browne]

Thanks for that Jeff.

As you probably guessed, I never use the wiz, so didn't realize that
limitation, though it makes sense.

 

[Jeff Conrad]

in message:

Jeff, Thanks for the tip. You were 100% correct and identified the qwerk. I
had based my form on an SQL query (in order to not have as many queries on my
query page), and therefore the combobox wiizard was not showing the third
option to "Find a record on my form based on the value I selected in my combo
box". I only had the two options. However, I change the record source to a
saved query and BINGO, there it is. Thanks for sharing your knowledge.

You're welcome Dylan, glad to help.

BTW, after the code has been created, you can reset the form's
record source back to a SQL statement if you desire. Everything
will work just fine.

 

[Jeff Conrad]

in message:

Thanks for that Jeff.

As you probably guessed, I never use the wiz, so didn't realize that
limitation, though it makes sense.

I'm not at all surprised to hear that Allen.

 

[Guest]

Allen,
How would I use this code if I had a combo box on my main form and my fields
on a subform? What I am trying to do is have a list of all my records in my
subform and then choose an option from my combo box to go to that record but
still have the other records visible in a continuous form so I can scroll
through it.