Trouble inserting records

[Clamato]

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:

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
 

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!

[puppydogbuddy]

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:

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
 

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!

Try changing your code as shown below and see if helps:

If DCount("[ID]", "[tbl_assoc_queue]", "[ID]='" & [WinID] & "'") > 0 Then
    X = MsgBox("You are currently in queue", vbOKOnly)
    Me.Undo  'undo data input
    YourListbox.Requery           'requeries listbox
Else

Replace YourListbox with the actual name of your listbox. Without seeing the rest of your code, I can't determine what the X = MsgBox code is doing.

[Clamato]

Beautiful. Thank you SO much!

[Clamato]

Thanks again for the help. I have another question....I have a refresh command button on the form that requeries the listbox to show updated records if they're added by another user. I'm playing with the ontimer event of the form to Me.Refresh every 5 seconds. This seems to work, however the form becomes fairly choppy because of the refreshes. Is there a better way for a listbox to be in sync with the table with no work on the users part?

[Clamato]

When a duplicate record is added it also seems to stop the timer for some reason. I'll look into the properties of the On Timer event

[puppydogbuddy]

Thanks again for the help. I have another question....I have a refresh command button on the form that requeries the listbox to show updated records if they're added by another user. I'm playing with the ontimer event of the form to Me.Refresh every 5 seconds. This seems to work, however the form becomes fairly choppy because of the refreshes. Is there a better way for a listbox to be in sync with the table with no work on the users part?

Hi Clamato,
Yes there is, and you don't need a button to do it. Just use the afterUpdate event of your listbox as shown below (replace yourListbox with the actual name).

Private Sub YourLIstbox_Aft erUpdate()
Me.Refresh
End Sub

[Clamato]

Thanks for the reply puppy.

Well, I probably should have shared this piece of information, the form is used on a network by multiple users at the same time. The .mdb that holds the forms is copied to their local drive and opened from there, records updated via linked tables to the network drive, so it seems that the after update only works on their machine when they make a change, it doesn't reflect to anyone elses. Is there a way you know of to automatically refresh either periodically or when a record is added to the "network" table? I would think a periodical query to the table, maybe through On Timer, and if something is added or removed a refresh is performed. I have no idea on where I would start if that's even possible.... :(

[puppydogbuddy]

Thanks for the reply puppy.

Well, I probably should have shared this piece of information, the form is used on a network by multiple users at the same time. The .mdb that holds the forms is copied to their local drive and opened from there, records updated via linked tables to the network drive, so it seems that the after update only works on their machine when they make a change, it doesn't reflect to anyone elses. Is there a way you know of to automatically refresh either periodically or when a record is added to the "network" table? I would think a periodical query to the table, maybe through On Timer, and if something is added or removed a refresh is performed. I have no idea on where I would start if that's even possible.... :(

Clamato,
Ok, if you've got a split Front End/Back End setup, I don't have much experience in that area. However, I believe you need a coded routine called a FE Updater, which will periodically refresh the links for you. See the following link for a free one that can be downloaded.

http://www.granite.ab.ca/access/autofe.htm

[Clamato]

Thanks for the help again, unfortunately I'm not able to implement any outside tools without approval, this is something that definetely wouldn't be approved. Thank you though for the help, I'll wait to see if anyone else has any suggestions, otherwise having users manually refresh their list should be sufficient.

[puppydogbuddy]

Thanks for the help again, unfortunately I'm not able to implement any outside tools without approval, this is something that definetely wouldn't be approved. Thank you though for the help, I'll wait to see if anyone else has any suggestions, otherwise having users manually refresh their list should be sufficient.

You don't understand. This is code that is available from many sites;the code has varying degrees of sophistication depending on the creator. See this link for another example:

http://www.mvps.org/access/tables/tbl0012.htm