Opening a recordset query

[stevens]

Can someone show me the VBA code for opening a recordset based on a query
and accessing those records?
I have a form on which I have placed a combo box whose data comes from a
value list. When I make my selection from the combo box, I want the combo
box after_update event to use an SQL statement to open a recordset based on
a query. I then want the fields of the recordset to display in unbound text
boxes on my form. All I need is the SQL statement to create the recordset.
Here is what I have done but it doesn't seem to work:
"SELECT * FROM qryYear WHERE qryYear.Year = " & Me.cboYear
Do I have to first create a querydef object?
After defining a database object and setting it, and defining a recordset
object and setting it, I use Set rs = db.Openrecordset("qryYear", ,
strSQL). It opens the recordset, but does not select a record based on the
cboYear choice. I know I am doing something wrong, but can't put my finger
on it. Thanks
Steve

 

[Rick Brandt]

Can someone show me the VBA code for opening a recordset based on a query
and accessing those records?
I have a form on which I have placed a combo box whose data comes from a
value list. When I make my selection from the combo box, I want the combo
box after_update event to use an SQL statement to open a recordset based on
a query. I then want the fields of the recordset to display in unbound text
boxes on my form. All I need is the SQL statement to create the recordset.
Here is what I have done but it doesn't seem to work:
"SELECT * FROM qryYear WHERE qryYear.Year = " & Me.cboYear
Do I have to first create a querydef object?
After defining a database object and setting it, and defining a recordset
object and setting it, I use Set rs = db.Openrecordset("qryYear", ,
strSQL). It opens the recordset, but does not select a record based on the
cboYear choice. I know I am doing something wrong, but can't put my finger
on it. Thanks
Steve

You can open a Recordset against a SQL string...

strSQL = "SELECT * FROM qryYear WHERE qryYear.Year = " & Me.cboYear
Set rs = db.Openrecordset(strSQL)

 

[Graham R Seach]

Steven,

*Year* is a reserved word (that's why I've enclosed it in square brackets),
so you're better to rename that field. Also, it might help to enclose the
external literal date reference in hashes (#).

sSQL = "SELECT * FROM qryYear WHERE [Year] = #" & Me.cboYear & "#"
db.OpenRecordset(sSQL, dbOpenDynaset)

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html