Solved Setting a Recordset to a query Criteria expression issue

[alvingenius]

Hello
I'm trying to build a module with a recordset to a query and it working as screenshot

but if i tried to but a criteria into the query, for example getting the date from the form1

and opened the form and entered a date for example 16/01/2022, then should only get 1 value in Debug.print then i get an error

its not accepting that type of expression in query ! why ?

---
Edit1 :
Solution on this post
Thanks @theDBguy ,@arnelgp

 

[theDBguy]

Hi. You could try using this generic function to create the recordset.

Code Snippets

 

[arnelgp]

...
Set db = Currentdb
With db.Querydefs("qry_files")
     .Parameters(0) = Forms!Form1!txtDate
    Set rst = .OpenRecordset(dbOpenDynaset)
End With
Do Until rst.Eof
...
...

 

[alvingenius]

I was just reading about it in the Microsoft community

DAO operates at the database level, not at the Access application level. It doesn't "know" about Access forms and combo boxes.
So, it doesn't understand "Forms!Form1!txtDate" until we define it

and @arnelgp code working great and I just have a very small edit on it, defining the parameters instead of its index incase of multiple parameters

.Parameters("[Forms]![Form1]![txtDate]") = [Forms]![Form1]![txtDate]

, and @theDBguy function I think it's the same idea without editing the code, only using,

...
Set db = CurrentDb
Set rst = fDAOGenericRst("qry_files", dbOpenDynaset)
Do Until rst.EOF
...

Thank you both for the solution

 

[theDBguy]

and @theDBguy function I think it's the same idea

That's correct. The idea of it is so you'll have a generic function to create a recordset without needing to modify your query or code. Cheers!