[AccessD] Record navigation - find versus new

Bob

The FindRecord action requires that the focus is on the control for the 
field being referenced.  Therefore you are going to need this in your code:
Me.TransactionsEntryNumber.SetFocus

However, without trying it, I'm not sure exactly how this is going to go, 
given that your code relates to the Exit event of that very control.  I 
don't think it will work to set focus to a control on its own Exit event. 
If it was mine, I would use an unbound textbox for the user to initially 
enter the number in, and use the After Update event of that control to run 
your code.  If the number doesn't already exist, have your code write it to 
the TransactionsEntryNumber field.  If it does, use SetFocus as mentioned 
above, prior to your FindRecord.  (In both cases, have the code clear the 
unbound textbox: Me.MyTextbox = Null - to avoid confusion.  In this type of 
scenario I normally have a different background colour for an unbound 
textbox or combobox, to make it clearer that it is for a special purpose.)

Regards
Steve

-----Original Message----- 
From: Bob Gajewski
Sent: Sunday, October 16, 2011 4:58 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Record navigation - find versus new

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