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

Checking for existing entries before adding a new record

P: 91
I have a form to allow a user to enter a new record to a table called "Walks", with a primary key of WalkNumber. (Although it is called "walk number" it is actually short text, because it contains an alpha prefix to the numeric portion).

Usually the walk number entered by the user will indeed be a new one, so I don't want them to have to use a combo box to search to see whether the number already exists. I just want them to enter the new number in the first field (txtWalkNumber) of the form and, if all is well, continue to enter the remaining data.

However there is always the possibility that they will mistakenly enter a walk that has already been entered. Currently, if this happens the form bombs when the record is committed, because of the attempt to add a duplicate primary key. I would like to be able to put a test AfterUpdate of txtWalkNumber to check if the walk is already there, and if so display its data in the form instead of accepting new data.

Does anyone have a suggestion? I have seen similar posts but not quite close enough for me to be able to modify.
Feb 27 '17 #1

✓ answered by jforbes

This might work better for you:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtWalkNumber_AfterUpdate()
  2.     On Error GoTo ErrorHandler
  3.  
  4.      '   Check whether the specified Walk already exists, and if so display it for edit; otherwise allow new entry.
  5.  
  6.     Dim sWalkNumber As String
  7.  
  8.     sWalkNumber = UCase(Me.txtWalkNumber.Value)
  9.     Me.txtWalkNumber.Value = sWalkNumber
  10.     If DCount("WalkNumber", "Walks", "WalkNumber='" & sWalkNumber & "'") > 0 Then   ' WalkNumber already exists
  11.         If MsgBox("We already have a Walk '" & sWalkNumber & "' in the system.  Would you like to Edit it instead?", vbYesNo) = vbYes Then
  12.             ' Undo Edits and Find the Exisiting Record
  13.             Me.Undo
  14.             With Me.RecordsetClone
  15.                 .FindFirst "WalkNumber='" & sWalkNumber & "'"
  16.                 Me.Bookmark = .Bookmark
  17.             End With
  18.         End If
  19.     Else
  20.         MsgBox "Enter new Walk data"
  21.     End If
  22.  
  23. ExitSub:
  24.     Exit Sub
  25.  
  26. ErrorHandler:
  27.     MsgBox Err.Number & ": " & Err.Description
  28.     Resume ExitSub
  29. End Sub
Something that I think that was tripping you up is that field txtWalkNumber is a Bound Field, meaning whenever something is typed into the Field on a New Record, Access will attempt to create a New Record in the Database out of it. Where in an Unbound Field, you can put text in it all day long and Access wont care. So, in general practice, Bound fields should only be used when inserting or updating data in the database.

You may still want to consider doing just that. By creating a Lookup Field that is Unbound to take the User to whatever walk the User is interested in. And by using the previously mentioned validation so that a duplicate Walk can't be entered.

If you still want to keep going this way, then make sure you set the WalkNumber to have a Unique Key (Indexed with no Duplicates) in the Database so that your Users can't enter Duplicate Walks into the Database.

Share this Question
Share on Google+
12 Replies


jforbes
Expert 100+
P: 1,107
The easiest way to do this is to use the Field's BeforeUpdate event. Something like this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtWalk_BeforeUpdate(Cancel As Integer)
  2.     If DCount("Walk", "Walks", "Walk='" & Me.txtWalk.Value & "'") > 0 Then
  3.         MsgBox "The Current Walk has been used alreay, please supply a different Walk to continue."
  4.         Cancel = True
  5.     End If
  6. End Sub
First it counts how many records exist in the Walks table with the current Walk. If the Count is not Zero, then it let's the user know that there is something wrong, then sets Cancel to True, which lets Access know that there was a problem and not to let the user exit the current Field until they have fixed the data.
Feb 27 '17 #2

P: 91
Many thanks, JForbes. That's a clever solution! I put that code in my BeforeUpdate and it did the job well.

