ms access VBA expands subform on current

This is a workaround to expand subdatasheet when a record on the main
datasheet is clicked (selected on current)

Me.SubdatasheetExpanded = False
Me!frmSub1.SetFocus 'subdatasheet name

' on current even of the main datasheet
Private Sub Form_Current()
'Select current Record in the MainForm
DoCmd.RunCommand acCmdSelectRecord

'Hold SHIFT and CTRL, then press the DOWN Arrow to Expand all SubDatasheets
SendKeys "+^({DOWN})"

'Hold SHIFT and CTRL, then press the UP Arrow to Collapse all SubDatasheets
SendKeys "+^({UP})"

End Sub

test post

Null values in the composite index fields

Access does not allow you to put null values in fields that make a part of a composite index (typically foreign keys fields). In some situations that is an undesirable limitation. For instance, U may want to allow the user to keep it blank without the choice and built in some logic to handle these situations. In this case U need a work around.

For all such fields U can input a standard (default) value equal to 0 in the table designer. On your form a foreign key field will typically have a dropdown list which would not have 0 value. From the user perspective the field will be left out blank. However on the background, on insert of a new record with the "blank" value for the field, Access will put 0 in the table. (0 - well, any value U like)

SO if U want to use composite indexes to avoid double records (which is a good practice), but U would like to make the choice not obligatory, U can use this routine.