CODEWITHSUNDEEP
vbams-access
realjapaner84
realjapaner84

Reputation: 37

Access VBA: Recordset doesn't work with a specific query?

first off: I'm not an expert, I'm pretty much a noob at VBA. onto my question:

So I'm using a Recordset in VBA, if the Recordset I open is a table or a simple query, it works, but I have another query that I want to base that recordset on, but when I do that, it doesn't work.

The query I'm trying to use with the Recordset basically contains a field "user" that changes based on the textbox of a form. Is it not possible to open that query as recordset because it depends on the textbox of a form or am I missing something in the code?

Here my code for reference:

Dim db As Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblExample")

Do Until rs.EOF
    Debug.Print rs.Fields("FieldExample")
    rs.MoveNext
Loop

rs.Close
db.Close
Set rs = Nothing
Set db = Nothing

I also tried replacing "tblExample" with an SQL string that is basically the same as the query I'm trying to use, with no success. The query itself does work without any problems though.

The query I'm trying to use (shortened version):

SELECT Inventar.nutzName FROM Inventar WHERE (((Inventar.nutzName) Like "*" & [Formulare]![frmInventarNutzerFilter]![cbxNutzer] & "*"))

Upvotes: 0

Views: 1428

Answers (1)

Gustav
Gustav

Reputation: 55806

Your code cannot "see" the parameter value, so you will have to pass this explicetly:

Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset

Set db = CurrentDb
Set qd = db.QueryDefs("YourQuery")
qd.Parameters(0).Value = [Forms]![frmInventarNutzerFilter]![cbxNutzer] 
Set rs = qd.OpenRecordset()

Do Until rs.EOF
    Debug.Print rs.Fields("FieldExample")
    rs.MoveNext
Loop

rs.Close
qd.Close
db.Close
Set rs = Nothing
Set db = Nothing

Upvotes: 1

Related Questions