Undo if same record exists

[niuginikiwi]

I have a main form and a subform.
On the maind form I have a combo called CustomerID and and text field called
OrderDate.
All I want to do is check to see if a record with the combination of
Customer and Order Date already exists and if it does, undo the insert or
edit and if it doesn't accept the insert or edit.

I have the code below that does the check but still tells em that there is a
duplicate even though I verified and know that there is no same record as the
new one that I'm just about to enter. Can anyone look at the code and tell me
where I am going wrong?

Here is the code that I gethered through this forum and is on before update
event of the main form.

Dim strWhere As String
Dim varResult As Variant
Dim strMsg1 As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"

If (Me.CustomerID = Me.CustomerID.OldValue) And _
(Me.OrderDate = Me.OrderDate.OldValue) Then
'do nothing
Else
strWhere = "(CustomerName = """ & Me.CustomerID.Column(1) & """) AND
(OrderDate = " _
& Format(Me.OrderDate, conDateFormat) & ")"

varResult = DLookup("OrderID", "tblOrders", strWhere)
If Not IsNull(varResult) Then
strMsg1 = "You already have on record" & vbCrLf &
Me.CustomerName & vbCrLf & "for " & Me.OrderDate & " on " & Format(varResult,
"\E00000") & _
vbCrLf & "CONTINUE ANYWAY?"
If MsgBox(strMsg1, vbYesNo + vbDefaultButton2, "Duplicate
Record") <> vbYes Then
Cancel = True
End If
End If
End If

 

[Jeanette Cunningham]

Hi miuginikiwi,

Change these lines
If (Me.CustomerID = Me.CustomerID.OldValue) And _
(Me.OrderDate = Me.OrderDate.OldValue) Then

To

If Me.NewRecord Or (Me.CustomerID <> Me.CustomerID.OldValue) Or _
(Me.OrderDate <> Me.OrderDate.OldValue) Then



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[niuginikiwi]

Hi Jeanette,

I changed the lines as you have suggested.
However, when I add a record with the same CustomerName from the combo
CustomerID and same date as in the OrderDate field,
it lets me through without even prompting the Duplicate Record message.

Thats what it looks like in the form Before Update event

Dim strWhere As String
Dim varResult As Variant
Dim strMsg1 As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"

If Me.NewRecord Or (Me.CustomerID <> Me.CustomerID.OldValue) Or _
(Me.OrderDate <> Me.OrderDate.OldValue) Then
'do nothing
Else
strWhere = "(CustomerName = """ & Me.CustomerID.Column(1) & """)
AND(OrderDate = " & Format(Me.OrderDate, conDateFormat) & ")"
'Debug.Print strWhere
varResult = DLookup("OrderID", "tblOrders", strWhere)
If Not IsNull(varResult) Then
strMsg1 = "You already have on record" & Format(varResult,
"\E00000") & _
vbCrLf & "CONTINUE ANYWAY?"
If MsgBox(strMsg1, vbYesNo + vbDefaultButton2, "Duplicate
Record") <> vbYes Then
'Cancel = True
Me.Undo
'& vbCrLf & Me.CustomerName & vbCrLf & "for " & Me.OrderDate
& " on "
End If
End If
End If

 

[Jeanette Cunningham]

One more change is needed:
remove the do nothing line and the Else following it on the next line.
It should look like this

Dim strWhere As String
Dim varResult As Variant
Dim strMsg1 As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"

If Me.NewRecord Or (Me.CustomerID <> Me.CustomerID.OldValue) Or _
(Me.OrderDate <> Me.OrderDate.OldValue) Then
strWhere = "(CustomerName = """ & Me.CustomerID.Column(1) & """)
AND(OrderDate = " & Format(Me.OrderDate, conDateFormat) & ")"
'Debug.Print strWhere
varResult = DLookup("OrderID", "tblOrders", strWhere)
If Not IsNull(varResult) Then
strMsg1 = "You already have on record" & Format(varResult,
"\E00000") & _
vbCrLf & "CONTINUE ANYWAY?"
If MsgBox(strMsg1, vbYesNo + vbDefaultButton2, "Duplicate
Record") <> vbYes Then
'Cancel = True
Me.Undo
'& vbCrLf & Me.CustomerName & vbCrLf & "for " & Me.OrderDate
& " on "
End If
End If
End If

 

[niuginikiwi]

Hi Jeanette,
Thank you for your help.
All your changes to my code works fine. The only problem was...
I realised that the table I used in the DLookup did only have the CustomerID
as FK and didn't have the CustomerName which I was using as the criteria. So
I changed tblOrders to qryOrder which had the field CustomerName in it which
did do the trick.

Again, thank you for your time and suggestions.
Have a wonderful weekend.
PS: below is the code if anyone is looking to use as an example.

' Check for duplicate order entry and alert if there is duplicate
Dim strWhere As String
Dim varResult As Variant
Dim strMsg1 As String
Const conDateFormat = "\#mm\/dd\/yyyy\#"

If Me.NewRecord Or (Me.CustomerID <> Me.CustomerID.OldValue) Or _
(Me.OrderDate <> Me.OrderDate.OldValue) Then
strWhere = "(CustomerName = """ & Me.CustomerID.Column(1) & """)
AND(OrderDate = " & Format(Me.OrderDate, conDateFormat) & ")"
'Debug.Print strWhere
varResult = DLookup("OrderID", "qryOrder", strWhere)
If Not IsNull(varResult) Then
strMsg1 = "You already have on record " & vbCrLf &
Me.CustomerName & vbCrLf & "for " & Me.OrderDate & " on " & Format(varResult,
"\E00000") & _
vbCrLf & "CONTINUE ANYWAY?"
If MsgBox(strMsg1, vbYesNo + vbDefaultButton2, "Duplicate Record ")
<> vbYes Then
'Cancel = True
Me.Undo
End If
End If
End If