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
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
Comment