If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Opening a recordset query
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 |
#2
|
|||
|
|||
Opening a recordset query
"stevens" wrote in message
. com... 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) -- I don't check the Email account attached to this message. Send instead to... RBrandt at Hunter dot com |
#3
|
|||
|
|||
Opening a recordset query
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/WileyT...764559036.html "stevens" wrote in message . com... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Hidden files in Ms-Query cause ODBC connect errors or Query is wac | needyourhelp | General Discussion | 4 | July 12th, 2004 09:38 PM |
Query in datasheet view yields different results than opening it as a recordset | Jason Seaver | Running & Setting Up Queries | 3 | July 7th, 2004 05:00 PM |
!Recordset from a parameterized query, as the form's recordset. Problem on sorting... | Savvoulidis Iordanis | Using Forms | 2 | June 24th, 2004 08:52 AM |
Stopping query when opening in design view | Dkline | Running & Setting Up Queries | 1 | June 16th, 2004 09:50 PM |
Query based recordset | Brian | New Users | 9 | May 21st, 2004 03:51 PM |