New records in forms

[Guest]

I have several different forms that display information about certain
companies. Many of the companies do not appear on all of the forms. Is
there a way when a new record is inserted to check if that company is already
in the database so that when a new record is created for that company it is
not given a new ID number? And if the company is not already located in the
database it should be given a new unique ID number.

 

[Guest]

Hi

Use the NotInList option - something like this

Private Sub CompanySearch_NotInList(NewData As String, Response As Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim Msg As String
Msg = "'" & NewData & "' is not on file." & vbCr & vbCr
Msg = Msg & "Do you want to add New Record?"
If MsgBox(Msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Lokk again or another message."
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("DataTable", dbOpenDynaset)

rs.AddNew
rs![CompanyID] = NewData
rs.Update
Response = acDataErrAdded

End If


Change the DataTable name to whatever you use and also the CompaySearch
name. Als the CompanyID below will need to be changed to your normal primary
key field name

Hope this helps