?
Solved

.AddNew not getting next AutoNumber

Posted on 2013-10-22
8
Medium Priority
?
350 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

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…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

741 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