Scanning table for duplicate entry from VBA

[Buakaw]

Hi


I have a form with unbounded text boxes where a user enters payment details. When he clicks "Submit" I want to scan the table where payment details are stored to see if a duplicate payment had already been made in the past, before I enter this new entry.

How can I do a scan of the table to see if a duplicate entry exists? I have looked at RunSQL but I don't think it returns a value.

Thanks!

[RuralGuy]

Maybe you want to look at DCount or DLookup: http://www.mvps.org/access/general/gen0018.htm
If it is the RecordSource of your form then .FindFirst would be faster.

[Buakaw]

Thanks RuralGuy!

I'm getting a "type mismatch" error when I do this:

duplicateCount = DCount("[fkStudentID]", "tblFeesPaid", "[fkStudentID] = '" & Me.fkStudentID & _
"' AND [MonthPaid] = '" & Me.MonthPaid & "'")

tblFeesPaid.fkStudentID is a number. I checked this part out in the Immediate window and it isn't causing the problem.

Me is a form that I'm on, and Me.MonthPaid is a text box containing a date.

tblFeesPaid.MonthPaid is a Date/Time type.

I tried to do CStr(Me.MonthPaid) and CDate(Me.MonthPaid) but I still get the data type mismatch error.

Any idea why?

[RuralGuy]

Look at the link I supplied again; especially referring to the three different types of values.

[Buakaw]

Ah! Got it! Thanks you!

No "'" for numbers and "#" for dates..learned something new today.

[RuralGuy]

By Jove, I think you've got it!

[Buakaw]

heheheh! thanks to you