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.

--
I may not know VBA inside out, but from the outside I am looking in.
Dylan Moran - Melbourne Australia
--
I may not know VBA inside out, but from the outside I am looking in.
Dylan Moran - Melbourne Australia

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


Have you?

--
HTH
Van T. Dinh
MVP (Access)



"Dylan Moran" <(E-Mail Removed)> wrote in message
news:9A866626-830F-4080-91A9-(E-Mail Removed)...
> 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.
>
> --
> I may not know VBA inside out, but from the outside I am looking in.
> Dylan Moran - Melbourne Australia
> --
> I may not know VBA inside out, but from the outside I am looking in.
> Dylan Moran - Melbourne Australia

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.





"Van T. Dinh" wrote:

> Access generally offer this option only if the Form is bound, i.e. you have
> assigned a RecordSource on the Form.
>
>
> Have you?
>
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
>
>
> "Dylan Moran" <(E-Mail Removed)> wrote in message
> news:9A866626-830F-4080-91A9-(E-Mail Removed)...
> > 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.
> >
> > --
> > I may not know VBA inside out, but from the outside I am looking in.
> > Dylan Moran - Melbourne Australia
> > --
> > I may not know VBA inside out, but from the outside I am looking in.
> > Dylan Moran - Melbourne Australia
>
>
>

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.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dylan Moran" <(E-Mail Removed)> wrote in message
news:68816E35-2F57-46CA-B586-(E-Mail Removed)...
> 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.
>
>
>
>
>
> "Van T. Dinh" wrote:
>
>> Access generally offer this option only if the Form is bound, i.e. you
>> have
>> assigned a RecordSource on the Form.
>>
>>
>> Have you?
>>
>> --
>> HTH
>> Van T. Dinh
>> MVP (Access)
>>
>>
>>
>> "Dylan Moran" <(E-Mail Removed)> wrote in message
>> news:9A866626-830F-4080-91A9-(E-Mail Removed)...
>> > 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.
>> >
>> > --
>> > I may not know VBA inside out, but from the outside I am looking in.
>> > Dylan Moran - Melbourne Australia
>> > --
>> > I may not know VBA inside out, but from the outside I am looking in.
>> > Dylan Moran - Melbourne Australia
>>
>>
>>

"Allen Browne" wrote in message:
news:(E-Mail Removed)...

> 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.

--
Jeff Conrad
Access Junkie
Bend, Oregon

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.


"Jeff Conrad" wrote:

> "Allen Browne" wrote in message:
> news:(E-Mail Removed)...
>
> > 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.
>
> --
> Jeff Conrad
> Access Junkie
> Bend, Oregon
>
>
>

Thanks for that Jeff.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jeff Conrad" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> "Allen Browne" wrote in message:
> news:(E-Mail Removed)...
>
>> 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.

"Dylan Moran" wrote in message:
news:8EB7E287-2FA2-47A4-B52A-(E-Mail Removed)...

> 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
Access Junkie
Bend, Oregon

"Allen Browne" wrote in message:
news:(E-Mail Removed)...

> 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.
:-)

--
Jeff Conrad
Access Junkie
Bend, Oregon

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.

"Allen Browne" wrote:

> Thanks for that Jeff.
>
> As you probably guessed, I never use the wiz, so didn't realize that
> limitation, though it makes sense.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Jeff Conrad" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > "Allen Browne" wrote in message:
> > news:(E-Mail Removed)...
> >
> >> 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.
>
>
>