check for duplicate entries on same date 2

[THWatson]

Using Access 2000

On a form "frmAppointments", based on "tblDates", two fields are MyDate, bound to MyDate in the table, and cboEvent bound to EventID in the table.

What I want to do is check for duplicate entries on the same date that have the same EventID when the EventID is > 3.

Here is the code I have been working with on the AfterUpdate event of cboEvent...

Private Sub cboEvent_AfterUpdate()
Dim intAnswer As Integer

Dim dteTemp As Date
Dim eventTemp As Variant

dteTemp = DLookup("MyDate", "tblDates", "[MyDate] = " & "#" & Forms!frmAppointments!MyDate & "#")
eventTemp = DLookup("EventID", "tblDates", "[EventID] = Forms!frmAppointments!cboEvent And [EventID] > 3")

If Not IsNull(dteTemp) And Not IsNull(eventTemp) Then
intAnswer = MsgBox("You already have an entry for this event on this day." & vbCrLf & "" _
                   & "          Is this a duplicate entry?" _
                   , vbYesNo Or vbExclamation Or vbDefaultButton1, "Possible Duplicate")
Select Case intAnswer
    Case vbYes
        Call MsgBox("      Thank you." _
                    & vbCrLf & "Entry will be cancelled." _
                    , vbExclamation Or vbDefaultButton1, "Duplicate entry")
        Me.Undo
        Me.MyDate.SetFocus
    Case vbNo
       Call MsgBox("       Thank you." _
                    & vbCrLf & "Continue with your entry." _
                    , vbExclamation Or vbDefaultButton1, "Entry process continuing")
End Select

End If
End Sub

This works if an event already exists on that date and has an EventID of 1, 2 or 3. However, if the EventID is greater than 3 the "possible duplicate message" appears if there is an event on any date in the table.

I also tried putting dteTemp and eventTemp together into a string, but this didn't work. I also tried declaring dteTemp as Variant rather than Date, but that didn't work.

I also tried replacing the DLookup functions with the following...

Dim strSQL As String
Dim strTemp As Variant
strSQL = "SELECT tblDates.MyDate, tblDates.EventID " _
& "FROM tblDates " _
& "WHERE (((tblDates.MyDate)=Forms!frmAppointments!cboEvent) AND ((tblDates.EventID)>3));"
DoCmd.RunSQL strSQL
strTemp = strSQL

but this results in a runtime error 2342 (argument consisting of an SQL statement required)

Any suggestions as to how to make the procedure work?

Thanks.

Tom

 

[AmigoMobility]

Hey Tom,

I'll take a shot at something here and hope I'm not chasing a rabbit but try changing eventTemp to this:

eventTemp = DLookup("EventID", "tblDates", "[EventID] = " Forms!frmAppointments!cboEvent & " And [EventID] > 3")

I don't know for sure if that will make a difference but it's worth a shot.

HTH,
Shane

 

[THWatson]

Shane
Yep, was worth a shot but unfortunately, that doesn't do it.

This rabbit is going to need a bigger snare, I guess.

Thanks for trying.

Tom

 

[AmigoMobility]

Ok, Tom, I don't want to give up yet and since you don't seem to have any other takers then lets see if we can't build a snare. After the DLookup for eventTemp put MsgBox eventTemp and then run this on something that you know will come out wrong and let's see what it tells us the value is of eventTemp in the message box.

Shane

 

[PaulF]

You might consider something like this where you first test to see if cboEvent is >3, if so, then check for dupes using DCount instead of DLOOKUP
if cboEvent>3 then

if dcount("MyDate", "tblDates", "[MyDate] = #" & Forms!frmAppointments!MyDate & "# AND [EventID] = " & Forms!frmAppointments!cboEvent) > 0 then
msgbox "Dupe"
else
'do this
end if

end if

PaulF

 

[THWatson]

Shane And PaulF

It appears that PaulF has built the snare!

Interestingly enough, Shane, the Message Box idea produced exactly the correct results...so the problem lies in Access interpreting from thereon. However, the good news is that Paul's solution works.

Thanks much to both of you. I always appreciate assistance.

Tom

 

[AmigoMobility]

Tom,

Your welcome. Wish I could of been more help to ya. I thought about DCount but couldn't get my mind to wrap around the right way to get it done. When I saw Paul's post I was glad he stepped in cause I said to myself "yea, what he said."

Shane