generate a SELECT query from VBA code.

[scroce]

I have a form that has 3 combo boxes and two checkmarks on it for the user to limit criteria searched. Specifically:

cboSalesRep
cboAgentName
cboCategory
chkLineOfBiz1
chkLineOfBiz2

After the user selects their criteria in these boxes, I want to have them press cmdFindRecords and have these records show up.

I've tried using the standard access queries, but to no avail, due to the number of paramters.

I've notices you can build an action query using "DoCmd.RunSQL" - but it doesn't seem to let you just run a plain Select query.

I just want to generate an SQL statement, substitute the selctions the user made with variables into the SQL statement, and then print the recordset to the screen.

Can I/How do I do this? - Also I'd eventually like to format these records into a report rather than in just a plain recordset - but maybe that's a separate issue.

Seek not outside yourself; heaven is within.

 

[SeeThru]

To create a report.....

create the report. Base it on a query. In the query select the data table (obviously!) and then in the criterie, use the build to link them to the form with the comboboxes ie.

forms!myselectorsform!mycombovalue

(if the value from the combo box is not the only column, you may need to use the .columns(0) to select the right value)

set all the criteria you want....one thing you can do is add a selection to you combo box that allows you to effectively ignore that selection - gets harder, though)

and then open the report...it should work.

Couple of points:
The form must be open before you try to open the report. Put a bit of code in the report onopen that checkes if the form is open and opens it if it isnt'

make sure you are actually selecting the right criteria in the right columns...if you are using combo-boxes from a linked table, consider making the report source query a multiple table query including all the primary keys, and setting the relationships correctly.

Hope this helps

 

[SeeThru]

To create a report.....

create the report. Base it on a query. In the query select the data table (obviously!) and then in the criterie, use the build to link them to the form with the comboboxes ie.

forms!myselectorsform!mycombovalue

(if the value from the combo box is not the only column, you may need to use the .columns(0) to select the right value)

set all the criteria you want....one thing you can do is add a selection to you combo box that allows you to effectively ignore that selection - gets harder, though)

and then open the report...it should work.

Couple of points:
The form must be open before you try to open the report. Put a bit of code in the report onopen that checkes if the form is open and opens it if it isnt'

make sure you are actually selecting the right criteria in the right columns...if you are using combo-boxes from a linked table, consider making the report source query a multiple table query including all the primary keys, and setting the relationships correctly.

If you have problems, replace the references to the form in the report criteria one at a time with known good values (basically, just type in something you know works) until you find the one thats causing the problem. The most common cause will be the combo box returning something unexpected due to its having multiple columns, possibly hidden.

Hope this helps

 

[scroce]

Thanks for your input - but the problem with that idea is that the user may want to leave one of the criteria blank - for example, cboCategory. In other words, I want to give the user a choice to choose "All" in any particular category.

Access parameter queries referencing an object in the way you have outlined, such as forms!frmMyform!cboCategory don't seem to be able to handle a * entry very well. I can't seem to figure out how to get the inherent query object to do what I want - which is why I want to be able to generate something like the following pseudocode:

Dim Combo1
Dim Combo2
Dim Combo3
Dim strSQL

combo1 = me!.frmMyFormcboCategory
combo2 = etc. etc.
combo3= etc.......

strSQL="SELECT myField1, myField2, myField3, FROM myTable WHERE myField1=Combo1, myField2=Combo2, myField3=Combo3;"

DoCmd.RunSQL strSQL

I know you can do this with an action query - which leads me to believe it can also be done with a simple select query. RunSQL does not work with a select query.

does this make sense? Seek not outside yourself; heaven is within.

 

[FancyPrairie]

Here's a shot:

Suppose your select query looks like this "Select * from yourTable;". What you're trying to do is build the Where clause. So you need to loop through each list box and determine which items (1 or more items per list box) have been selected. If the first item is selected (i.e. (All)), then ignore building the Where clause for that list box, because by default, all of those items will be shown anyway.
Here's a start:

   dim strSQLSelect as string
   dim strSQLWhere as string
   dim varItem as variant

   strSQLSelect = "Select * from YourTable "
   strSQLWhere = vbNullString

'Optional code ... Begin Loop thru all list boxes

   if (YourListBox.enabled) then
       if (not YourListBox.Selected(0)) then   'IF True, then (All) not selected
           for each varItem in YourListBox.ItemsSelected
               strSQLWhere = strSQLWhere & YourListBox.column(1,varItem) & " = " & YourListBox.column(0,varItem) & " OR "
           next

           if (strSQLWhere <> vbNullString) then
               strSQLSelect = strSQLSelect & &quot; WHERE &quot; & mid(strSQLWhere,1,len(strSQLWhere)-4)
           end if
        End If
    End if

