Full Version: design question re subforms
UtterAccess Discussion Forums > Microsoft® Access > Access Forms
lanimc
I created several tables then used one as the basis for a main form (servicelog) with a subform (from the fields of a separate table) becoming visible depending on the choice made in a combo box(agency or client). To do this I created both forms than dragged the subform into the main form in design view, then wrote select case code to make the subforms visible/not visible.
ach subform has it's own subforms (Ex. Agency subform has an agencymeeting subform that is visible depending on the choice made in the contacttype comobox in Agency.)
How do I ensure that entries made in any of the subforms are linked exclusively to the initial record in servicelog? Does this happen automatically? Do I have to specify any relationships? Right now only ServiceLog has an automatic counter as a primary key. the other tables do not include primary keys or autonumbers...
mhartman
Hello:
You must relate your tables via a primary key and foreign key field. Enclosed below is the procedure for establishing the relationship.
'
Define relationships between tables
When you create a relationship between tables, the related fields don't have to have the same names. However, related fields must have the same data type unless the primary key field is an AutoNumber field. You can match an AutoNumber field with a Number field only if the FieldSize property of both of the matching fields is the same. For example, you can match an AutoNumber field and a Number field if the FieldSize property of both fields is Long Integer. Even when both matching fields are Number fields, they must have the same FieldSize property setting.
For a one-to-many or a one-to-one relationship
Close any tables you have open. You can't create or modify relationships between open tables.
Press F11 to switch to the Database window.
Click Relationships on the toolbar.
If you haven't yet defined any relationships in your database, the Show Table dialog box is automatically displayed.
If you need to add the tables you want to relate and the Show Table dialog box isn't displayed, click Show Table on the toolbar.
Double-click the names of the tables you want to relate, and then close the Show Table dialog box. To create a relationship between a table and itself, add that table twice.
Drag the field that you want to relate from one table to the related field in the other table.
To drag multiple fields, press the CTRL key, click each field, and then drag them.
In most cases, you drag the primary key field (which is displayed in bold text) from one table to a similar field (often with the same name) called the foreign key in the other table.

The Edit Relationships dialog box is displayed. Check the field names displayed in the two columns to ensure they are correct. You can change them if necessary.
Set the relationship options if necessary. For information about a specific item in the Edit Relationships dialog box, click the question mark button , and then click the item.
Click the Create button to create the relationship.
Repeat steps 5 through 8 for each pair of tables you want to relate.
When you close the Relationships window, Microsoft Access asks if you want to save the layout. Whether you save the layout or not, the relationships you create are saved in the database.
Note You can create relationships using queries as well as tables. However, referential integrity isn't enforced with queries.
Regards
Mark
lanimc
Thanks a bunch!
lanimc
Hi again.
I've created the relationships but when i want to enter data into one of the subsub forms (a subformwithin a subform) i get the error message saying i cannot add or delete a record because a related record is required in the primary table..what gives? how should i be relating the sub sub forms? all back to the primary record or to the form it is located in?
mhartman
Hello
It looks to me like your trying to enter something in a subform record without first entering something in the primary record. You must enter your primary record first, on the one side, then you can enter records on the many side.
Regards
Mark
lanimc
yep, you were right. some earlier test data in the sub form was recorded with no data in the main form. Thanks for all your help
mhartman
Hello:
Your welcome!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.