Using a combo box to set criteria for query

[Norm Henderson]

Today I have read several posts on this topic and I am still having problems setting criteria in a query using a combo box in a form. In the query I have set the criteria on the criteria line: [Forms]![MyFormName]![Combo8] (The name of the combo box was given this name by the program, I guess).

On the form using the Combo8 box, I have set the values to be drawn from another table that contains the values.

On the form, I set a command button to run the query. However, when I bring up the form, it is totally blank. If I delete the criteria from the query, the form comes up as usual with the various boxes, etc. showing. Obviously, I have not done something or have made an incorrect connection between the query and the form for it to be blank.

Would appreciate some further help here. Thanks. Norm.

 

[Norm Henderson]

Thanks Wayne for your response and questions back. Although I'm a bit of a newbie, I'll try to answer them and give more details.
I have a select query with names and addresses, etc. drawn from a registry table (table1). In addition, I have a field in the query showing industries from where the individuals are from. Some persons have come from more than 1 industry. I have a table (table 2) in the query related one to many which lists the persons IDs and the industries from where they came.(enforced referential integrity with table1)

I have another table (table3) which lists the industries a-z. In the relationships, this table is related to table 2 in a one to many but referential integrity is not enforced. In the query I have drawn on tables 1 and 2.

In the form, which has only one field - the combo box unbound and draws its values from table 3, I have also placed a button to run the query. In the query in the field "industry" (drawn from table2), I have placed in the criteria line: [Forms]![nameofform]![combo8]. After all is saved, when I click on the form, it is simply blank - no combo box shows - nothing. If I delete out the query criteria line [Forms]......, then the form and combo box shows but, of course, does not do any filtering.

I'm not sure I have answered your questions - but I have tried to explain my layout. I would add that I am experimenting using the industries field. At a later date, I will expand the number of tables to show skill levels of the persons in the industries, etc. However, I want to get over the first hurdle of being able to search the database.

Many thanks again for your kind assistance. Norm.

 

[Wayne Morgan]

Ok, right off hand it sounds as if you are wanting a many-to-many relationship between
table1 and table3. To do this, use table2 as a "linking table". The fields in table2
should be the ID field from tables 1 & 3.

Example:
Table1 Fields
PersonID
FirstName
LastName
etc.

Table3 Fields
IndustryID
IndustryName
etc.

Table2 Fields
PersonID
IndustryID

(In table2, the Primary Key would be both of the fields together)

Table 2 would be linked to each of the other 2 tables on the associated ID field. This
would then normally be set up as a form/subform setup. In the main form you would draw
from table1 and in the subform you would draw from table3 and 2 (by using a query between
the tables and the subform) using table2 as the linking information. Link the main/sub
forms on the PersonID field. The main form will be listed as the Parent form. This will
cause all of the associated industry records for the person showing in the main form to be
displayed in the subform.

--
Wayne Morgan
Microsoft Access MVP

Thanks Wayne for your response and questions back. Although I'm a bit of a newbie, I'll

try to answer them and give more details.

I have a select query with names and addresses, etc. drawn from a registry table

(table1). In addition, I have a field in the query showing industries from where the
individuals are from. Some persons have come from more than 1 industry. I have a table
(table 2) in the query related one to many which lists the persons IDs and the industries
from where they came.(enforced referential integrity with table1)

I have another table (table3) which lists the industries a-z. In the relationships, this

table is related to table 2 in a one to many but referential integrity is not enforced. In
the query I have drawn on tables 1 and 2.

In the form, which has only one field - the combo box unbound and draws its values from

table 3, I have also placed a button to run the query. In the query in the field
"industry" (drawn from table2), I have placed in the criteria line:
[Forms]![nameofform]![combo8]. After all is saved, when I click on the form, it is simply
blank - no combo box shows - nothing. If I delete out the query criteria line
[Forms]......, then the form and combo box shows but, of course, does not do any
filtering.

I'm not sure I have answered your questions - but I have tried to explain my layout. I

would add that I am experimenting using the industries field. At a later date, I will
expand the number of tables to show skill levels of the persons in the industries, etc.
However, I want to get over the first hurdle of being able to search the database.

 

[Norm Henderson]

Many thanks, Wayne. Very helpful and I shall give it a go. If I have a further problem, I shall contact you.
Norm.