473,320 Members | 1,612 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Trouble inserting records

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
10 1802
puppydogbuddy
1,923 Expert 1GB
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
Clamato
25
Beautiful. Thank you SO much!
May 22 '08 #3
Clamato
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
Clamato
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
1,923 Expert 1GB
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
Clamato
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
1,923 Expert 1GB
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
Clamato
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
1,923 Expert 1GB
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
Clamato
25
Ahhh, I didn't understand. Thanks so much!
May 23 '08 #11

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

Similar topics

6
by: Pushpendra Vats | last post by:
Hi , I am trying to insert records into database. I am trying to write the following code. On button1 click event i am inserting five records and after that i am calling the update method of...
10
by: Roger Withnell | last post by:
I'm using ASP, VBScript and SQL Server. I'm also using UTF-8 character set and so my codepage is 65001 and SQL Server datatype nvarchar. I can insert unicode characters correctly into the...
1
by: ferraro.joseph | last post by:
Hi, I'm querying Salesforce.com via their AJAX toolkit and outputting query results into a table. Currently, their toolkit does not possess the ability to do table joins via their structured...
1
by: gouse | last post by:
Hello Friends, In a Table I am inserting more than 50,000 Records one by one. It was taking a lot of time . Is it There any good approach/solution for inserting records more than 50,000 one by one...
7
by: ebindia0041 | last post by:
This is like the bug from hell. It is kind of hard to explain, so please bear with me. Background Info: SQL Server 7.0, Asp.net 1.1 with c# I'm inserting simple records into a table. But one...
3
by: veerapureddy | last post by:
Hai everybody, i like to insert some records into database from html form by entering data.my problem is how can i check , whether a record is available in database about a particular...
13
by: imnewtoaccess | last post by:
Hi, I am getting errors while inserting records in one table from another. These are the structures of two tables : file51tm_new RecordType Text
6
by: ashes | last post by:
Hi, I am creating an ecommerce website using Microsoft Visual Studio, VB.Net and MS Access 2003. I am new to VB.Net When someone wants to register on the website, they fill out a form and the...
2
by: AlexanderDeLarge | last post by:
Hi! I got a problem that's driving me crazy and I'm desperately in need of help. I'll explain my scenario: I'm doing a database driven site for a band, I got these tables for their discography...
5
by: rando1000 | last post by:
Okay, here's my situation. I need to loop through a file, inserting records based on a number field (in order) and if the character in a certain field = "##", I need to insert a blank record. ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.