Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Location
    White Oak, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    AutoNumbering (2002-SP3)

    I'm having trouble with a table that has an autonumber as the primary key field.
    Even though the records, when written, increment the autonumber, I cannot read the autonumber field programatically. The program keeps returning a number that is from a previous record.
    In the code below, the field TripNumber in the Trips table keeps returning 163 as the record number.
    I had this problems a while ago and the only way I solved it was to copy and paste the records to a new table. But, of course, all the autonumbers were recreated.
    Here's the code I'm using:

    Dim WS As Workspace, DB As Database
    Dim Trips As Recordset


    Set WS = DBEngine(0)
    Set DB = DBEngine.Workspaces(0).Databases(0)

    Set Trips = DB.OpenRecordset("Trips")

    With Trips
    .AddNew
    !VehNumber = Me.cboVehicle.Column(0)
    !BegDate = Me.txtStartDate
    !EndDate = Me.txtEndDate
    !BegTime = Me.txtStartTime
    !EndTime = Me.txtEndTime
    !Destination = Me.txtDestination
    !Driver = Me.cboDriver.Column(0)
    !GLDept = Me.cboDept.Column(1)
    !GLAccount = Me.cboDept.Column(0)
    .Update

    End With
    '
    ' Get the trip number of this trip and place it on the form
    '
    Trips.MoveLast
    Me.txtTripNumber = Trips!TripNumber

    Me.cmdNew.SetFocus

    I'd appreciate any help you can give me,

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

    Re: AutoNumbering (2002-SP3)

    You shouldn't need to use .MoveLast. As soon as you've executed .AddNew, the new AutoNumber value is available. So you should be able to use

    With Trips
    .AddNew
    Me.txtTripNumber = !TripNumber
    ...
    End With

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Location
    White Oak, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoNumbering (2002-SP3)

    Yes, I tried that. I still get the the numer back of 163.

    Also combinations of MoveFirst, MoveNext, MovePrevious, MoveLast - all to no avail.

    Hope you can help.

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

    Re: AutoNumbering (2002-SP3)

    Perhaps your database has become slightly corrupt. First try Tools | Database Utilities | Compact and Repair Database.
    If that doesn't help, create a blank new database, and import all database objects from the problem database into it.
    You will have to set the startup options anew, if any. Non-standard references in the Visual Basic Editor as well.

  5. #5
    Star Lounger
    Join Date
    Mar 2004
    Location
    White Oak, Pennsylvania, USA
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoNumbering (2002-SP3)

    Yep, that fixed it.
    Strange thing is that the corruption occurred in the middle of the entry process - after record 163.

    Thanks for the help!

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

    Re: AutoNumbering (2002-SP3)

    Access can be quirky! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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