In a Microsoft Access project that is connected to a database in Microsoft SQL Server 7.0 or earlier or in MSDE 1.0, you can't save datasheet formatting changes unless the datasheet is a form. Datasheet formatting changes include:
- Resizing a column or row
- Showing or hiding columns
- Changing the gridline style
- Changing the background color
- Changing the font or the font style, size, or color
If you need to save formatting changes to an Access project table, view, or stored procedure datasheet, use the AutoForm Wizard to create the datasheet as a form (which quickly sets the RecordSource property to the datasheet and adds all the fields to the form), and then set the form's DefaultView and ViewsAllowed properties to Datasheet (which makes the form behave like a datasheet).
Troubleshoot subdatasheets
I can't insert a subdatasheet in a datasheet in an Access project.
You can't use subdatasheets in a Microsoft Access project that is connected to a database in Microsoft SQL Server 7.0 or earlier or in MSDE 1.0.
I can't see the foreign key or matching fields in my subdatasheet.
By default, Microsoft Access does not display the foreign key or matching field in the subdatasheet of a table or query. You can temporarily show the foreign key or matching field by using the Unhide Columns command (Format menu), but the next time you open the datasheet containing the subdatasheet, the column will be hidden again. Hiding or showing this field changes only the appearance of the subdatasheet; it does not modify any relationship.
In a form, you can permanently display the foreign key or matching field in a subdatasheet corresponding to a subform if the subform's source object is a form.
My datasheet containing a subdatasheet looks different from when I last saved it.
The SubdatasheetExpanded property controls the state of a subdatasheet in a table or query, or the subform control in a form. You can set the property to Yes (all rows expanded) by clicking Expand All (Format menu, Subdatasheet submenu) or No (all rows collapsed) by clicking Collapse All (Format menu, Subdatasheet submenu). Clicking one or more expand indicators to show subdatasheets for one or more rows in the datasheet doesn't change the SubdatasheetExpanded property; even if you click all expand indicators to show all subdatasheets, the property is still set to No, and all rows are collapsed the next time you open the datasheet.
My subdatasheet displays the same data for each row of the datasheet.
In a table or query, you need to specify the matching field between the datasheet and subdatasheet in the List Child Fields and Link Master Fields boxes in the Insert Subdatasheet dialog box (Insert menu, Subdatasheet command). These boxes correspond to the LinkChildFields and LinkMasterFields properties of the datasheet containing the subdatasheet.
In a form, you need to specify the matching field between the main form and the subform in the LinkChildFields and LinkMasterFields properties of the subform control on the main form.
If you have just created the query, you must save it first and then add the subdatasheet to see the fields from the query in the Link Master Fields box.
When the SubdatasheetName property of a table is set to Auto and the table is on the "one" side of a one-to-many relationship or is in a one-to-one relationship, Microsoft Access automatically creates a subdatasheet. However, the value of the SubdatasheetName property can be set to a specific table or query in the database; or it can be set to None, in which case no subdatasheet will be created. To return to the default behavior, set the value of the SubdatasheetName property to Auto.