Calling a Microsoft Access Query and Passing Parameters

[Scyclone]

Hello Everyone,



I have a form consisting of several dynamic drop-downs, the last of which contains a concatenated value of a query.

Product Name + Product Part Number

Now, once you select the product from the drop-down list. I am trying to display all of the information related to that product.

(For example, text boxes called 'ProdName' to display the Product Name as it is pulled from the access query)

I am calling a query named 'ProdPicker Query', I need to pass it the selected value of the dropdown (ItemDrop1), and retrieve back the row for that item.

Following this, how do I manipulate and place the data I retrieve into textboxes such as 'ProdName' listed above?

On VBA side, I have the following set up.

=======

Dim dbClient As Database
Dim qdf As QueryDef
Set dbClient = CurrentDb
Set qdf = dbClient.QueryDefs("ProdPicker Query")
qdf.Parameters!ProdFullName = Me.ItemDrop1
qdf.Execute

=========

The error I recieve is "Cannot execute a select query."

========

Provided below is my access query 'ProdPicker Query'. I have tested the query by itself (passing it the needed parameter) and it works with flying colors

SELECT [ProdList Query].[ID], [ProdList Query].[ProdName], [ProdList Query].[ProdPartNo], [ProdList Query].[ProdDescription], [ProdList Query].[CatName], [ProdList Query].[StatusName], [ProdList Query].[BrandName], ([ProdList Query].ProdName & " " & [ProdList Query].ProdPartNo) AS ProdFullName
FROM [ProdList Query]
WHERE ProdFullName=Forms![Product Configuration]!ItemDrop1;


========

Any help would be greatly appreciated.

Thanks,
Scyclone

[Robeen]

Try this:
Set qdf = dbClient.QueryDefs("[ProdPicker Query]")

Spaces in field, table & query names are safer when put in [].

Let me know if that helps at all.

[Scyclone]

Just gave that a shot.

Unfortunately, the error remains the same. Hmm... would CurrentDb automatically assume the access files by default?

My experience is in web-programming so I am a little fuzzy when it comes to access programming syntax.

[pbaldy]

This is the rare MS error that's actually quite descriptive. You can't execute a SELECT query. You can only execute an action query, like an append or update query. You can open a recordset on the QueryDef, or if you're trying to display the results to the user I'd probably base a form or subform on the query and open it for the user. If you really want to open the query (I wouldn't), try

DoCmd.OpenQuery

[Scyclone]

Hmm....

Unfortunately that confuses me further.

Is there no way to pass a parameter (value from a dropdown list) to a query and retrieve/use the resulting data on the same form that the dropdown exists on?

[pbaldy]

Sure; base the form on the query, and requery it in the after update event of the dropdown. As is, the form would open blank. If you use this technique, the form will open with all records displayed, then restrict upon selection in the combo:

http://access.mvps.org/access/queries/qry0001.htm

[Scyclone]

Hmmm

Not sure if that would get what I need unfortunately.

I took a screenshot and filled in data to try to help draw a better picture of what I am attempting to do.



Again sorry for the confusion, MS Access is by no means my forte.

[tdecker81]

If your original query without passing the variable worked by directly linking the form's data source based on the selection in that combo box then leave it like that.

You should be able to pass the parameter but if that isn't working unless you plan on reusing the query in multiple other places in your software to return the exact same dataset then it doesn't matter. It might not be pretty but it works.

[Scyclone]

If your original query without passing the variable worked by directly linking the form's data source based on the selection in that combo box then leave it like that.

If I understood what you were saying correctly, the query itself does not work without a variable being passed to it. What I have done is quite literally, copy + pasted, the value in the item drop down.

ProdPicker Query requires a value equal to Product Name + " " + Product Number

In this example above, ProdPicker Query would ask me to enter a value when opening it, to which I provide it "Test Product X001" and it works (providing me with the row of results I do need).

==

The second part of your response I do not understand.

I do need to pass a parameter, without it, it will not have anything to pull from.

1) Item is selected in dropdown
2) Item dropdowns value is passed as a parameter (required) to ProdPicker Query
3) ProdPicker Query returns a single row consisting of name, prod number, etc etc relevant to the parameter
4) Product Name Textbox needs to display the returned name, Product Part Number needs to display the returned Part Number, etc.

=

What would I need to do to accomplish this, preferably step by step. Forgive me for reasking the question, but I am just not able to mentally follow whats been provided thusfar, and its frustrating to myself seeing as I can easily do this in a web application setting, I just dont see MS Accesses need for overcomplication when it comes to symantecs on something so simple.

Any help is still greatly appreciated, I am banging my head on a wall at this point

[pbaldy]

This sounds like it accomplishes the goal, without the need for the query:

BaldyWeb - Autofill

[Scyclone]

Oh wow,

If I understand that right, I can actually take all the information associated and pull straight from the ItemDrop1 record set based on the column number itself.

That is awesome

[pbaldy]

As long as all the info can be pulled in through the combo row source, yes. Post back if you get stuck.

[Scyclone]

Hmm I have managed to get it half working

If I set column count to like 9, everything loads up into the boxes. However... the dropdown itself now shows 9 columns in its selection when I only want to see Column 0.

Any way to make it appear like this? Setting column count to 1 makes most return as null.

[pbaldy]

Check out the column widths property of the combo. Hint: 0 = hidden.

[Scyclone]

Just set it to 0, and it works for after the item is selected but on the dropdown itself it is

Test Pro | Cat | Bran | X00| etc etc

Strange.