Insert (1 Viewer)

[mike60smart]

Hi Everyone
I have crossposted this question https://www.utteraccess.com/topics/2065471/posts/2819007
but not understanding Doug's answer.

I have a Continuous Form that displays a list of all students with their current StudentID, ClassID, GradeID and DateFrom

as shown in the attached Current List.

I have an On Click event as shown below which should Insert records for each Student.

It inserts new records for each student but inserts the same values for each Student as shown in the Immediate Window output shown below.

INSERT INTO tblStudentsGrade ( StudentID, ClassID, GradeID, DateFrom) VALUES ( 1, 4, 6,#25-Nov-2023#);

Private Sub cmdInsert_Click()

10        On Error GoTo cmdInsert_Click_Error
          Dim lngStudentID As Long
          Dim lngClassID As Long
          Dim lngGradeID As Long
          Dim dteDateFrom As Date

20              lngStudentID = Me.StudentID
30              lngClassID = Me.ClassID + 1
40              lngGradeID = Me.GradeID + 1
50              dteDateFrom = Date
          Dim rsTemp As DAO.Recordset
          Dim strSQL  As String
          Dim i As Integer

          'Create a copy of this forms Recordset

60        Set rsTemp = Me.RecordsetClone
70        rsTemp.MoveFirst

          'Loop through all records and insert records....
80        For i = 1 To rsTemp.RecordCount
120           strSQL = "INSERT INTO tblStudentsGrade ( StudentID, ClassID, GradeID, DateFrom) " _
                  & "VALUES ( " & lngStudentID & ", " & lngClassID & ", " & lngGradeID & "," & Format(dteDateFrom, "\#dd\-mmm\-yyyy\#") & ");"
130           Debug.Print strSQL
140           CurrentDb.Execute strSQL, dbFailOnError
150       Next i
          'Release resources

160       rsTemp.Close

170       Set rsTemp = Nothing
180       Me.Requery
190       On Error GoTo 0
200       Exit Sub

cmdInsert_Click_Error:

210       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdInsert_Click, line " & Erl & "."

End Sub

Can anyone help with the VB Code to carry out this Insert process?
Any help appreciated

 

[ebs17]

As a suggestion - I prefer proper queries instead of loops.

Dim sSQL As String
sSQL = "INSERT INTO tblStudentsGrade ( StudentID, ClassID, GradeID, DateFrom)" & _
      " SELECT RS.StudentID, RS.ClassID + 1 AS C, RS.GradeID + 1 AS G, Date()" & _
      " FROM (" & Me.RecordSource & ") AS RS"
CurrentDb.Execute sSQL, dbFailOnError

Your problem so far: The values in the query remain constant, so you always insert the same thing.
If you want to work with a recordset loop, you need a MoveNext in the loop to get a dependency on the respective record. And then you should install the recordset fields directly instead of the variables.

 

[jdraw]

Mike,
Gustav has provided code on the UA site.

 

[Pat Hartman]

I agree with @ebs17 but I would be even firmer in my suggestion - NEVER use a VBA loop when an action query can do the job. PERIOD.

However, I'm pretty sure that the +1 in the append query example will not provide a valid solution since we cannot depend on Autonumber values having any meaning. However if the "ID" fields are not autonumbers but actual sequence numbers, then the concept will work.