[AccessD] Record navigation - find versus new

DataEntry = No
AllowEdits = Yes 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
Sent: Saturday, October 15, 2011 12:57 PM
To: Access Developers discussion and problem solving
Subject: Re: [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
>
>
>
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com