one form with multiple tables 1

[bobbster2000]

I want to make one Form with multiple textboxes and checkboxes from different tabels. But I don't know how to make each textbox and the checkboxes reference there "specific" tables. When I click on the drop down list in the control source section its empty or its "only" referencing the table I seleted when the form wizard started up. I can get to the other tables when I put subforms on the main form but the users don't like the look and it looks clumsy. Also when I try it in the expression builder (example: = [Work Order]![Quantity] With the work order being the table and Quantity the field) I get the message "Control can't be edited" when I try to use the form. Can someone PLEASE help me?! By the way, the tables are not related.

 

[tempclerk]

Why are you trying to put unrelated data on the same form?

 

[bobbster2000]

Sorry for not making it clear but they are related "but" they don't have a common field for me to build a relationship. I just made one up in order for me to make the form.

 

[tempclerk]

I still don't understand what you're trying to do. Your form will now show one record from table 1, and you want it to show also a record from table 2 and a record from table 3. WHICH record from table 2 and which from table 3 do you want to see?

If you want to see more than one record from each of the other tables, I think a subform is the only way to go. You can change the look of it.

 

[bobbster2000]

I just need to use one Form to update 11 tables at the same time. But I don't know how to reference the other tables when I'm making the form.

 

[Maquis]

Do these 11 tables have the same structure? (Same fields)

If so, then you may want to think about combining those tables into 1 table with and extra field to distinguish which type of record/table it is.

If you don't want to combine the tables, then perhaps you could create an unbound form with the appropiate fields and add an update button to the form. You would then need to write some code in the button's click event to manually update these tables.

If the 11 tables do not have the same structure and they are not related to each other, then I'm as confused as Tempclerk as to how you are going to navigate through the records. You need some meaningful relationship between these tables in order to know which record in one table goes with a record in another table. Maq B-)
<insert witty signature here>

 

[bobbster2000]

The 11 tables do not have the same fields. I purposely added a field to all of them so could build a relationship between all of them. So they all have one field in common which is the Job order #. But other wise, the fields in each table are unique.

 

[tempclerk]

In design view of your form, select the form, then open the properties. Click the Data tab, then click just to the right of the Record Source box. If it asks if you want to build a query based on your table, say yes. Add your other ten tables to the query, then link them by dragging Job order # from the first table to Job order # of each of the other tables. Then drag all the fields you need from your other tables down to the query grid. Save the changes. Now all your fields are available to your form. Drag them from the field list into place on your form design.