'End Loop thru all list boxes

Note I subtracted 4 from the length to get rid of the last " OR ".

1. I checked to see if the 1st item is selected. If it is, then I assume that the 1st item is "(All)". Therefore, no need to build where clause.

2. I also check to see if the list box is enabled, if it is not enabled, then I assume that that list box does not apply to the report the user selected.

3. The above code assumes that column(0) of the list box contains the value of the table field (ID), column(1) of the list box contains the name of the field, and column(2) is the text the user sees. For example, "2;lngStaffID;Smith, John"

Therefore, your SQL statment will look like this:
Select * from YourTable Where lngStaffID = 2;

Finally, you could put an outer loop that loops through all of the list boxes on the form, then loops thru each ItemsSelected.

 

[gol4]

If you dont want to view the data in a report then another
way is to create a query based on your sql statement

Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef("test1",strsql)
DoCmd.OpenQuery "test1"

you will need to delete the query the next time you run the code

DoCmd.DeleteObject acQuery, "test1"

 

[scroce]

ok - but once you get your sql statement to look the way you want, how do you execute it so it actually shows up on the screen as a recordset?

Let's make it even simpler - say my sql statment is
SELECT * FROM tblMyTable

Can I write code so I can click a button on a form that would show me the result set of this query "on the fly" without having a query previously defined under "Queries" Seek not outside yourself; heaven is within.

 

[FancyPrairie]

You would have to have a form, subform, or report to diplay the results of the recordset. Or, do as the previous post suggested and write the SQL string to a query (via CreateQueryDef). Then open the query via the QBE.

 

[scroce]

OK - I've managed to get the Query Def to function - thank you all for your posts on that

Now the issue is this:

If I've got 3 limiting criteria on my form (using combo boxes) - what if the user only wants to limit by one or two of my choices?

my sql is this:

strSQL =

"SELECT tblMembers.*, tblMembers.AgencyID, tblMembers.RepID, tblMembers.CateogryID

FROM tblMembers

WHERE tblMembers.AgencyID=" & varAgent & " tblMembers.RepID=" & varRep & "tblMembers.CategoryID=" & varCategory

how do I pass a * to a particular field to show that it should not be limited? I have a choice for "all" on my combo box but can't figure out how to use it. Seek not outside yourself; heaven is within.

 

[scroce]

sorry to be post-happy, but I just found another post by someone else that essentially simply describes the problem I'm having - only difference is that I have more than one combo box, and I would like to solve the problem with VBA if possible

thread705-155413 Seek not outside yourself; heaven is within.

 

[FancyPrairie]

You don't have to pass a *. In other words, the fields you define in the Where clause do not have to be part of the Select clause. In my example, I had a list box with staff names and I chose "Smith, John" so my SQL statement limited the results to just the records where lngStaffID = 2. However, if the user selected (All), then my Where clause does not exist for this listbox. And, so, I get all staff names shown.

For example, the code in my previous post might look like (assuming 2 list boxes one for staff names and one for dept names):
Select * from YourTable Where lngStaffID = 2 and lngDeptID = 3;
In this case only records where lngStaffID = 2 and lngDeptID = 3 would be shown.
However, if the user selected (All) for staff, then the select statement would look like this:
Select * from YourTable where lngDeptID = 3
In this case, all staff would be shown whose Dept ID = 3

 

[scroce]

yes, ok you are making good sense - however my query isn't working as you mention. If my user chooses (All), it passes a zero into the query. Then the query tries to limit by a zero, and therefore pulls no records every time the user chooses (All) My combo box pulls from a field in a table with all the names in it.

Needless to say, it did not inherently have an "all" option, so I added this little piece of SQL to the syntax which populates the combo box to give that choice:

UNION SELECT 0 '(All)' FROM tblMyTable

Why did I do that? b/c I found an example in my access book.

Although I think I'm on the right track I still can't seem to get my query to release criteria if the user chooses (All) - it just keeps coming up with an empty recordset.

Seek not outside yourself; heaven is within.

 

[gol4]

don't worry about the all or the wild card it will only limit the field to criteria if you specify in the statement

this code will work if you are not returning text or date fields. Replace the field names and combo names with your names
strsql = "select * from tblmembers where " "return all the records
if combo1 <> &quot;&quot; then strsql = strsql & &quot; tblmembers.field1 = &quot; & combo1 & &quot; and&quot;
if combo2 <> &quot;&quot; then strsql = strsql & &quot; tblmembers.field1 = &quot; & combo2 & &quot; and&quot;
if combo3 <> &quot;&quot; then strsql = strsql & &quot; tblmembers.field1 = &quot; & combo3 & &quot; and&quot;
strsql = left(strsql,len(strsql)-4)'drop last and

