Access Forms – Access Default Value

This post is a result of a question I had from a visitor.

They were having an issue opening a related form when there was not a related record.

One way of solving this issue is using the default value on the sub form.

2 different ways to handle a main form and sub form are having the main information on the top of the form and the sub information in a datasheet on the lower part of the form, linked to the main (parent) record on the top. Or, we can open the sub form on a click event, to the record related to the form we just clicked on.

Here is our main table information:

Here is our related, “sub”, table information:

When I create 2 simple forms (a form and subform), and add a “click” action to my button, I get this:

Here is the code for my “click” event:

Private Sub btnPatientDetail_Click()
    DoCmd.OpenForm "frmPatientSub", , , "StudyID=" & Me.StudyID    
End Sub

This works great on records with “children”, (Records: 14, 15, 16), in tblSub.

But, when we click on StudyID 17 or 23 we get a 0!

So in order to fix this, we’ll add a default value to the “frmPatientSub” form’s StudyID text box

Now when we click on a record that has no related records, like 17 or 23, we’ll get this:

We’re not actually adding the record to the table, we are “about to”, we are just defaulting the field to the “StudyID”, so we are ready to add a new record, but if we want to close the form, we are good, and no record will be added.

Do you have any questions or comments? Leave them below, or send me a comment.

By the way, if you got or are getting value from the VBA information, please give me a tip, thanks!