Creating a new recordset from an existing recordset

[Bilbo_Baggins_Esq]

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

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?

 

[pbaldy]

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.

 

[Isskint]

try
rs.Filter = "[Trans_Cust_Num]=" & Customer_Number_VBA
instead of
rs.Filter = "[Trans_Cust_Num]" = Customer_Number_VBA

 

[Bilbo_Baggins_Esq]

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?

 

[pbaldy]

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.