However I would really like to be able to put a test AfterUpdate of txtWalkNumber to check if the walk is already there, and if so display its data in the form instead of accepting new data. So I moved the suggested code to AfterUpdate and expanded it as follows:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtWalkNumber_AfterUpdate()
  2.  '   Check whether the specified Walk already exists, and if so display it for edit; otherwise allow new entry.
  3. Dim rs As Recordset
  4.  
  5. On Error GoTo ErrorHandler
  6. Set rs = CurrentDB.OpenRecordset("Walks")
  7.  
  8. txtWalkNumber.Value = UCase(txtWalkNumber.Value)
  9. If DCount("WalkNumber", "Walks", "WalkNumber='" & Me.txtWalkNumber.Value & "'") > 0 Then   ' WalkNumber already exists
  10.    MsgBox "We already have a Walk " & txtWalkNumber & " in the system. Edit details if desired."
  11.    DoCmd.SearchForRecord , , , WalkNumber = txtWalkNumber
  12.    rs.Edit
  13.    Me.Requery
  14. Else
  15.    MsgBox "Enter new Walk data"
  16.    rs.AddNew
  17. End If
  18.  
  19. ExitSub:
  20.    Exit Sub
  21.  
  22. ErrorHandler:
  23.    MsgBox Err.Number & ": " & Err.Description
  24.    Resume ExitSub
  25. End Sub
This doesn't work. If given a new WalkNumber it adds the record OK, but if given the number of an existing record the Me.Requery statement fails with Error 3188, "Could not update; currently locked by another session on this machine." This occurs even after a reboot where there is clearly nothing else running.
When I swapped the order of the rs.Edit and Me.Requery statements so that the Requery came first, it bombed on the Requery with Error 3020, "Update or CancelUpdate without AddNew or Edit".
Am I mistaken to think I can run the test in AfterUpdate?
Feb 28 '17 #3

jforbes
Expert 100+
P: 1,107
This might work better for you:
Expand|Select|Wrap|Line Numbers
  1. Private Sub txtWalkNumber_AfterUpdate()
  2.     On Error GoTo ErrorHandler
  3.  
  4.      '   Check whether the specified Walk already exists, and if so display it for edit; otherwise allow new entry.
  5.  
  6.     Dim sWalkNumber As String
  7.  
  8.     sWalkNumber = UCase(Me.txtWalkNumber.Value)
  9.     Me.txtWalkNumber.Value = sWalkNumber
  10.     If DCount("WalkNumber", "Walks", "WalkNumber='" & sWalkNumber & "'") > 0 Then   ' WalkNumber already exists
  11.         If MsgBox("We already have a Walk '" & sWalkNumber & "' in the system.  Would you like to Edit it instead?", vbYesNo) = vbYes Then
  12.             ' Undo Edits and Find the Exisiting Record
  13.             Me.Undo
  14.             With Me.RecordsetClone
  15.                 .FindFirst "WalkNumber='" & sWalkNumber & "'"
  16.                 Me.Bookmark = .Bookmark
  17.             End With
  18.         End If
  19.     Else
  20.         MsgBox "Enter new Walk data"
  21.     End If
  22.  
  23. ExitSub:
  24.     Exit Sub
  25.  
  26. ErrorHandler:
  27.     MsgBox Err.Number & ": " & Err.Description
  28.     Resume ExitSub
  29. End Sub
Something that I think that was tripping you up is that field txtWalkNumber is a Bound Field, meaning whenever something is typed into the Field on a New Record, Access will attempt to create a New Record in the Database out of it. Where in an Unbound Field, you can put text in it all day long and Access wont care. So, in general practice, Bound fields should only be used when inserting or updating data in the database.

You may still want to consider doing just that. By creating a Lookup Field that is Unbound to take the User to whatever walk the User is interested in. And by using the previously mentioned validation so that a duplicate Walk can't be entered.

If you still want to keep going this way, then make sure you set the WalkNumber to have a Unique Key (Indexed with no Duplicates) in the Database so that your Users can't enter Duplicate Walks into the Database.
Feb 28 '17 #4

P: 91
Hmm, that looks good. I haven't absorbed it all and it's pretty late over here (Australia) so I'll have a crack at it tomorrow afternoon. Thanks.

However I didn't understand your second paragraph about "doing just that" and the unbound lookup field. Sorry to be a bit slow! I've been fiddling with this project (my first in Access) for some time now but I still have masses to learn about Access!

WalkNumber is indeed indexed with no duplicates, so that's a start!
Feb 28 '17 #5

