(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