How check for existing record and edit if exists otherwise create new record

I want to open a form to edit a record if it already exists OR create a new record if it does not exist.  The form is bound to a table.  But two field criterias have to be met in determining if the record exists already or not.  How can I do this?
SteveL13Asked:
Who is Participating?
 
TinTombStoneConnect With a Mentor Commented:
The following uses the DLookup() function to check for a particular CustomerID before opening the form in Add (not found) or Edit (found) mode

Sub OpenFormInAddOrEditMode()

Dim custID As String

    custID = "CUST1"

    If Not IsNull(DLookup("[CustomerID]", "[tblCustomers]", "[tblCustomers].[CustomerID]='" & custID & "'")) Then
    'or for a numeric field
    'If Not IsNull(DLookup("[CustomerID]", "[tblCustomers]", "[tblCustomers].[CustomerID]=" & custID)) Then
        DoCmd.OpenForm "frmCustomers", acNormal, , "[CustomerID]='" & custID & "'", acFormEdit, acWindowNormal
    Else
        DoCmd.OpenForm "frmCustomers", acNormal, , , acFormAdd, acWindowNormal
    End If

End Sub
0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
i suggest that you add an openargs option when opening the form in add mode

DoCmd.OpenForm "frmCustomers", acNormal, , , acFormAdd, acWindowNormal,openargs:=custID

then in the open event of the form, use this codes to set the value of the field automatically

private sub form_load()

if me.openargs & ""<> "" then
  me.custID=me.openargs
end if

end sub
0
All Courses

From novice to tech pro — start learning today.