jforbes
Expert 100+
P: 1,107
What I was getting at with the Unbound field is to add an unbound Search field to the Form to allow you to find any given Walk. Then keep the data input simple by only Validating the WalkNumber.

The ability to go to the existing Walk is very neat. But you may find it's easier for your users to understand if you keep it simple.

It's just an idea that I thought you should consider.
Feb 28 '17 #6

P: 91
OK; thanks. I'll need to think about this a bit more! Appreciate your advice.
Feb 28 '17 #7

P: 91
Your suggestion in your post #4 worked perfectly. Thanks very much for your help.
Feb 28 '17 #8

P: 91
I thought I had it licked, but the next challenge is how to add the new record if the entered WalkNumber is new. That is, what to do after MsgBox "Enter new Walk data". If I just leave it as it is, the new record overwrites the first existing record in the Walks table. I've been blundering about with .AddNew's and .Update's, but so far to no avail.
Mar 1 '17 #9

jforbes
Expert 100+
P: 1,107
The cheap and sleazy way to fix what you are experiencing is to have your Form navigate to the New Record on Open. But that's really a band-aid for your system. This is related to what I was attempting to convey at the bottom of post #4 about Bound Fields and Unbound Fields.

To me it sounds like your kind of using a Control Bound to WalkNumber as a way for your users to Find a Record, and now it's becoming apparent how this doesn't quite work. Since the control is Bound and already showing a WalkNumber, you are better off leaving it alone and adding an Unbound Control to your Form to perform your Find function and Add Function. If you leave things as they are, sooner or later someone is going to really mess up your data and overwrite at least one of the WalkNumbers with the wrong WalkNumber and then most likely your Reports will be all wonky.

What a lot of people do, including myself is to have one Form to show and navigate through Existing Records and another to Add New Records. Often these two different Forms are launched from the Main Menu separately and Users seem to readily accept that there are two different buttons to use depending on what they are attempting to do.

The New Form would be Similar to this:


Where the User enters whatever is needed to create a new record, then clicks the OK button, which will create the new record and then open up the Edit/Navigation Form and show it.

The Edit/Navigation Form would have some Unbound Controls at the top to Filter (1) the Form based on what the User is looking for as well as some controls to Save, Refresh and unlock the Form (2). You may not care to Lock the Form, but the best code I've come across for locking a Form is by Allen Browne: Locking Bound Controls


I usually include the New Button on the Edit Form for convenience. (3)

This may be more effort than you wish to put into your database and if so, then I would recommend opening the Form and Navigating to the New Record. That would at least cut down the possibility of trouble for you:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     DoCmd.GoToRecord , , acNewRec
  3. End Sub
Attached Images
File Type: jpg GenericFormOperation.jpg (24.9 KB, 128 views)
File Type: png GenericFormOperation_New.png (5.0 KB, 133 views)
Mar 1 '17 #10

P: 91
OK, I have split my form into two separate forms as suggested. The first form, "Enter new Walk", uses your suggested DCount technique to check for duplicate records and if so executes an Undo and a Requery and exits. So far so good.

The second form, "Update existing Walk details", uses an unbound combo box to locate the wanted record. In the BeforeUpdate event of the combo box I have put a similar DCount to make sure the specified Walk exists - if the DCount=0 then I issue a message, Undo, set Cancel=True and exit the sub. However this leaves the invalid WalkNumber in the form and I can't clear it. In fact I can't find any way to exit or close the form. If I clear it in the Sub I get error 2115; if I don't, it keeps checking the cbo box and won't let me leave. If the user clears the cbo box I get Error 94 (Illegal use of null).
Mar 2 '17 #11

jforbes
Expert 100+
P: 1,107
Sounds like a lot of fun. =)

Since you are using a ComboBox for the Walk Lookup, you could do a couple things that would make things easier.
  1. Move the Code into the AfterUpdate event and remove the Cancel=True and the DoCmd.Undo lines if you have them.
  2. Set the ComboBox's LimitToList Property to True
This will make it so your Users can only enter a valid Walk into the ComboBox, but they will be able to Escape out of the ComboBox data entry if they can't find a Walk like they expected.
Mar 2 '17 #12

P: 91
Hmm. I was so busy trying to develop clever programming solutions that I didn't think of LimitToList.
Thank you.
Mar 3 '17 #13

Post your reply

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