Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update text field with Autonumber (Access 2003, SP-2)

    Hi,
    I have a very basic form which is used to create new records.
    There is a text field, named Order, which is used as the unique identifier for the table.
    However, when the user creates a new record, besides filling in or selecting from a number of picklists, she must remember the previous highest number entered into the "Order" field and increment that number by one.
    Without totally redeveloping this form, database and other databases which link to this database, I would like to automate the process of entering the correctly incremented text number into the "Order" field.
    One approach I have taken is to include an Autonumber field in the table, and was hoping to turn that into the appropriate text string and populate the "Order" field.
    I have created a button, using the wizard, to create the new record and was wondering how, using the Autonumber field, I can update the "Order" field at this point.

    This is what I've tried, but I get a "Invalid Use of Null" all the time
    DoCmd.GoToRecord , , acNewRec
    Forms!frmDeliverableMaintenance.Controls!Text9 = Str(Forms!frmDeliverableMaintenance.Controls!AutoO rder)
    The Text9 control is the "Order" field from the table and the AutoOrder control is the Autonumber field, which I have hidden on the form.

    I'm more than happy to accept other suggestions or approaches, as long as they are simple to implement within this one database.
    Regards
    Zinger
    Invalid Use of Null

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update text field with Autonumber (Access 2003, SP-2)

    The value of the AutoNumber field is not assigned yet when you move to a new record, It is assigned the moment you enter a value in any of the fields. So you'd have to insert a line to set a value, even if only a dummy value. If you have an order date field, you could set it to the current date.
    You don't have to refer to Forms!... in code behind the form itselfm you can use Me instead to refer to the form. This has the advantage that it won't need to be changed if you rename the form.

    DoCmd.GoToRecord , , acNewRec
    Me.OrderDate = Date
    Me.Text9 = Me.AutoOrder

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update text field with Autonumber (Access 2003, SP-2)

    Added later: AutoNumber may not always do what you want. If a user starts to create a new record, then cancels, the AutoNumber of the canceled record is not reused.

    Another option is the method described in <post#=507,042>post 507,042</post#>.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •