Solved

.AddNew not getting next AutoNumber

Posted on 2013-10-22
8
325 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

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