loop help

[swmorin]

What I want to do is have the the insert query run one time for each record in the recordset that I pull.

I have never done anything like this before so I am posting the code I have so far and would like someone to help me out.

Private Sub Command2_Click()
Dim instr_block_reqdb As Database
Dim instr_block_reqrs As Recordset
Set instr_block_reqdb = CurrentDb
Set instr_block_reqrs = instr_block_reqdb.OpenRecordset("Select * From tbl_block_requirements where tbl_block_requirements.courseid = " & [Forms]![frm_instructor_add_course]![Combo1] & "")
CurrentDb.Execute "INSERT INTO tbl_instructor_req" _
  & "(INSTRUCTORID, BLOCKREQID, BLOCKID, COURSEID, REQTYPE, REQINTERVAL) Values " _
  & "(" & [Forms]![tbl_instructor_req]![Combo13] & " " _
  & "," & instr_block_reqrs.blockreqid & " " _
  & "," & instr_block_reqrs.blockid & " " _
  & "," & [Forms]![tbl_instructor_req]![Combo1] & " " _
  & "," & instr_block_reqrs.reqtype & " " _
  & "," & instr_block_reqrs.reqinterval & ");"
End Sub

 

[pbaldy]

Here's my template code for a loop:

  Dim strSQL  As String
  Dim db      As DAO.Database
  Dim rs      As DAO.Recordset

  Set db = CurrentDb()
  
  strSQL = "SELECT ..."
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

  Do While Not rs.EOF

    rs.MoveNext
  Loop

  set rs = nothing
  set db = nothing

 

[swmorin]

Thanks it worked great!!!

Way simpler then I thought it would be

 

[pbaldy]

No problemo!