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!
10 1802
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.
Beautiful. Thank you SO much!
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?
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
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
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.... :(
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
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.
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
Ahhh, I didn't understand. Thanks so much!
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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
|
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...
|
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...
|
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.
...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |