Good Morning,
I'm working with a form that basically add's a users windows logon ID, first name, and last name to a table. A list box on this form is then requeried once added displaying the names in this table. I'm ultimately trying to make it so the user can't add their information twice in this table. Well, you would think just making their ID the PK in the table would work, however I'm running into an issue with this.
The user clicks the command button and their info is inserted into the table by bound text boxes. Then, they click the command button again and I receive an error message that "The changes you requested to the table were not successfull because they would create duplicate values blah blah". But, this only happens on the first time. If I remove the record from the list box and add a duplicate again I only get "You can't go to the specified record" yadda yadda. I ultimately would like it to just not add the duplicate record if there is one and prompt an error message and continue.
So, I tried to use a DCount procedure to verify the ID wasn't in the table before attempting to add the record, this seems to work, when I attempt to create a dup record when the form is initially opened I receive my error message that they are already in the queue, but then I receive the access message that the changes weren't successful due to a duplicate record, as if it's attempting to still add the record. I placed a stop in the code and verified it was skipping over the add new record, so I'm not sure how this was. Here is my code:
I'm very very new to access so just flying by the seat of my pants, I've looked around and haven't been able to figure out what I'm doing wrong. Any help or suggestions would be GREATLY appreciated. Thanks so much!
I'm working with a form that basically add's a users windows logon ID, first name, and last name to a table. A list box on this form is then requeried once added displaying the names in this table. I'm ultimately trying to make it so the user can't add their information twice in this table. Well, you would think just making their ID the PK in the table would work, however I'm running into an issue with this.
The user clicks the command button and their info is inserted into the table by bound text boxes. Then, they click the command button again and I receive an error message that "The changes you requested to the table were not successfull because they would create duplicate values blah blah". But, this only happens on the first time. If I remove the record from the list box and add a duplicate again I only get "You can't go to the specified record" yadda yadda. I ultimately would like it to just not add the duplicate record if there is one and prompt an error message and continue.
So, I tried to use a DCount procedure to verify the ID wasn't in the table before attempting to add the record, this seems to work, when I attempt to create a dup record when the form is initially opened I receive my error message that they are already in the queue, but then I receive the access message that the changes weren't successful due to a duplicate record, as if it's attempting to still add the record. I placed a stop in the code and verified it was skipping over the add new record, so I'm not sure how this was. Here is my code:
Code:
Private Sub cmd_add_Click()
On Error GoTo Err_cmd_add_Click
Dim WinID As Variant
WinID = [txt_ID]
If DCount("[ID]", "[tbl_assoc_queue]", "[ID]='" & [WinID] & "'") Then
X = MsgBox("You are currently in queue", vbOKOnly)
DeleteRec 'Removes record. Added this because it seems to still be trying to add the record
renew 'requeries listbox
Else
DoCmd.GoToRecord , , acNewRec
renew
End If
Exit_cmd_add_Click:
Exit Sub
Err_cmd_add_Click:
MsgBox err.Description
Resume Exit_cmd_add_Click
End Sub
Comment