Combo boxes for Form Query

jim

I have created a Table that has 13 fields and 2 Primary Keys, e.g. 60 1, 60
2, ... 60 28,
61 1, 61 2, ... 61 28, etc...
I want to create a Form where I can input the Primary Key values to query
the Form to
show the 13 fields assigned to the Primary key pair. If I create 2 Combo
boxes I can only
get the see the assignments for the first Primary Key, or only for the
second Primary Key.
I can't see how to get the lookup to use both Primary Keys.

appreciate any help
Jim

Mar 30 '06 #1

Larry Linson

"jim" <ji*****@yahoo.com> wrote

I have created a Table that has 13 fields
and 2 Primary Keys, e.g. 60 1, 60
2, ... 60 28, 61 1, 61 2, ... 61 28, etc...
Just for the record <pun intended>, a Record can have either no Primary Key
or one Primary Key. What you describe is a multi-field Primary Key, that is,
a single Primary Key composed of two Fields.

Create an unbound Combo Box, showing all the values of the first Field of
your Primary Key. Create a second unbound Combo Box, with a query for Row
Source that uses the first Combo Box value as criteria for that Field, and
shows the values of the second Field of the Primary Key to be selected. In
the After Update event of the first Combo Box, Requery the second Combo Box.
In the AfterUpdate event of the second Combo Box, use the contents of both
Combo Boxes to construct the WHERE clause, or WhereContition argument of a
DoCmd.OpenForm (or DoCmd.OpenReport).

In my "linked Combo Box searches" I set the Enabled property of the second
Combo Box to No in Design View, and enable it in code in the AfterUpdate
event of the first Combo, just prior to Requerying. That prevents the user
from entering the second Field first and causing confusion (though if you
are careful, you can make it work "going either way").

Larry Linson
Microsoft Access MVP

I want to create a Form where I can input the Primary Key values to query
the Form to
show the 13 fields assigned to the Primary key pair. If I create 2 Combo
boxes I can only
get the see the assignments for the first Primary Key, or only for the
second Primary Key.
I can't see how to get the lookup to use both Primary Keys.

appreciate any help
Jim

Mar 30 '06 #2

jim

"Larry Linson" <bo*****@localhost.not> wrote in message
news:JnUWf.16905$W75.4526@trnddc07...

"jim" <ji*****@yahoo.com> wrote

>I have created a Table that has 13 fields
> and 2 Primary Keys, e.g. 60 1, 60
> 2, ... 60 28, 61 1, 61 2, ... 61 28, etc...
Just for the record <pun intended>, a Record can have either no Primary

Key or one Primary Key. What you describe is a multi-field Primary Key, that is, a single Primary Key composed of two Fields.

Create an unbound Combo Box, showing all the values of the first Field of
your Primary Key. Create a second unbound Combo Box, with a query for Row
Source that uses the first Combo Box value as criteria for that Field, and
shows the values of the second Field of the Primary Key to be selected. In
the After Update event of the first Combo Box, Requery the second Combo Box. In the AfterUpdate event of the second Combo Box, use the contents of both
Combo Boxes to construct the WHERE clause, or WhereContition argument of a
DoCmd.OpenForm (or DoCmd.OpenReport).

In my "linked Combo Box searches" I set the Enabled property of the second
Combo Box to No in Design View, and enable it in code in the AfterUpdate
event of the first Combo, just prior to Requerying. That prevents the user
from entering the second Field first and causing confusion (though if you
are careful, you can make it work "going either way").

Larry Linson
Microsoft Access MVP

Thanks for the help.
Some of what you say is new to me, I will try to work thru it.
Thanks

Mar 31 '06 #3