Ref: Add New Record

[DarrenAmbrose]

I have some code which is used on the form field property - NotInList, As follows:

Private Sub CallNoID_NotInList(NewData As String, Response As Integer)
'If the data is not in the current call no list, requests if information required

Dim strMessage As String
Dim dbsCallNoTable As Database
Dim rstTypes As Recordset


strMessage = "The Call Number listed doesn't currently exist on this database, do you wish to add '" & NewData & "' to the database?"

If Confirm(strMessage) Then

Set dbsCallNoTable = CurrentDb()
Set rstTypes = dbsCallNoTable.OpenRecordset("CallNoTable")
rstTypes.AddNew
rstTypes!CallNo = NewData
rstTypes.Update
Me!CustomerID.Visible = True
Me!CallTypeID.Visible = True
Response = acDataErrAdded
Else
Response = acDataErrDisplay


End If

End Sub

This works fine for adding the new data to the relevant table, but I am desperately trying to advise the user that if the value is a current record it exists and they need not perform any additional actions?
Any ideas?
Darren
GB

 

[MHM]

You can just look up whether NewData is already in table:

if isnull(DLookup("[CallNo]","CallNoTable","[CallNo]=' & NewData & "'") then
' add new record
else
' NewData is in table, do not add
end if

 

[DarrenAmbrose]

Thanks for the reply. I have tried to place this in several areas of the form properties, but am still having no look. I sometimes also get a message this the argument DLookup is not optional?

Is there something I'm doing wrong?