Preventing Duplicates on Multiple Fields (Numeric & Text) (1 Viewer)

[Groundrush]

Hi Guys,

Anyone know how to prevent duplicates on the combination of two fields - text & numeric?

I'm currently using the code below that warns users when the combination of two fields have already been used. (Combination of the TWO fields has to always be unique so if used again will warn the user)

Works well when both fields are numeric but fails when the JobDetails field is changed to text in the main table (tblPPMPLanner)

Option Compare Database
Option Explicit


Private Function IsDuplicateRecord() As Boolean

    On Error Resume Next
    Dim PreviousRecordID As Long
    IsDuplicateRecord = False

    PreviousRecordID = 0
    PreviousRecordID = DLookup("TaskID", "tblPPMPlanner", "TaskID<>" & TaskID & _
        " AND BuildingID=" & cboBuildingID & " AND JobDetails=" & cboJobDetails)
    
    If PreviousRecordID <> 0 Then
        MsgBox "Duplicate record"
        IsDuplicateRecord = True
    End If

End Function

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If IsDuplicateRecord Then Cancel = 1
    
End Sub

The field that should be a text field is called "JobDetails"

Any help would be greatly appreciated

Thanks

 

[JHB]

When text you need to put the value from cboJobDetails in quotes.

PreviousRecordID = DLookup("TaskID", "tblPPMPlanner", "TaskID<>" & TaskID & _ " AND BuildingID=" & cboBuildingID & " AND JobDetails='" & cboJobDetails & "'")

 

[Groundrush]

When text you need to put the value from cboJobDetails in quotes.

Ha...I read something about that during one of my searches this morning but failed to see the importance of it.


Thanks

 

[Groundrush]

Can someone please help correct my code below please?

I'm OK with text & number fields but now need to prevent a date field duplicate but can't work out what characters to change to

& " AND MLDate=" & txtTMDate)

Full code:

PreviousRecordID = DLookup("MLID", "tblMaterialLogger", "MLID<>" & MLID & _
        " AND MLLINKTask=" & cboJobNo & " AND MLSupplier=" & cboSupplier & " AND MLAmount=" & txtAmount & " AND MLDate=" & txtTMDate)

Thanks

 

[namliam]

& " AND MLDate=#" & txtTMDate & "#")

txtTMDate must already be a date or be a text in MM/DD/YYYY format.
If it is date, you may need/want to enforce the proper format by using:
& " AND MLDate=#" & Format(txtTMDate, "MM/DD/YYYY") & "#")

 

[Groundrush]

Thanks, used your second option