Solved

.AddNew not getting next AutoNumber

Posted on 2013-10-22
8
301 Views
Last Modified: 2013-10-22
I had a bound form for users to enter data.  After data had already been entered I change it to unBound to allow users to cancel changes.  

I added the code below to btnSave.  For some reason the .AddNew has started somewhere in the lower number ranges.  It will add if it happens to find a gap in the AutoNumbers, but I need it to start grabbing the last AutoID plus one.  I have tried compact repairs on both the front  and back ends but nothing seems to be working.
Dim rst As DAO.Recordset
Dim strSQL As String
Dim intLoop As Integer
Dim fld As DAO.Field
    
    
    Set rst = CurrentDb.OpenRecordset("Select * From tblIntakeForms Where IntakeFormID = " & Nz(IntakeFormID, 0))
        If Nz(IntakeFormID, 0) > 0 Then
            rst.Edit
        Else
            rst.AddNew
        End If
        For Each fld In rst.Fields
            If fld.Name <> "IntakeFormID" Then rst(fld.Name) = Me(fld.Name).Value
        Next fld
        If Nz(Me.IntakeFormID, 0) = 0 Then rst!AddedBy = GetCurrentUserName()
        rst.Update
        Me.IntakeFormID = rst!IntakeFormID
        rst.Close
    Set rst = Nothing

Open in new window

0
Comment
Question by:KCTechNet
  • 4
  • 3
8 Comments
 

Author Comment

by:KCTechNet
ID: 39592006
I just realized this has nothing to do with the code.  I put it back to a bound form and added a cancel button that merely did a me.Undo.  I see that I still can not add a new record because the AutoNumber is out of wack (yeah, throwing around those techie terms).
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39592017
<I had a bound form for users to enter data.  After data had already been entered I change it to unBound to allow users to cancel changes. >

This is a bit heavy handed...

You can cancel changes on the Before update (or Before insert) event of the form...
Since both of these events can be canceled

    me.Undo     'cancels changes
    Cancel=True  'Cancels the event


Can I ask why you are "unbinding" the form, ...just to allow the user to cancel changes?

Just curious...


JeffCoachman
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 500 total points
ID: 39592027
Here's how to reset the autonumber:

How to reset an AutoNumber field value in Access
http://support.microsoft.com/kb/812718

 and I second Jeff's comments...especially on the un-bind.

Jim.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39592073
< I see that I still can not add a new record because the AutoNumber is out of wack>

Can you first explain what this code is doing?

And note that in either case of this:
 If Nz(IntakeFormID, 0) > 0 Then
            rst.Edit
        Else
            rst.AddNew
        End If

You are still running the For-Each field loop code...
...is this what you want...?

finally I am not sure what "out of whack" means with regards to the auto number.
Even if it is non-sequential because of deleted records, I cant see how that would stop you from adding records...?

JeffCoachman
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:KCTechNet
ID: 39592129
If the form opens with a previously added record it would already have an IntakeFormID (passed to the form in OpenArgs).  So when the user clicked save, it would Edit instead of AddNew.  Then, regardless of adding or editing, it was looping through the fields of the recordset and getting the value of the control with the same name.

But as I mentioned, i got rid of that code and went to .Undo. The code would have worked perfectly, but as mentioned, a bit overkill.

So, with the autoNumber out being out of wack, say the largest number used was 100.  For some reason, it started back over at a smaller number.  So the .Update, or even when the record was trying to be added "normally" without all this code, it was not able to add the record because of the Duplicate IntakeFormID.

Luckily I didn't have than many records in the table so I just went to the table, started to add a record, hit escape to cancel the save.  This advanced where it thought the next autonumber was.  I continued this until the next AutoNumber caught up to the "correct" number.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39592259
Perhaps I am missing something then...
If this is a true autonumber field (and not a field controlled be some custom "autonumber" code you created) Then the AN will always get the "Next" number.
This is presuming that tho old/previous value was indeed saved to the table.

I have never known a true autonumber field to go "Backward" and get a lower number...

JeffCoachman
0
 

Author Comment

by:KCTechNet
ID: 39592394
Yeah, I don't recall seeing that before either. It was an AutoNumber, no custom stuff.

 I would have used the methods posted by Jim above, but there were several other related tables using the IntakeFormID  and luckily there were not that many existing records.
0
 

Author Comment

by:KCTechNet
ID: 39592409
One thing I forgot to mention... the reason I initially thought I needed to go the code route is because I was setting some field values on open and I didn't think they could be 'undone".  But then I realized that I could hit escape to get rid of the pre-populated fields so undo would work.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now