I have a continuous form that i would like to populate via a sql select statement in my VBA script. I can't seem to make this work:
sel = "Select * from table1 where a = 10;"
Set SQL = db.OpenRecordset(sel)
SQL.Requery
Add a comment
|
2 Answers
Why don't you put your select instruction in the recordsource property of the form?
Me.recordsource = "Select * from table1 where a = 10;"
if your script is in one of the form's procedures, or
myForm.recordsource = "Select * from table1 where a = 10;"
if your script is in an independant module
Comments
If you want to use a Recordset instead of just setting the RecordSource (like already suggested in Philippe Grondier's answer), you can also do this:
Set Me.Recordset = db.OpenRecordset("select ...")
I must admit that setting the RecordSource is the "standard way" (especially if you already have a SQL statement and want to populate the form with that), but I still wanted to show this alternative solution.
I usually use it to populate a form with a Recordset that is returned by a function.
answered Feb 7, 2012 at 18:06
Christian Specht
36.6k16 gold badges132 silver badges190 bronze badges
2 Comments
Philippe Grondier
Totally right, and this is also the way I do it, though I systematically use ADODB instead of DAO recordsets.
Christian Specht
Yes, me too (we're using Access as a SQL Server frontend)