• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 524
  • Last Modified:

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

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

1 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.
Heartless91Author Commented:
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.


Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now