Access VBA create new record on doubleclick IF

Question

I currently have a Datasheet Form wherein doubleclicking on a record will open another Single Form to that record.

Currently, from the Datasheet Form, users can create a new record by adding information to any of the fields in the New Record Row (i.e. - the blank row with an asterisk at the bottom of the datasheet). This works flawlessly for existing records but I am experiencing problems with users doubleclicking in that New Record Row before they've entered any data into any of the New Record Row fields. With no data entered into any of the New Record Row fields, there technically isn't a record, yet (confirmed by the primary key still showing "(NEW)" instead of a number). So the doubleclick in that blank new row triggers my macro to attempt opening a Single Form for a record that doesn't exist.

The macro I currently have is:

     Private Sub Project_ID_DblClick(Cancel As Integer)
        DoCmd.RefreshRecord
        DoCmd.Save
        DoCmd.OpenForm "F_Project_Detail", , , "ProjectSerialNum = " & Me.ProjectSerialNum
     End Sub

ProjectSerialNum is the primary key and it is an AutoNumber. The fix I'm envisioning is an IF statement which says if all fileds in the Datasheet row are blank, then create a new record identified by the next increment ProjectSerialNum. This IF statement would go at the very beginning of my macro, providing the subsequent "OpenForm" line with a ProjectSerialNum to cross-reference. But I don't know how to write this and I'm not sure its the best approach. Any help would be much appreciated. Thanks!

HansUp HansUp · Accepted Answer · 2021-04-28T18:27:29

You don't need to check if every field in the form's current record is empty in order to determine whether it's the "new" record. Simply check the form's NewRecord property.¹

And when the user double-clicks ProjectSerialNum in the new record, open the second form (F_Project_Detail) without supplying the WhereCondition option and then go to the new record there², or open it in acFormAdd data mode³.

Opening F_Project_Detail and then going to its new record would allow the user to navigate to an existing record. If you don't want to allow that, the acFormAdd data mode approach will allow them to add records but not access existing records.

Private Sub Project_ID_DblClick(Cancel As Integer)
    If Me.NewRecord = True Then
        DoCmd.OpenForm "F_Project_Detail"
        DoCmd.GoToRecord acDataForm, "F_Project_Detail", acNewRec
        'DoCmd.OpenForm "F_Project_Detail", DataMode:=acFormAdd ' if Add Mode preferred
    Else
        If Me.Dirty Then Me.Dirty = False
        DoCmd.OpenForm "F_Project_Detail", , , "ProjectSerialNum = " & Me.ProjectSerialNum
    End If
End Sub

Documentation:

Access VBA create new record on doubleclick IF

1 Answers