A2010 vba code "works" but table entries "skip" autonumber ID

Posted on 2012-09-07
Last Modified: 2012-09-10
The following code works but enters data on every other ID (records 2,4,6,etc). It does not create blank records at 1,3,5,etc. I've included the cancel and close code at the top for advice on a more efficient code if possible.

My request deals more specifically with "Private Sub Form_Load()" and the skipping of records on data entry. I'm not sure of the efficiency of the test for existing record but it seems to work. (I cobble code together taking advantage of EE and other posts.)

Thanks in advance.

Option Compare Database
Option Explicit

Private Sub cmdCancel_Click()
'   Closes the form and cancels any data input

   On Error Resume Next
   DoCmd.RunCommand acCmdUndo
   DoCmd.RunCommand acCmdUndo
   DoCmd.Close acForm, Me.Name
End Sub

Private Sub cmdClose_Click()

'   Closes the form. Data is automatically saved.

    DoCmd.Close acForm, Me.Name
End Sub

Private Sub Form_Load()

Dim Db As DAO.Database
Dim rst As DAO.Recordset
Dim frmEntry
Dim strRecord As String, strEntry As String, NewRec As Boolean
NewRec = True
Set frmEntry = Forms![frmAssignInterventions]
strEntry = frmEntry![StID] & frmEntry![TchrLast] & frmEntry![Class] & frmEntry![InterventionDate]

'   Test to see if a record already exists by testing for StID, TchrLast, Class and IntDate

    Set Db = CurrentDb()
    Set rst = Db.OpenRecordset("Interventions", dbOpenDynaset)
    If rst.RecordCount = 0 Then GoTo AddModify
    Do Until rst.EOF
        strRecord = rst.Fields("StID") & rst.Fields("TchrLast") & rst.Fields("Class") & rst.Fields("IntDate")
        If strRecord = strEntry Then
            NewRec = False
            GoTo AddModify
        End If
'   Pull information from roster form and allow teacher to provide additional information

    With rst
        If NewRec = False Then
            Me.StID = Forms!frmAssignInterventions.StID
            Me.Last = Forms!frmAssignInterventions.Last
            Me.First = Forms!frmAssignInterventions.First
            Me.Lab = Forms!frmAssignInterventions.Lab
            Me.HmSch = Forms!frmAssignInterventions.HmSch
            Me.TchrLast = Forms!frmAssignInterventions.TchrLast
            Me.Class = Forms!frmAssignInterventions.Class
            Me.Avg = Forms!frmAssignInterventions.Avg
            Me.InputDate = Date
            Me.IntDate = Forms!frmAssignInterventions.InterventionDate
            Me.cboIntArea = Forms!frmAssignInterventions.IntArea
        End If
    End With
    Set rst = Nothing
    Set Db = Nothing
End Sub

Open in new window

Question by:Heartless91
    LVL 77

    Accepted Solution

    I'm afraid you are confusing two different approaches to meeting this requirement and as a result your code contains bits of both.

    You should be EITHER adding a record to the underlying recordsource (using an  rs.Addnew/ rs.Update structure)  OR you set the form values of the current record (a new record) to specific values.

    You are doing a bit of both.  You are starting with an rs.addnew but the code that follows is acting on the form not the recordset.    So all the statements like :
     Me.Last = Forms!frmAssignInterventions.Last
    is setting the 'Last' control on the form - it is NOT doing anything to the recordset.
    'Me' refers to the object containing the code - which is the form.

    You don't do anything to add data to the recordset.  And nor do you complete the recordset process with an rs.Update.  So i'm GUESSING that the autonumber for the recordset is allocated as soon as you issue the .addnew command.  But no record is ever created so the autonumber value is just discarded - but once generated it cannot be re-used.  Meanwhile you are creating another new record in your form (with all the me.control= value commands) and that will be saved when you move away from the record in any way at all.

    Author Comment

    Two things:

    1) As I understand you, all I would need to do is remove the .addnew command so that it doesn't increment the autonumber twice ( with the .addnew and with the Me.fields)

    2) I am probably violating all sorts of proper coding. If I am, please direct me to what is best.

    To clarify the process, I have a command button on another form that lists class rosters. When the teacher clicks on it, it opens this form so that the teacher can assign the student to intervention by choosing the various combinations of options. If there is an existing record (based on Student, Teacher, Class and Intervention Date) then I want to edit the existing record. If there is no existing record, I want to create a new record.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    737 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