check for duplicate records using text boxes

[dodyryda]

Hi ..

I have a few text boxes on my form that I would like to check for duplicates before adding a record.. I managed to cobble the code below togther but think there is a problem in my sql... can anyone suggest a possible solution

Dim Rst As Recordset
Dim S As String
S = Me![ID] & Me![BR] & Me![JO] & Me![CO] & Me![SI]
Set Rst = CurrentDb.OpenRecordset("SELECT [ID] & [BRANCH] & [JOB TYPE] & [SITE LOCATION] AS T FROM JobSpec WHERE ((([ID] & [BRANCH] & [JOB TYPE] & [SITE LOCATION])='" & S & "'));", dbOpenDynaset)
If Rst.RecordCount <> 0 Then
MsgBox "Duplicates Found"
Cancel = True
End If

'CLEAN UP MEMORY AT END
If Not Rst Is Nothing Then
   Rst.Close
   Set Rst = Nothing
End If
End Sub

 

[MarkK]

Why do you think there is a problem with your SQL? Do you get an error? Do you get a wrong result?
If we're doctors and you want a diagnosis, then you need to describe the symptoms. Then maybe we can run some tests.

 

[dodyryda]

well i click the command button i have set this code on, to check for the duplicate then it allows the add record even though i have created a duplicate of the values for testing purposes... so I'm figure the select query is not setting rst with the correct values.. ie maybe my syntax for the sql statement is a bit wobbly.. the values of the text boxes Me![ID] & Me![BR] & Me![JO] & Me![CO] & Me![SI] are the same as the [ID] & [BRANCH] & [JOB TYPE] & [SITE LOCATION] fields duplicate created in my jobspec table... this is the full procedure

Private Sub Command50_Click()

Dim Rs As DAO.Recordset
Dim S As String
S = Me![ID] & Me![BR] & Me![JO] & Me![CO] & Me![SI]
Set Rst = CurrentDb.OpenRecordset("SELECT [ID] & [BRANCH] & [JOB TYPE] & [SITE LOCATION] AS T FROM JobSpec WHERE ((([ID] & [BRANCH] & [JOB TYPE] & [SITE LOCATION])='" & S & "'));", dbOpenDynaset)
If Rst.RecordCount <> 0 Then
MsgBox "Duplicates Found"
Cancel = True
Else
Set Rs = Me.Recordset.Clone
Rs.AddNew
Rs![ID] = Me![ID]
Rs![Branch] = Me![BR]
Rs![Deliver Time] = Me![DE]
Rs![Job Type] = Me![JO]
Rs![Company Name] = Me![CO]
Rs![Site Location] = Me![SI]
Rs![Equipment] = Me![EQ]
Rs![Purchase Goods] = Me![PU]
Rs![Driver] = Me![DR]
Rs.Update
End If

'CLEAN UP MEMORY AT END
If Not Rs Is Nothing Then
   Rs.Close
   Set Rs = Nothing
End If


End Sub

 

[MarkK]

OK.
The string that you assign to S is composed of 5 values drawn from the form, however the WHERE clause in your SQL only compares S against 4 fields in the table. This will yield an incorrect result without generating an error.
In addition, since your SQL is only designed to count records, it's not necessary for it to return a concatenation of field values 'AS T.'

S = Me.ID & Me.BR & Me.JO & Me.CO & Me.SI
Set Rst = CurrentDb.OpenRecordset( _
  "SELECT ID " & _
  "FROM JobSpec " & _
  "WHERE ID & BRANCH & [JOB TYPE] & [SITE LOCATION] & [COLOR="Red"]Field5[/COLOR] = '" & S & "';")

A couple of minor things...
1) What effect does setting Cancel = True have? A button click is not an event that you can cancel.
2) In your clean up 'Not Rs Is Nothing' will always be true so there is no need for the If..End If block.
Cheers,

 

[dodyryda]

thanks ..lagbolt .. works a charm!