Check for existing record

[mayday999]

Hi all,

I have a form with two comboboxes which upon clicking a save button writes these two values to a table. Does any one have code to check to see if the combination of the two values already exists in the table I am writing to so I won't get duplicates of the same combination. Any help is greatly appreciated.

Thanks,

mayday

 

[PHV]

Have a look at the DLookUp function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mayday999]

PHV,

Not sure how this would work, I am kind of a novice.

Thanks,

mayday

 

[TheAceMan1]

How are ya mayday999 . . .

Here's a sample function you can use (the code assumes [blue]text[/blue] data):

[blue]Public Function CombinExist() As Boolean
   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String
   
   Set db = CurrentDb
   SQL = "SELECT FieldName1, FieldName2 " & _
         "FROM tablename " & _
         "WHERE ((FieldName1='" & Me!ControlName1 & "') AND " & _
               "(FieldName2='" & Me!ControlName2 & "'));"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If Not rst.BOF Then CombinExist = True
   
   Set rst = Nothing
   Set db = Nothing
   
End Function[/blue]

See Ya! . . . . . .

 

[PHV]

so I won't get duplicates of the same combination
A common way is to create an unique composite index on the 2 fields.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886