Saturday 01 Apr 2023

Open Record or Create New Record - VBA on Access

(Not an April fools)

I was working with Microsoft Access 365 the other day. I wanted a script to check if a record exists then open a form, filter to that record, and if the record does not exists, create a new record. I wrote the code in the OpenRecordBtn click event. I have changed the names of the table and primary key for this example.

Private Sub OpenRecordBtn_Click() 

' This will open the record based on the record ID entered 
' Gets the value from a texy box named Record_Box

	Dim RecordID As Long 

	RecordID = Me.Record_Box.Value 

' Check if record exists in database 
' if it does not exist then create new record 
' I have used the DCount to return number of records found
' based on the RecordID. if 1 then exists, otherwise 0 does not.

Result = DCount("PrimaryKey", "tbl_records", " PrimaryKey = " & RecordID)  
' If less than 1, record does not exist, create one:

If (Result < 1) Then 

	' Insert record 
	DoCmd.RunSQL "INSERT INTO tbl_records (PrimaryKey) VALUES (" & RecordID & ")" 
    End If 

' Opens the form using the supplied record ID
' Opens form name "A Form Here" using the primary key with
' my variable named "Record ID"  
DoCmd.OpenForm "A Form Here", , , "PrimaryKey = " & RecordID 

End Sub