Link to home
Start Free TrialLog in
Avatar of Heartless91
Heartless91Flag for United States of America

asked on

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
    rst.MoveFirst
    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
      rst.MoveNext
    Loop
    
'   Pull information from roster form and allow teacher to provide additional information

AddModify:
    
    With rst
        If NewRec = False Then
            .Edit
            Else
            .AddNew
            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
    
Completed:
    Set rst = Nothing
    Set Db = Nothing
    
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Heartless91

ASKER

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.

Thanks.