By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,523 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

Trouble inserting records

P: 25
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmd_add_Click()
  2. On Error GoTo Err_cmd_add_Click
  3.  
  4. Dim WinID As Variant
  5.     WinID = [txt_ID]
  6. If DCount("[ID]", "[tbl_assoc_queue]", "[ID]='" & [WinID] & "'") Then
  7.     X = MsgBox("You are currently in queue", vbOKOnly)
  8.     DeleteRec 'Removes record. Added this because it seems to still be trying to add the record
  9.     renew 'requeries listbox
  10. Else
  11.     DoCmd.GoToRecord , , acNewRec
  12.     renew
  13. End If
  14. Exit_cmd_add_Click:
  15. Exit Sub
  16.  
  17. Err_cmd_add_Click:
  18.     MsgBox err.Description
  19. Resume Exit_cmd_add_Click
  20.  
  21. End Sub
  22.  
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!
May 22 '08 #1
Share this Question
Share on Google+
10 Replies


puppydogbuddy
Expert 100+
P: 1,923
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmd_add_Click()
  2. On Error GoTo Err_cmd_add_Click
  3.  
  4. Dim WinID As Variant
  5.     WinID = [txt_ID]
  6. If DCount("[ID]", "[tbl_assoc_queue]", "[ID]='" & [WinID] & "'") Then
  7.     X = MsgBox("You are currently in queue", vbOKOnly)
  8.     DeleteRec 'Removes record. Added this because it seems to still be trying to add the record
  9.     renew 'requeries listbox
  10. Else
  11.     DoCmd.GoToRecord , , acNewRec
  12.     renew
  13. End If
  14. Exit_cmd_add_Click:
  15. Exit Sub
  16.  
  17. Err_cmd_add_Click:
  18.     MsgBox err.Description
  19. Resume Exit_cmd_add_Click
  20.  
  21. End Sub
  22.  
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:
Expand|Select|Wrap|Line Numbers
  1. If DCount("[ID]", "[tbl_assoc_queue]", "[ID]='" & [WinID] & "'") > 0 Then
  2.     X = MsgBox("You are currently in queue", vbOKOnly)
  3.     Me.Undo  'undo data input
  4.     YourListbox.Requery           'requeries listbox
  5. 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.
May 22 '08 #2

P: 25
Beautiful. Thank you SO much!
May 22 '08 #3

P: 25
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?
May 22 '08 #4

P: 25
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
May 22 '08 #5

puppydogbuddy
Expert 100+
P: 1,923
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_AfterUpdate()
Me.Refresh
End Sub
May 22 '08 #6

P: 25
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.... :(
May 23 '08 #7

puppydogbuddy
Expert 100+
P: 1,923
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
May 23 '08 #8

P: 25
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.
May 23 '08 #9

puppydogbuddy
Expert 100+
P: 1,923
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
May 23 '08 #10

P: 25
Ahhh, I didn't understand. Thanks so much!
May 23 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.