1 form w/ data from 2 tables?

[shiner]

Hi,
I am trying to construct a universal equipment log for a school and their sports teams. I am having difficulty with a form handling data from two tables - essentially a #Name? error. One table holds the item descriptions for a particular team. Like:

field: equipment_item_1 value: shirt
field: equipment_item_2 value: shorts

Then there is another table that holds the records of individual students to identify what equipment they have checked out. But this holds more info - like item size, style/color, and whether they have the item (true/false).

I am trying to make the form that will handle the data entry portion for each student. This form needs to display the item description from the equipment_description table and then the info from the individual_student table.

I am having difficulty when I try to apply the other table as the control source for the data that is found outside of the table that the form is based on. I am not sure it is possible, but it seems possible to incorporate because I can use the BUILDER feature to navigate over to the data I am trying to display. However, I get the #Name? error when I try to actually view my new form. Any tips would be greatly appreciated, I know this is simple and I am missing something.

The syntax I have now in that data control box is:
=equipment_desriptions!equipment_item_1

 

[shiner]

ok, solution

Ok, after toying with it some more, I found the easiest of answers. I made the form based on a query - rather than a table. This allowed me to make the query, based on both tables.... I am getting the hang of this stuff slowly but surely.

 

[dodgeboy29]

Could you explain how you set the query up for your example? I am getting the same #Name? errors in a very similar situation.

Thanks

 

[casey]

shiner,
Not to confuse your situation, but did you create and save a Query and then set your Form's recordsource to that Query (say Query1 or something like that)?
If so, you can accomplish the same thing by copying the Query's SQL statement (Select....From...Where...etc) and pasting it directly in the Form's recordsource. By doing so you can more easily manage changes to the Form's recordsource w/o the need to go back and change a separate Query you've created.

To change the Form's recordsource, click on the far right of the Recordsource property field (...) on the properties sheet and this will bring up Querybuilder which should look familiar. Copy your Query's SQL and paste it into the SQL view. This way you won't have to flip back and forth between the Form and its Query, thus simplifying things if changes need to be made.
In essense, you're doing the same thing as what you have done, but your Form manages its own query, eliminating the need to manage and store a Query separately from your Form. If done properly, you get both in one and you can get rid of your Query altogather. (Unless you've used that query elsewhere, that is!)

If this is what you've already done, then disregard this post.

If this doesn't make sense, let me know and I'll try to clarify.
HTMS.

 

[shiner]

Casey: Thank you for the suggestion. I am sure the SQL statement would speed things up and it certainly sounds more efficient. However, that is a little advanced for me at this point. Basing it on a new query was the easiest solution for me at the moment.

Dodgeboy: When you make a query, it can be based on multiple tables. Create a new query and in the design view, you can bring up a list of tables, queries, or tables and queries. From here, you can add the multiple tables in question and their fields become available for you to drop into your query. This is what worked for me. The form will then be based on your new query and the data will be present for the form from the query. You will no longer see objects bound to =[some_table]![some_field] rather they would be at the local level =some_field

I hope that helps.