Create a query from drop down lists selections 1

[AT76]

Hi all,

I have added drop down lists selections in a form. What I would like a user to do is select the fileds he/she wants and then press on a button that will create a query based on the items selected. I have a query already made but I'm not sure how to put this together. Any suggestions?

Thank you!

 

[AlexCuse]

Here is an example of building a query dynamically in VBA. It is up to you to decide how to display it, either through a form or a report I imagine.

Dim strSQL as string

strSQL = "SELECT "
strSQL = strSQL & DropDown1.Value & ", "
strSQL = strSQL & DropDown2.Value 
strSQL = strSQL & " FROM TABLE WHERE SOMETHING = "
strSQL = strSQL & DropDown3.Value

This will build you a query that reads like this:

select Drop1Value, Drop2Value
from TABLE
where SOMETHING = Drop3Value

You can then assign this query to the recordsource property of a form or report for display of the data.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[AT76]

Thanks AlexCuse for your example...

As you mentioned I'm trying to do the following:

sqlstring = "SELECT FIELD1, FILED2, FIELD3, FIELD4"
sqlstring = sqlstring & " FROM tbl_Registration WHERE FIELD3 = "
sqlstring = sqlstring & Me.cmbField3.Value

But I get the following error message:

Run-Time error: '3075' Syntax Error (missing operator)

Any ideas? Thank you!

 

[AT76]

I changed my query as follows:

sqlstring = "SELECT *"
    sqlstring = sqlstring & " FROM tbl_Registration WHERE FIELD3 ="
    sqlstring = sqlstring & Me.cmbField3.Value

and I now get the following error:

Error 3065: Cannot Execute a Select Query

Any ideas? Thanks?

 

[AlexCuse]

You can't call this using the OpenQuery method. As far as I know, you need to provide a form or report that you can set the recordsource property to strSQL. So in your click event for a button, you build this query, assign it to recordsource, and then open form/report to display. Does this make sense? Post back if you have any further questions.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Eupher]

Alex,

You can't call this using the OpenQuery method.

No, but you can assign the SQL to an existing (or new) querydef object and then run the OpenQuery method.

Ken S.

 

[AlexCuse]

Good call Ken! I mostly use access for action queries and reporting, so I always forget about that option

AT76 - Does that make sense? I think it will make your life much easier

Ignorance of certain subjects is a great part of wisdom

 

[AT76]

Thanks AlexCuse! I got it to work. Yes it makes sense now. I got it to work by selecting the 1 choice parameter. Can I use this same method for multiple drop down selections?

Thanks!

 

[AT76]

The help provided was awesome. Thank you.

One more question, what if I want to query on more than one field?

Thanks!

 

[AlexCuse]

You just will need to add them to your SQL string. Using my first example, here is how I would do it (you will need to add conditional logic to handle for anything like an unselected drop down box)

Dim strSQL as string

strSQL = "SELECT "
strSQL = strSQL & DropDown1.Value
strSQL = strSQL & ", " & DropDown2.Value
strSQL = strSQL & ", " & DropDown3.Value 
strSQL = strSQL & " FROM TABLE WHERE SOMETHING = "
strSQL = strSQL & DropDown4.Value

Notice I changed it to place the comma before each column name to be selected. This (IMHO) makes it easier to build queries where you will have a variable number of columns selected. You still will need to add some if/else logic to handle for unselected boxes, to make sure you will still build a valid query if that happens.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom