[AccessD] Record navigation - find versus new

Bob Gajewski rbgajewski at roadrunner.com
Sat Oct 15 10:58:56 CDT 2011


Hi All
 
I am having trouble with what is probably one of the most basic functions,
but I just can't figure it out and would really appreciate if someone could
point me in the right direction.
 
I have a data entry form, with one of the key fields called
TransactionsEntryNumber (text, length=8, required, indexed, duplicates not
allowed). I am trying to write the code so that when a user types in an
entry number, the code looks to see if it exists ... if it does, go to the
existing record ... if not, continue to the next field as a new record.
 
    <code>
    Private Sub TransactionsEntryNumber_Exit(Cancel As Integer)
    If IsNull(TransactionsEntryNumber) Or TransactionsEntryNumber = "" Then
        Dim Msg As String, Response As Integer
        Msg = "Do you want to have an entry number assigned?"
        Response = MsgBox(Msg, vbYesNo + vbQuestion + vbDefaultButton1,
"Entry Number Assignment")
        If Response = vbYes Then
            TransactionsEntryNumber =
fAssignEntryNumber(TransactionsFilerCodeID.Column(1),
DMax("TransactionsEntryNumber", "tblTransactions"), 1)
        Else
            Cancel = True
            Exit Sub
        End If
    Else
        DoCmd.FindRecord TransactionsEntryNumber, acEntire, True,
acSearchAll, True, acCurrent, True
    End If
    End Sub
    </code>
 
The 'new' part works perfectly ... probably because I'm starting the user in
a new record:
 
    Private Sub Form_Open(Cancel As Integer)
    DoCmd.GoToRecord , , acNewRec
    End Sub

... it's the 'find' that keeps failing (error: unable to save; would create
duplicate)
 
    <error>
    Run-time error '3022':
 
    The changes you requested to the table were not successful because they
would
    create duplicate values in the index, primary key, or relationship.
Change the data
    in the field or fields that contain the duplicate data, remove the
index, or redefine
    the index to permit duplicate entries and try again.
    </error>
 
Is it as simple as trapping the error? Or should I use two controls and
stack them?
 
Thanks, as usual
Bob Gajewski


More information about the AccessD mailing list