Dim qdf As QueryDef
Set qdf = CurrentDb.CreateQueryDef(&quot;test1&quot;,strsql)
DoCmd.OpenQuery &quot;test1&quot;

 

[FancyPrairie]

scroce, you're missing the point. If the user selects (All), you don't do anything (you don't add the statement &quot;Where ...=0&quot;.) There is no need to build the Where clause. For example, suppose the user selects a staff and a dept. Your SQL statement would look like this: &quot;Select * from YourTable Where lngStaffID = 2 and lngDeptID = 3;&quot;. However, if the user selects (All Staff) and (All Departments) then your SQL statement would look like this: &quot;Select * from YourTable;&quot;

Go back and look at my first post. I'm not building the Where clause for the listbox if (All) is selected. In my example (first post), I'm assuming that the first item in the listbox is &quot;(All)&quot;. And the following code checks to see if the user selected &quot;(All)&quot;:
If (not YourListBox.Selected(0)) Then
Therefore, it says, if (All) is NOT selected, then loop thru all items selected and build where clause. If (All) is selected, then no need to build where clause.

 

[FancyPrairie]

Update to previous posts.

1. In my first post I suggested that the columns of your listbox should look something like this:
Column(0) = lookup field (i.e. ID)
Column(1) = fieldname (i.e. lngStaffID)
Column(2) = text displayed to user
I would change the properties of the listbox(es) to look like this:
Column(0) = lookup field (i.e. ID)
Column(1) = text displayed to user (i.e. Smith, John)
Tag property = TableName.FieldName
Consequently, the strSQLWhere statement in my code (see first post) would look like this:
strSQLWhere = strSQLWhere & YourListBox.Tag & &quot; = &quot; & YourListBox.column(0,varItem) & &quot; OR &quot;

2. You want the OR clause not the AND clause within a listbox. Use the AND clause between listboxes. For example, assume 2 listboxes lstStaff and lstDept, and assume user selects 2 items from each listbox; your SQL code would look like:
Select * from YourTable Where (lngStaffID=2 OR lngStaffID=3) AND (lngDeptID=3 OR lngDeptID=4)

 

[scroce]

Fancy,

The user is only using combo boxes for each choice, i.e. they can't choose more than one item at one time.

If I understand everyone correctly, there is no need to pass a wildcard, and that in order to do this properly you need to use the VBA to essentially manipulate the WHERE clause of the SQL statement.

What i'm still having trouble is the logic used to limit the SQL using my three combo boxes. Trying to capture every possible combination the user may choose is presenting a problem for me. You could probably write an if..then statement for each, and have it generate its own SQL statement and then execute it, but that can't be the best way to do it.

If you look at Gol4's code example - this I think is something close to what I'm looking to do, however again - I don't think that his code will allow for every permuation, does it? Maybe I'm missing the point again. If I am, I'm sorry. Seek not outside yourself; heaven is within.

 

[scroce]

Looks like I was able to use gol4's code example successfully. Luckily I am using the ID fields for each of my limiting criteria, so I am in fact using numbers.

I think this was a very tricky problem - not very intuitive.

I'm going to have to study the code a little more.

I'm very grateful for all of your posts.

SC Seek not outside yourself; heaven is within.

 

[FancyPrairie]

Sorry I missed the part about using combo boxes. However, the concept is the same. Just forget the OR clause. However, I have found that the user has more options if they can select the items they want from a multi select listbox. Of course using listboxes may not be applicable to your application. But, consider the following 2 scenerios.

Using a combobox:
Suppose you have 200 agents and the user wants to print the information for Agent1 and Agent2...Agent10. To do this, the user would have to select Agent1 from the combo box, then print the report. Then he/she would have to select Agent2 from the combobox and print the report. And so on, until the reports for the 10 agents were printed.

Using a listbox:
Again, suppose you have 200 agents and the user wants to print the information for Agent1 and Agent2...Agent10. To do this, the user would select, via the listbox, Agent1, Agent2,...Agent10. Then select the report. The report would then print the information for all 10 agents with, optionally, a page flip between agent's information.

 

[scroce]

fancy,

you are certainly right. I will most likely attempt to do something like that in my DB very soon, and when I do this thread will be waiting for me! I probably should have thought of doing it that way beforehand, but ah c'est la vie.

Seek not outside yourself; heaven is within.

 

[Guest_imported]

Select M_Employees.Name, M_Employees.Emp_Number, M_Employees.Address
From M_Employees
Order by
Choose(Val(Forms!F_Emp!Sort_Option), M_Employees.Emp_Name, M_Employees.Emp_Number, M_Employees.Address);

Note: Entry in Forms!F_Emp!Sort_Option must be an integer and it is best to force it with the Val() function. You can have up to 29 options and you can use the Choose Function in each part of the query except the From clause.