[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

.AddNew not getting next AutoNumber

Posted on 2013-10-22
8
Medium Priority
?
357 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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 2000 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

831 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