Problem with multi-line add new

[shaggles]

I'm trying to add multiple records by looping through one
record set and using addnew on a second recordset using
the code below but I get a Sub or Function not defined
error when I run it. What am I doing wrong? Can I open 2
recordsets at once?

Private Sub Combo30_AfterUpdate()
Dim db As DAO.Database
Dim rst1, rst2 As DAO.Recordset
Set db = CurrentDb
Set rst1 = db.OpenRecordset("SELECT tblUsers.DeptID,
tblUsers.User_Name " & _
"FROM tblUsers INNER JOIN
tblGroups on " & _
"tblUsers.UserID=tblGroups.User
ID " & _
"WHERE tblGroups.Group_Name="
& "'" & Me!Group & "'")
Set rst2 = db.OpenRecordset("tblResponse", dbOpenDynaset)
While Not rst1.EOF
rst2.AddNew
rst2("DeptID") = DeptID
rst2.Update
MoveNext
End Sub

 

[Dirk Goldgar]

I'm trying to add multiple records by looping through one
record set and using addnew on a second recordset using
the code below but I get a Sub or Function not defined
error when I run it. What am I doing wrong? Can I open 2
recordsets at once?

Private Sub Combo30_AfterUpdate()
Dim db As DAO.Database
Dim rst1, rst2 As DAO.Recordset
Set db = CurrentDb
Set rst1 = db.OpenRecordset("SELECT tblUsers.DeptID,
tblUsers.User_Name " & _
"FROM tblUsers INNER JOIN
tblGroups on " & _
"tblUsers.UserID=tblGroups.User
ID " & _
"WHERE tblGroups.Group_Name="
& "'" & Me!Group & "'")
Set rst2 = db.OpenRecordset("tblResponse", dbOpenDynaset)
While Not rst1.EOF
rst2.AddNew
rst2("DeptID") = DeptID
rst2.Update
MoveNext
End Sub

First, this declaration is incorrect (though that's not the source of
your problem):

Dim rst1, rst2 As DAO.Recordset

It should be

Dim rst1 As DAO.Recordset, rst2 As DAO.Recordset

or

Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset

The way you had it originally, rst1 was defaulting to Variant type, not
DAO.Recordset.

Second, and the cause of your problems, you failed to qualify the
MoveNext method and the DeptID field with the recordset object, rst1, to
which they belong. Change it to this:

While Not rst1.EOF
rst2.AddNew
rst2("DeptID") = rst1("DeptID")
rst2.Update
rst1.MoveNext
Wend

Note that I also added a missing Wend statement. That would have bitten
you if you hadn't bombed out before that.

Finally, don't forget to close your recordsets before the End Sub
statement:

rst1.Close
rst2.Close