Heartless91
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.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.