Opening a form to a specific record

[casan.mike@gmail.com]

Hi all,

I have a form called "customer_f rm" with many records in it.

I would like to have a form that has a combobox listing all the
customers, and a button that open the "customers_ frm" to the specific
record I have chosen in the combobox.

I could do this through the Search function of access, however it is
not really friendly for simple users of access.

Do you have an idea?

Thanks,

Mike

[Roger]

Re: Opening a form to a specific record

On May 14, 4:13 am, casan.m...@gmai l.com wrote:

Hi all,
>
I have a form called "customer_f rm" with many records in it.
>
I would like to have a form that has a combobox listing all the
customers, and a button that open the "customers_ frm" to the specific
record I have chosen in the combobox.
>
I could do this through the Search function of access, however it is
not really friendly for simple users of access.
>
Do you have an idea?
>
Thanks,
>
Mike

your button will do a
docmd.openform "customers_frm" ,,,,,

one of the parameters of openform is a 'where' clause, there you'll
specify
"customerId = '" & cboCustomer(0) & "'"

where cboCustomer is the name of your combobox

[casan.mike@gmail.com]

Re: Opening a form to a specific record

Thank You... I am gonna try it right now.

[Albert D. Kallal]

Re: Opening a form to a specific record


Try building a search combo box on the form using the wizard.

The result will be a combo box that lists all the customers, and when you
select one customer, the form will display that record.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

[casan.mike@gmail.com]

Re: Opening a form to a specific record

Hi again,

I got a runtype error '13':
Type mismatch

the customer_id is an autonumber, not a text... maybe it can help?!

thanks

[casan.mike@gmail.com]

Re: Opening a form to a specific record

On 14 mai, 13:24, "Albert D. Kallal" <PleaseNOOOsPAM mkal...@msn.com >
wrote:

Try building a search combo box on the form using the wizard.
>
The result will be a combo box that lists all the customers, and when you
select one customer, the form will display that record.
>
--
Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa l...@msn.com

Hi Albert, I already tried, but it does not work... It shows that it
is filtered, but still it shows all the records...
I managed to do this little tricky things with another database, but
with this one, I don't understand why it does not work....

Thanks

[Albert D. Kallal]

Re: Opening a form to a specific record

This uselaly works.

Remmeber, the seach combo box should not be "bound".

remember, let the wizard build it for you. Did you choose:

"find a record based on the value I selected in my combo box"

Also make sure the combo box is unbound, and also plays in the form's
header, not in the detail section of the form.

Pperhaps this is a continuous form, and it might not work in that case.

Remember this combo boxes placed on the main form (heading section), and
that form is assumed to be a form that allows you to edit and view a single
record.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

[casan.mike@gmail.com]

Re: Opening a form to a specific record

Hi again,

Well I tried many different things, but it does not work.

I can do it with other database, but with this one it does not work...
Is it possible that there is something inside the database?..

Thanks

[Albert D. Kallal]

Re: Opening a form to a specific record

<casan.mike@gma il.comwrote in message
news:e142bc9a-3cf5-435a-957c-8c304811a2ac@a1 g2000hsb.google groups.com...

Hi again,
>
Well I tried many different things, but it does not work.
>
I can do it with other database, but with this one it does not work...
Is it possible that there is something inside the database?..
>

You don't mention what version of MS access, but my recollection is in
access 97, or access 2000, if the form was based on a query, the wizard
would not work. what people would often do in this case who was simply
change the form to being based on the actual table in place of a query,
running the wizard ensuring a works, and then change in the form back to the
particular query who you are using for that form.

I would also suggest that when you create the combo box, make the first
column the primary key of the table, and the second column your customer
name. When you hit next on the wizard, you'll get a chance to resize the
columns, and you should simply size the first IDE column who does so small
that you can't see it anymore.

You could certainly post the code that the wizard created in your example,
and that might help to see what's going wrong here.

I assume that you can use the normal navigation buttons with this form, and
are able to freely navigate records on this form?

I would try the combo box wizard one more time, and remember to select the
first field as the primary key or auto number id from that table, and then
the second field you select is your customer name field that you plan to use
for searching.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKa llal@msn.com

[Roger]

Re: Opening a form to a specific record

On May 14, 5:32 am, casan.m...@gmai l.com wrote:

Hi again,
>
I got a runtype error '13':
Type mismatch
>
the customer_id is an autonumber, not a text... maybe it can help?!
>
thanks

if it's a number, you don't need the single quotes
so this should work
"customerId = " & cboCustomer(0)

[casan.mike@gmail.com]

Re: Opening a form to a specific record

Thank You all,

I am gonna try to do something with it.
You'll know soon enough if it works...

I have tried to search the web, and I have found this website that
gives a similar function.
However, it does not work either.

http://www.databasedev.co.uk/list_box_searching.html

The example given is also to search a records that is a text. The
night is gonna be long...

See you

[casan.mike@gmail.com]

Re: Opening a form to a specific record

This is mervellous!!!!
Thanks to you Roger, I find out!

The problem came from this tiny details. that is, the single quotes
are not needed!
In fact, I used the template from the website I gave above.

instead of:
DoCmd.OpenForm "customers_form ", , , "[customers.custo mer_id]=" & "'"
& Me.lstSearch.Co lumn(0) & "'"


I made:
DoCmd.OpenForm "customers_form ", , , "[customers.custo mer_id]=" &
Me.lstSearch.Co lumn(0)

And it works perfectly! The problem is solved!
Thank You all again for your quick replies. The night is not gonna be
so long :-)