|
|||||||||
|
|||||||||
|
|||||||||
 |
|||||||||
| |
||
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
ASP Free Forums Sponsor:
|
|
|
|
#1
February 11th, 2004, 02:03 PM
|
||||
|
||||
|
Passing Parameters to a Query
I'm trying to do something which should be simple, but I almost never program MS Access... I want a drop-down combobox that gives users readable values they can pick from, which launches the query with the correct (encoded) parameter(s), no prompting. The combobox is no problem; passing the parameters is.
I've got a query "qryTEST2" that starts off like this: Code:
PARAMETERS [Enter Group Code] Text ( 255 ); TRANSFORM Count([Activity Codes].[Description]) AS CountOfDescription SELECT [Activity Codes].[Description] ... Opening it of course prompts to "Enter Group Code" and successfully runs. I'm trying to make a listbox control on a form launch the query with the selected parameter. JezWalter's post at http://www.experts-exchange.com/Dat...Q_20585825.html (mgrattan's answer is similar.) includes the following (modified): Code:
Dim dbsDatabase As DAO.Database
Dim qdfQueryDef As QueryDef
Set dbsDatabase = CurrentDb
Set qdfQueryDef = dbsDatabase.QueryDefs("qryTEST2")
qdfQueryDef.Parameters("Enter Group Code") = cboList0.Value
qdfQueryDef.Execute dbFailOnError
However, here's the problem: 1.) It always gives an Error 3065, "Cannot execute a select query" 2.) I'd rather use ADO, since there's no other reason for including (and confusing) DAO in the project. Help? TIA-- ~ewall
|
|
#2
February 11th, 2004, 02:34 PM
|
|||
|
|||
|
Access doesn't require that much sophistication for a saved query (No ADO/DAO required)
In the criteria postion of the query, just reference the from (and Textbox or in your case a list box) and access will take care of transfering the parameters automatically [forms]![formname]![Listboxname] Select * from table1 where table1.PK = [forms]![formname]![cboList0] The all you do is launch/execute your query DoCmd.OpenQuery ("qryTEST2"), acViewNormal You will want to use acViewPreview if you want to preview it and not just execute it. The above statement would be different if you build it from scratch and execute it in VBA S-
|
|
#3
February 11th, 2004, 03:32 PM
|
||||
|
||||
|
So close; new error...
I figured there had to be an even simpler way, and you hit the nail on the head!
Annoyingly, I'm so close, but now getting error message #3070, "The Microsoft Jet Database Engine does not recognize '[Forms]![frmTEST2]![cboList0]' as a valid field name or expression." Of course I removed the PARAMETERS part of the query. I've confirmed the spelling of the form and control names. I've tried quoting it (bad idea--the WHERE clause filters out everything then looking for a value of "[Forms]..."!). The SQL looks good: Code:
... WHERE ([People].[EeGroup]=[Forms]![frmTEST2]![cboList0]) ... I even tried changing the syntax to Forms("frmTEST2").Controls("cboGroup").Value, just for kicks--that's apparently not valid in Jet SQL. Any idea what I'm doing wrong now? ~ewall
|
|
#4
February 11th, 2004, 03:53 PM
|
|||
|
|||
|
Can you post a sample copy of your DB for me to look at, I don't see anything wrong with it, but would like to look the whole picture
What references do you have listed in your DB (windows debuger, tools, references) S-
|
|
#5
February 12th, 2004, 11:07 AM
|
||||
|
||||
|
Hmm...
I don't think I can attach a sample copy without a lot of scrubbing. (Is there an easy way to export the table definitions as SQL?)
However, here's the SQL of the offending crosstab query: Code:
TRANSFORM Count([Activity Codes].Description) AS CountOfDescription SELECT [Activity Codes].Description FROM People INNER JOIN ([Development Area Codes] INNER JOIN ([Activity Codes] INNER JOIN DATA ON [Activity Codes].Code = DATA.Activity_Code) ON [Development Area Codes].Code = DATA.Dev_Area_Code) ON People.SAP_ID = DATA.Person WHERE (((DATA.Dev_Area_Code)<>"X") AND ((People.Status)="Active") AND ((People.EeGroup)=[Forms]![frmTEST2]![cboList0])) GROUP BY [Activity Codes].Description ORDER BY [Activity Codes].Description, [Development Area Codes].Description PIVOT [Development Area Codes].Description; Changing just People.EeGroup=... to People.EeGroup="Prod" (for example) or prompting for the value, both work fine (proving that all the names and syntax are OK); but the above does not. I can get other queries to work with this same method, however, but they all include the filtered field in the returning lines (i.e. the SELECT part). References for this Access 2000 db: 1. VBA 2. MS Access 9.0 Object Lib 3. OLE Automation 4. ADO 2.1 Thanks... ~ewall
|
|
#6
February 12th, 2004, 11:36 AM
|
|||
|
|||
|
I just don't see anything wrong with it...
If you compact and zip your DB will it be less then 5MB. If so I have an FTP site you can put it on. Let me know S-
|
|
#7
February 24th, 2004, 04:42 PM
|
||||
|
||||
Got it! Including that reference is apparently still a parameterized query... Thus, specifying the PARAMETERS clause at the beginning of the SQL statement (or letting Access put it there for you by right-clicking the tables pane in the Design View and choosing "Parameters...") helps Access recognize it.
Code:
PARAMETERS [Forms]![frmTEST2]![cboList0] Text (255); TRANSFORM etc. et all and so on... Thanks for all your help with this, sbaxter... I've learned plenty, and I'm even starting to like MS Access! ~ewall
|
|
| Viewing: ASP Free Forums > Database > Microsoft Access Help > Passing Parameters to a Query |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
