use 1 combobox instead of 4

[Guest]

Hi all
On an AXP Form I need to find records by LastName, PostCode, OrderNo and
Date. To have 4 comboboxs, 1 for each search seems dead clumky to me. But I'm
not how marry an Option Group with a Combobox so that depending on the Option
Group selection the Combobox will show the appropriate list.

TIA

johnb

 

[Allen Browne]

John, you would need to use the AfterUpdate event of the first combo box to
set the RowSource of the second one.

You may run into problems here: these fields appear to have different data
types (text, number, and date), and it is quite easy to crash Access if you
go altering the data types after the form is open.

It may therefore be better to use a combo for selecting the field, and a
text box for entering the value to match. You can do that quite easily by
downloading the example from here:
Find as you type - Filter forms with each keystroke
at:
http://allenbrowne.com/AppFindAsUType.html
This example is *very* easy to set up: after you copy the code into a
module, you just paste the combo and text box onto any form and set one
property. It figures out what field it can offer on that form, and matches
the value you type after each key press.

BTW, if you really have a field named Date, it is likely to cause you grief.
Date is a reserved word, and Access is likely to confuse it with the today
(the system date.) For a list of other field names to avoid, see:
http://allenbrowne.com/AppIssueBadWord.html

 

[Al Campagna]

John,
If I understand correctly... I think you mean...

I need to find records by LastName OR PostCode OR OrderNo OR Date.

If you want to use an OptionGroup to accomplish that, use the OptionGroup AfterUpdate
event...
(you could also use a Select Case statement...)
If optFindBy = 1 Then
cboFindCombo.RowSource = (your LastName SQL statement here)
ElseIf optFinBy = 2 Then
cboFindCombo.RowSource = (your PostCode SQL here)
'etc...
End If
cboFindCombo.Requery

You'll also need to code the AfterUpdate event of cboFindCombo
If optFindBy = 1 Then
(do a LastName Find here)
ElseIF optFindBy = 2 Then
(do a PostCode Find here)
'etc...
End If
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

Hi Al and Allen

Many thanks for your assistance. Yes. Using Date as a fieldname is a no no I
should have said OrderDate. And Al your are spot on I did mean LastName OR
Postcode etc...

Many thanks guys

johnb

 

[Guest]

Hi Allen Browne
I've just downloaded the solution you suggested. How on earth did you get
your brain around that code? I know code is a bit like bricks in a wall; one
brick is like one line of code. How did you approach solving this problem? I
feel a loss as to where start solving a problem like this.

Regards

john

 

[Allen Browne]

Fortunately, the FindAsUType code is dead simple to employ, even if it isn't
so easy to understand.

Most of the complexity in the code involves figuring out which fields the
user can see on the form, and the name the user knows them by, and the data
type of each field, and whether the value shown in the combos is the actual
value stored in the field, and if not whether your version of Access is able
to filter that field, and the order of the fields on the form including any
that may be on subsequent tab pages, so it can load the combo with the
fields that can be filtered, along with a display name, field name, and data
type, so you can use it on any form without knowing anything about the form
and its data ahead of time.

Once the combo is loaded with the data about the fields, the filtering is
dead simple.

 

[Guest]

Allen
Thanks for the comments

john

Fortunately, the FindAsUType code is dead simple to employ, even if it isn't
so easy to understand.

Most of the complexity in the code involves figuring out which fields the
user can see on the form, and the name the user knows them by, and the data
type of each field, and whether the value shown in the combos is the actual
value stored in the field, and if not whether your version of Access is able
to filter that field, and the order of the fields on the form including any
that may be on subsequent tab pages, so it can load the combo with the
fields that can be filtered, along with a display name, field name, and data
type, so you can use it on any form without knowing anything about the form
and its data ahead of time.

Once the combo is loaded with the data about the fields, the filtering is
dead simple.