Access/VBA Tutorials - Code for Relationship Report with extended field information
Print extended information about each field in the Relationships Report in your database. For Microsoft Access and later. Tutorial: Relationship Report with extended field information. Examples of how to produce a complex report detailing all your relationships in your database. A relationship helps you combine data from two different tables. In an Access desktop database, you can create a relationship in the Relationships window.
Last updated April Show additional information beside each field in the Print Relationships report. Set the On Click property of a command button to: The Relationships report uses a list box for each table.
Unknown field type ' X prefix indicates a Complex data type, e. Lower case p, u, or i indicates a secondary field in a multi-field index.
Opens the relationships report with extended field information. Number of tables adjusted on the Relationships report. Only tables shown in the Relationships diagram are processed. The table's record count is shown in brackets after the last field. Aliased tables typically duplicate copies are not processed.
System fields used for replication are suppressed.
Setting margins and orientation operates only in Access and later. Dim db As DAO. Dim tdf As DAO. TableDef 'Each table referenced in the Relationships window. Dim ctl As Control 'Each control on the report. Dim lngKt As Long 'Count of tables processed.How to Make an Inventory Database in Access (Part 2) - Relationships and Queries
Open the Relationships report in design view. Open the Relationships report.
Create a relationship - Access
Name of the report. Zero-length string on failure. String to append any error message to. Database, tdf As DAO. To drag multiple fields, press the Ctrl key, click each field, and then drag them. The Edit Relationships dialog box appears. Verify that the field names shown are the common fields for the relationship.
If a field name is incorrect, click on the field name and select the appropriate field from the list. To enforce referential integrity for this relationship, select the Enforce Referential Integrity box.
Code for Relationship Report with extended field information
When you are finished in the Relationships window, click Save to save your relationship layout changes. Access draws a relationship line between the two tables.
If you selected the Enforce Referential Integrity check box, the line appears thicker at each end. This means that the Indexed property for these fields should be set to Yes No Duplicates. If both fields have a unique index, Access creates a one-to-one relationship. This means that the Indexed property for this field should be set to Yes No Duplicates.
The field on the many side should not have a unique index. It can have an index, but it must allow duplicates. When one field has a unique index, and the other does not, Access creates a one-to-many relationship. Create a relationship in an Access web app The Relationships window isn't available in an Access web app. Instead of creating a relationship in an Access web app, you create a lookup field that gets values from a related field in another table.
The field that your lookup will use as the source for values must already exist before you create your lookup field. Open the table where you want to create a new lookup field by double-clicking it in the navigation. In the above example, click the Employees table. Click in the Field Name column just below the last field in the table and type a name for your new lookup field. In the example, type Region as the field name. In the Data Type column, click the arrow and select Lookup.