Alerting to duplicate IDs

[iBasho]

Hello-

My database was created for to keep track of program participant reimbursements. Participants can submit up to 3 times per 12 month period with a maximum reimbursement of $150 per 12 month period.

I would like to set up an alert to warn me if a member's ID has been entered before. Duplicates are OK, I just want to be aware that they are in there so I don't "overpay" someone. Is there a way to alert how many times their ID is actually in the database? Any suggestions on the best way to keep track?

Thanks for your help!

[Rabbit]

You can use:

DCount("*", "[Table Name]", "[ID] = 45658")

[iBasho]

Where would you put this code?

Thanks.

You can use:

DCount("*", "[Table Name]", "[ID] = 45658")

[Rabbit]

It's not the full code, by itself it does nothing. You'll need an If/Then statement to make any use of it.

I would assume you'd put it in the AfterUpdate event of the ID textbox.

[iBasho]

Thanks, Rabbit. I tried your solution but unfortunately it's not working for my situation.
In your code you set the ID to a specific number whereas I want to check a new entry ID against existing IDs already in the dtb (it's OK to have duplicate IDs). I tried to modify the code so that [ID] = [ID] and not a specific number, but I think since the code is placed on the AfterUpdate property it is looking at itself and I always get a message that the ID exists in the database although it is the first time I am entering a record with that ID.

I don't know if this makes sense so I'm also sending the code I used.

Private Sub CARDID_AfterUpdate()
If DCount("*", "[qryDay60]", "[CARDID] = [CARDID]") Then
MsgBox "This member already exists in the system"
Else
MsgBox "This member does not exist in the system"
End If
End Sub

Thanks again.
Ina

It's not the full code, by itself it does nothing. You'll need an If/Then statement to make any use of it.

I would assume you'd put it in the AfterUpdate event of the ID textbox.

[Rabbit]

Thanks, Rabbit. I tried your solution but unfortunately it's not working for my situation.
In your code you set the ID to a specific number whereas I want to check a new entry ID against existing IDs already in the dtb (it's OK to have duplicate IDs). I tried to modify the code so that [ID] = [ID] and not a specific number, but I think since the code is placed on the AfterUpdate property it is looking at itself and I always get a message that the ID exists in the database although it is the first time I am entering a record with that ID.

I don't know if this makes sense so I'm also sending the code I used.

Private Sub CARDID_AfterUpdate()
If DCount("*", "[qryDay60]", "[CARDID] = [CARDID]") Then
MsgBox "This member already exists in the system"
Else
MsgBox "This member does not exist in the system"
End If
End Sub

Thanks again.
Ina

You're very close. It should be:

"[CARDID] = " & Me.[CARDID]

The way you used it was indeed the field looking at itself. What you want is for the field to compare the value in your form's control. You may want to get rid of the else as it's more a nuisance than anything. Only need to tell them if there's a duplicate.

Also, this is for if CardID is numeric, if it's text then you need to surround it with quotes.

"[CARDID] = '" & Me.[CARDID] & "'"

[iBasho]

Works like a charm.

Thanks again.

You're very close. It should be:

"[CARDID] = " & Me.[CARDID]

The way you used it was indeed the field looking at itself. What you want is for the field to compare the value in your form's control. You may want to get rid of the else as it's more a nuisance than anything. Only need to tell them if there's a duplicate.

Also, this is for if CardID is numeric, if it's text then you need to surround it with quotes.

"[CARDID] = '" & Me.[CARDID] & "'"

[Rabbit]

Not a problem.