Syntax to check Form Field and Subform Field against a Subform Field

[Robert M]

What I have is a Form with the attendee Name and a sub form with all the attendee's Information. One of the fields in the sub form contains the date which I want to check to be sure I do not have a duplicate Date. I am not sure of the exact syntaxt (code) to enter but I believe it is something like...

If [subform].[date] = [form].[attendee] and [subform].[date] then
MsgBox "Possible Duplicate Data being Entered, Please Check"
End If
Continue entering data

Because the subform source contains All Dates from All Attendees I am needing to be sure that I am checking the correct date for the appropiate attendee.

Any help on this is greatly appreciated.

Thank you

Robert M

 

[JPearson]

You have [subform].[date] in there twice. basically it looks like your statement is comparting the [subform].[date] to the [form].[attendee] and the [subform].[date] to the [subform].[date].

appears your trying to compare the same box to itself.

Are you trying to compare the date in the subform to all dates matching that attendee in a recordset?

 

[Robert M]

JPearson Asks
"Are you trying to compare the date in the subform to all dates matching that attendee in a recordset?"

Yes I am, as I said I am not sure of the syntax of the code to get the program to do what I want.
Thank you for your help in this matter.

Robert M

 

[JPearson]

Without table structure I cant quite get specific but it should follow this pattern I believe.

query the recordset for that attendee info and date.

exisitingrecord = "select " & table.Attendee & ", " & table.date & " into TempTable from table where" & table.attendee & " = " & form.attendee.value & "and " & table.attendee & "= " & form.date.value
 
docmd.runsql existingrecord
set CheckRec = CurrentDB.openrecordset("TempTable", dbOpenDynaset)
If checkrec.recordcount > 0 then
MsgBox "Possible Duplicate Data being Entered, Please Check"
End If
 
DoCmd.Close acTable, "TempTable"
 
 
 
 
 
Set CheckRec = Nothing
DoCmd.DeleteObject acTable, "TempTable"

Not sure I have the Syntax exactly right but that should do what you need.


It creates and runs a query that will only pull back the records that match the attendee and the date value. If this query pulls back more than 0 records then your msgbox is created.
It then removes the query for the next time..

Not sure if this is the quickest or best was to handle it but its similar I used while importing excel sheets and looking for bad records or duplicate records.