473,322 Members | 1,307 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Adding Records using DAO and variables derived from queries.

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
2 2003
> 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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: tom | last post by:
I am new to SQL administration. >From a list of IDs that are the primary key in one table (i.e. Customer Table), I want to make changes in tables that use those IDs as a foreign key. ...
6
by: Robin S. | last post by:
**Eric and Salad - thank you both for the polite kick in the butt. I hope I've done a better job of explaining myself below. I am trying to produce a form to add products to a table (new...
24
by: clare at snyder.on.ca | last post by:
I have a SQL query I need to design to select name and email addresses for policies that are due and not renewed in a given time period. The problem is, the database keeps the information for every...
8
by: shumaker | last post by:
I'm wondering if adding an autonumber primary key will improve the performance of a multiuser access database on a network share. I have a website that lists many tips for improving performance of...
6
by: ninrulz | last post by:
I have created a database that has over 70 queries and over 40k records. I used the OutputTo action to export the queries to Excel. I would like the functionality of adding the date (Month-YY) to...
8
by: mantrid | last post by:
Hello Im having problems working out why the following code does not work. I dont think its the sql as the error occurs on the first update which ever one is put there ($q1 or $q2). Ive swapped...
11
by: kaisersose1995 | last post by:
Hi, I've got an import procedure working, using a standard import specification to import a .csv file into a temporary table. The problem i'm having is that i have 4 different sets of borrower...
8
by: rbg | last post by:
I did use query plans to find out more. ( Please see the thread BELOW) I have a question on this, if someone can help me with that it will be great. In my SQL query that selects data from table,...
1
by: vbDavidC | last post by:
I am adding a new record to a table via a dataset/adapter. I have got the following to work for me but I am wondering if there is a better way to do this. I am having to have something in my...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.