Creating a new recordset from an existing recordset

Bilbo_Baggins_Esq

Registered User.
Local time
Today, 09:59
Joined
Jul 5, 2007
Messages
586
I know this is sitting right in front of me, but for some reason, it is eluding me...

Running Access 2010 x64 on Win7 x64

Code:
Function Pull_Existing_Payments()

Dim ws As Workspace
Dim db As Database, rs As Recordset, rsFiltered As Recordset
Dim Customer_Number_VBA As Double
Dim Trans_Num_VBA As Double

Set ws = DBEngine.Workspaces(0)
Set db = ws.Databases(0)
Set rs = db.OpenRecordset("Transactions", dbOpenDynaset)

rs.Filter = "[Trans_Cust_Num]" = Customer_Number_VBA
Set rsFiltered = rs.OpenRecordset

rsfiltered.MoveFirst
Trans_Num_VBA = rsfiltered!Trans_Num

MsgBox Trans_Num_VBA

rs.Close

End Function

This fires a Run-time error '13': Type Mismatch error on the rs.Filter line.

In the database, "Trans_Cust_Num" is formatted as a number.
In the code, I've tried Customer_Number_VBA "As Double" and "As Long" and "As Integer" with no luck.

Anybody have any suggestions?
 
For starters, why two recordsets instead of one opened on an SQL statement that gets the desired records?

Set rs = db.OpenRecordset("SELECT * FROM Transactions WHERE [Trans_Cust_Num] = " & Customer_Number_VBA", dbOpenDynaset)

For another, you never set the variable's value and the filter syntax is off anyway.
 
try
rs.Filter = "[Trans_Cust_Num]=" & Customer_Number_VBA
instead of
rs.Filter = "[Trans_Cust_Num]" = Customer_Number_VBA
 
well, you caught me, a little.
The Customer_Number_VBA value is obtained in a different function.
Technically, it is a Public variable, I just addd the Dim line to the posted code so you could see I was creating it and the type applied to it.

Thanks for the idea on running the SQL!

But, using the two recordset method, what should the filter syntax be anyway?
 
If you wanted to filter a recordset, I'd just filter the first rather than trying to open a second, or I'd open the second the same way I suggested above, with an SQL statement.
 

Users who are viewing this thread

Back
Top Bottom