[AccessD] Record navigation - find versus new

What are the Data Entry and Allow Edits properties on the form set to?

Charlotte Foust
On Sat, Oct 15, 2011 at 8:58 AM, Bob Gajewski <rbgajewski at roadrunner.com>wrote:

> 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
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
>
>
> Website: http://www.databaseadvisors.com
>
>
>
>