Use textbox value to show specific field

[Ringhal]

I am having a problem setting up a query. I have two tables and one form. The tables are linked in a one-to-one relationship by primary key where no duplicates are allowed. For this example, there is a People table and a Food table. The People table has names of people as field names. These fields are set as check boxes (Yes/No data type). The primary in both table lists foods and for this example we'll say that the check boxes show that the food is the person's favorite food.

The form has a combo box and a text field. The text box field has information pulled from somewhere else and for this example, it's manually typed in. The combo box is why I am asking for help. The Row Source for the Combo Box is where I want to build a query that will show the person named in the textbox their favorite foods.

This can be done using VBA, SQL or simply a query, whichever way is needed. I tried to add an expression to the query where it would only show the Person, but it didn't work out.

12199 Why is an mdb file invalid?

[jonh]

Your table design doesn't seem to make a lot of sense.
Peoples names are data. You should not have to change a table's design to add new data, only a different kind of data.

[Ringhal]

I am not sure if I am making myself clear, maybe I can try explain it a different way. What I want to do is, change which field (the person's field) is displayed in the query by using an expression that matches the text box on the form, like creating a new table, one field for food and another field for the specific person. I do not know if it can be done. Hope its clearer now.

[jonh]

I understood you perfectly.

Anyway, add a button to your form and paste this code

Private Sub Command5_Click()
Field1.RowSource = "SELECT Food FROM People WHERE [" & Text0 & "]=True"
End Sub

[Ringhal]

Thank you for the help. It is working correctly. I added the code to the form_open event so that it runs automatically when the form opens.