Adding Records using DAO and variables derived from queries.

[PaJeeper]

I'm trying to write code that will automatically add a specified
number of records to a table using variables derived from queries. I
am attempting to do this with DAO.

Background.
I have three tables within an Access 2000 Database.
1. IssuedCards. It contains all of the card data for cards that have
been issued.

2. CardRequestTabl e. Used to hold user request for cards. This is
populated by the user.

3. temp. Used to store the last card to be processed from the last
request entered. This last card number is generated via a query that
looksup the last card issued in the IssuedCards table and adds however
many cards the user has requested from the CardRequestTabl e.

I am getting a "type mismatch" error in my loop statement. It is
telling me that a type mismatch exist between the variables card1 and
LastCard.

I have attempted this with the following code. Please forgive me, I
am very new to VBA. Thanks in advance.

Private Sub AddRecords()

Dim db As ADODB.Database
Dim rst As ADODB.Recordset
Dim card1 As ADODB.Recordset
Dim VendNum As ADODB.Recordset
Dim LastCard As ADODB.Recordset
Dim strSQLnextcard As String
Dim strSQLvend As String
Dim strSQLlastcard As String

strSQLnextcard = "SELECT TOP 1 FirstCard" & _
"FROM CardRequestTabl e" & _
"ORDER BY ID DESC;"
strSQLvend = "SELECT TOP 1 CardRequestTabl e.VendorNum" & _
"FROM CardRequestTabl e" & _
"ORDER BY CardRequestTabl e.ID DESC;"
strSQLlastcard = "SELECT Temp.LastCardNu m" & _
"FROM Temp;"
Set db = CurrentDb()
Set card1 = db.OpenRecordse t(strSQLnextcar d, dbOpenDynaset)
Set VendNum = db.OpenRecordse t(strSQLvend, dbOpenDynaset)
Set LastCard = db.OpenRecordse t(strSQLlastcar d, dbOpenDynaset)
Set rst = db.OpenRecordse t("IssuedCards" )

With rst
'Right below in the do while statment is where the type mismatch
occurs.
Do While card1 <= LastCard
.AddNew
![CardNum] = card1
![VendorNum] = VendNum
.Update
Loop
End With
End

End Sub

[Mike Storr]

> With rst

'Right below in the do while statment is where the type mismatch
occurs.
Do While card1 <= LastCard
.AddNew
![CardNum] = card1
![VendorNum] = VendNum
.Update
Loop
End With
End

End Sub

Card1 and VendNum are recordsets and have no values to equate to. You may
need to specify the field you want the value from.

![CardNum] = card1!FirstCard
![VendorNum] = VendNum!VendorN um
Mike Storr
www.veraccess.com

[PaJeeper]

Excellent! Thanks Mike!
"Mike Storr" <no****@somewhe re.con> wrote in message news:<1y******* ************@ne ws20.bellglobal .com>...

With rst
'Right below in the do while statment is where the type mismatch
occurs.
Do While card1 <= LastCard
.AddNew
![CardNum] = card1
![VendorNum] = VendNum
.Update
Loop
End With
End

End Sub

Card1 and VendNum are recordsets and have no values to equate to. You may
need to specify the field you want the value from.

![CardNum] = card1!FirstCard
![VendorNum] = VendNum!VendorN um
Mike Storr
www.veraccess.com