Prevent suspected duplicate records in form

[Wesant]

Hi,
I got this very simple order form, used by several people. Often unknowingly they would place an duplicate order for the same company. I would like to capture this before record is saved and give the operator option to cancel, add or view suspected duplicate. I managed to do that, but only with single parameter. How do I add second parameter that would check for orders done in last seven days from date of current order and alert the operator? I manage to find bits and pieces of code and had help from others, see code below, but this is limit of my capabilities.



I am getting Run-time error 3075:
Syntax error (missing operator) in query expression
'PhName="PRICELINE GOULBURN" AND OrderDate Between 5/02/2011 9:16.31 PM AND #12/02/2011 9:16:31 PM#

Table name - PBSOrder_tbl
Table field - OrderDate
Form control name - OrderDate

OrderDate is recorded as Date/Time with default value Now() to record date and time of order to capture orders placed the same day by different operator for the same company.

Here is the complete code:

Dim SID As String
Dim stLinkCriteria As String
Dim iAns As Integer
Dim rsc As DAO.Recordset
Set rsc = Me.RecordsetClone
SID = Me.PhName.Value
stLinkCriteria = "PhName=" & Chr(34) & SID & Chr(34) & " AND OrderDate Between " & DateAdd("d", -7, Me.OrderDate) & " AND #" & Me.OrderDate & "#"
If DCount("*", "PBSOrder_tbl", stLinkCriteria) > 0 Then
iAns = MsgBox("This job already exists! Add it anyway?" _
& vbCrLf & "Click Yes to add, No to jump to existing record, " _
& vbCrLf & "Cancel to go back to editing this record", _
vbYesNoCancel)
Select Case iAns
Case vbYes ' do nothing, let the record be added
Set rsc = Nothing
If MsgBox("You are about to add an order." _
& vbCrLf & vbCrLf & "Do you want to save this order?" _
, vbYesNo, "Order confirmation") = vbYes Then
DoCmd.Save
Else
DoCmd.RunCommand acCmdUndo
End If
Case vbNo
Cancel = True
Me.Undo
rsc.FindLast stLinkCriteria
Me.Bookmark = rsc.Bookmark ' move to last found record
Case vbCancel
Cancel = True ' suppress update, return to form
End Select
End If
================================================== ====
I will appreciate anyones assistance.

Regards

Wesley

[pbaldy]

As answered elsewhere, you correctly surrounded the second date/time value with #, but didn't with the first.

[Wesant]

As answered elsewhere, you correctly surrounded the second date/time value with #, but didn't with the first.

Thanks Paul ,

Sorry for duplicate post, but I can't get anywhere with my problem.

I did that, see below:

stLinkCriteria = "PhName=" & Chr(34) & SID & Chr(34) & " AND OrderDate BETWEEN #" & DateAdd("d", -7, Me.OrderDate) & "# AND #" & Me.OrderDate & "#"

Ok, using your method of debugging, I found out that the string is working correctly, but for some reason does not trigger the action prompt if the record is found. Please see code in my original post.

It will prompt for action if I try to amend an existing record within seven days range. I got no Idea why.

Can you or anyone else please help?

Regards

Wesley