Adding a record when non exists

[access345]

I am trying to streamline my forms by just having one form that does two functions. My form is called frmInformation my textbox is called txtSerialNumber . On the form I have that Data entry function set to No. The primary function of the form is to check for current information. Instead of having another form just for entering new serial numbers. I want this form to do that function as well. So I need some code to check to see if the serial number exists. If the serial number does not exist. I need the serial number to be added to a table called TblBoardSerialN umber. I also need the entry to added to a counter. Because depending on the count will depend on where the unit stands in the process.

I have the follwing code:

Public Sub AddNewRecord()
On Error GoTo ERR_AddNewRecor d_1


Dim SerialNumber As Long
Dim Counter As Double
Dim SQL As String


'Verify SerialNumber exists
'If IsNull(DLookup( "ProcessCou nt", "TblBoardSerial Number", "SerialNumb er =" & SerialNumber)) Then


'End If

'Start Counter
If IsNull(DLookup( "ProcessCou nt", "TblBoardSerial Number", "SerialNumb er =" & SerialNumber)) Then
Counter = 1
Else
Counter = DLookup("Proces sCount", "TblBoardSerial Number", "SerialNumb er =" & SerialNumber) + 1
End If


DoCmd.OpenQuery strQueryName, acViewNormal, acEdit
SQL = "UPDATE TblBoardSerialN umber " & _
"SET ProcessCount = " & Counter & " " & _
"WHERE SerialNumber= " & SerialNumber & " "

CurrentDb.Execu te SQL, dbFailOnError
Exit Sub

ERR_AddNewRecor d_1:
MsgBox "Informatio n not available. Contact Administrator x412"
Exit Sub


End Sub

[nico5038]

I don't get why you use a counter for the process to see where the process is.
Normally a process should start with step 1
In your code you don't use the formfield, as that would require me.txtSerialNum ber in your code.
To check for the serialnumer you can use:
NZ(DLookup("Pro cessCount", "TblBoardSerial Number", "SerialNumb er =" & me.txtSerialNum ber)) + 1
This will return 1 when not present and serialnumber + 1 when found.

Just a start.

Nic;o)