By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,222 Members | 1,118 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,222 IT Pros & Developers. It's quick & easy.

Adding Records using DAO and variables derived from queries.

P: n/a
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. CardRequestTable. 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 CardRequestTable.

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 CardRequestTable" & _
"ORDER BY ID DESC;"
strSQLvend = "SELECT TOP 1 CardRequestTable.VendorNum" & _
"FROM CardRequestTable" & _
"ORDER BY CardRequestTable.ID DESC;"
strSQLlastcard = "SELECT Temp.LastCardNum" & _
"FROM Temp;"
Set db = CurrentDb()
Set card1 = db.OpenRecordset(strSQLnextcard, dbOpenDynaset)
Set VendNum = db.OpenRecordset(strSQLvend, dbOpenDynaset)
Set LastCard = db.OpenRecordset(strSQLlastcard, dbOpenDynaset)
Set rst = db.OpenRecordset("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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
> 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!VendorNum
Mike Storr
www.veraccess.com

Nov 12 '05 #2

P: n/a
Excellent! Thanks Mike!
"Mike Storr" <no****@somewhere.con> wrote in message news:<1y*******************@news20.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!VendorNum
Mike Storr
www.veraccess.com

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.