Solved

.AddNew not getting next AutoNumber

Posted on 2013-10-22
8
335 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Suggested Solutions

Title # Comments Views Activity
Connecting to MSSQL server from MS Access 5 51
DateAdd every year from Jun 3 24
record saved form with no buttons or X 3 20
query sort by digit 5 9
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

861 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