Checking for existing entries before adding a new record

[Petrol]

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.

[jforbes]

The easiest way to do this is to use the Field's BeforeUpdate event. Something like this:

Private Sub txtWalk_BeforeUpdate(Cancel As Integer)
    If DCount("Walk", "Walks", "Walk='" & Me.txtWalk.Value & "'") > 0 Then
        MsgBox "The Current Walk has been used alreay, please supply a different Walk to continue."
        Cancel = True
    End If
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.

[Petrol]

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:

Private Sub txtWalkNumber_AfterUpdate()
 '   Check whether the specified Walk already exists, and if so display it for edit; otherwise allow new entry.
Dim rs As Recordset
 
On Error GoTo ErrorHandler
Set rs = CurrentDB.OpenRecordset("Walks")
 
txtWalkNumber.Value = UCase(txtWalkNumber.Value)
If DCount("WalkNumber", "Walks", "WalkNumber='" & Me.txtWalkNumber.Value & "'") > 0 Then   ' WalkNumber already exists
   MsgBox "We already have a Walk " & txtWalkNumber & " in the system. Edit details if desired."
   DoCmd.SearchForRecord , , , WalkNumber = txtWalkNumber
   rs.Edit
   Me.Requery
Else
   MsgBox "Enter new Walk data"
   rs.AddNew
End If
 
ExitSub:
   Exit Sub
 
ErrorHandler:
   MsgBox Err.Number & ": " & Err.Description
   Resume ExitSub
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?

[jforbes]

This might work better for you:

Private Sub txtWalkNumber_AfterUpdate()
    On Error GoTo ErrorHandler
 
     '   Check whether the specified Walk already exists, and if so display it for edit; otherwise allow new entry.
 
    Dim sWalkNumber As String
 
    sWalkNumber = UCase(Me.txtWalkNumber.Value)
    Me.txtWalkNumber.Value = sWalkNumber
    If DCount("WalkNumber", "Walks", "WalkNumber='" & sWalkNumber & "'") > 0 Then   ' WalkNumber already exists
        If MsgBox("We already have a Walk '" & sWalkNumber & "' in the system.  Would you like to Edit it instead?", vbYesNo) = vbYes Then
            ' Undo Edits and Find the Exisiting Record
            Me.Undo
            With Me.RecordsetClone
                .FindFirst "WalkNumber='" & sWalkNumber & "'"
                Me.Bookmark = .Bookmark
            End With
        End If
    Else
        MsgBox "Enter new Walk data"
    End If
 
ExitSub:
    Exit Sub
 
ErrorHandler:
    MsgBox Err.Number & ": " & Err.Description
    Resume ExitSub
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.

[Petrol]

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!

[jforbes]

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.

[Petrol]

OK; thanks. I'll need to think about this a bit more! Appreciate your advice.

[Petrol]

Your suggestion in your post #4 worked perfectly. Thanks very much for your help.

[Petrol]

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.

[jforbes]

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:

Private Sub Form_Load()
    DoCmd.GoToRecord , , acNewRec
End Sub

[Petrol]

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).

[jforbes]

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.

[Petrol]

Hmm. I was so busy trying to develop clever programming solutions that I didn't think of LimitToList.
Thank you.