Query criteria in comboboxes on a form

[hockeyman9474]

I have 3 comboboxes on a form. Year, Month, Carline. I wrote a query that queries those three comboboxes using criteria [Forms]![PartsOrderEnter]![Year]. I did this for all three . I cna get the query to change based on the comboboxes changing and how do I get it to query all 3 instead of just one?

[Dal Jeanis]

Generally, when I'm doing something like that, I use the AfterUpdate event of each of the three comboboxes to call the same BuildSQL function. That way, in a single place, you can test each of the boxes and string together appropriate WHERE clauses.

[hockeyman9474]

Generally, when I'm doing something like that, I use the AfterUpdate event of each of the three comboboxes to call the same BuildSQL function. That way, in a single place, you can test each of the boxes and string together appropriate WHERE clauses.

OK so let me ask you this...if I use the after update function, is there a way to view my filtered results by staying on the same form?

[Dal Jeanis]

Easy. Assuming you're building the SQL for the form, then at the end of the procedure that builds the SQL, use

Me.Requery

[hockeyman9474]

OK I kinda follow but I am still a little lost. I have 3 combo boxes on my form. Each Combobox gets its selections from a query. I would like to view on the same form as the controls the filtered data. So I add a subform who's data source is a query. I would like the data to change as the controls change. I understand how the use the afterupdate function. Where does the me.query code come in? I assume also I substitute the actual query name for the word query?

[Dal Jeanis]

No, Requery is a method name. http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

The exact syntax for invoking a requery can vary based upon what form you are on, and what form you are trying to requery. http://access.mvps.org/access/forms/frm0031.htm

Now, remember that there is a control on your main form that links the main form to the subform. That control may -- should, really -- have a different name than your subform itself. I usually name it lnk_SubFormName so I won't forget the difference.

Assuming your controls are on the main form, then after you calculate the SQL and assign it to the subform's record source, the next line in the Control's AfterUpdate event would be

Me![lnk_SubFormName].Requery

[hockeyman9474]

No, Requery is a method name. http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

The exact syntax for invoking a requery can vary based upon what form you are on, and what form you are trying to requery. http://access.mvps.org/access/forms/frm0031.htm

Now, remember that there is a control on your main form that links the main form to the subform. That control may -- should, really -- have a different name than your subform itself. I usually name it lnk_SubFormName so I won't forget the difference.

Assuming your controls are on the main form, then after you calculate the SQL and assign it to the subform's record source, the next line in the Control's AfterUpdate event would be

Me![lnk_SubFormName].Requery

That code almost worked! It actually did work, but here's my dilemma:
I have 3 controls YEAR, MONTH, CARLINE. The table source is the same query for each. That is also the query the subform is datasourced to. When I make the criteria on the YEAR column in the query

[Forms]![OrderEntry]![YEAR]

The subform requeries perfectly. The other controld do not work. When I add the same line of code to MONTH and CARLINE the subform does not work correctly. Basically I have 3 controls that should all filter what I see in the subform similar to how filter works in excel.


What am I missing?

[Dal Jeanis]

You can't think about it like excel. In essence, data filters in excel are "AND"ed together. In Access, you get one SQL source query and one filter. You have to build the SQL for one or the other. That's why, when I have multiple controls that all will affect the query, I put the VBA for the "SQL build" into a common routine, and have each of the three (for example) call the same module to build the SQL based upon the states of all three controls.

Here's some "air code" on how to do it. You'll have to fix all the names, and make sure that the resulting SQL looks right. I assumed that carline was a text field, and that you were looking for an exact match, os I put single quotes around it. Your mileage may vary.

Private Sub BuildQuery()
Dim WhereDone As Boolean
Dim strListSQL As String
  ' the select fields are static
  WhereDone = False
  strListSQL = "SELECT "  & _
                "[MyTable].[Field1], " & _
                "[MyTable].[Field2], " & _ 
                "[MyTable].[Field3],  " & _ 
                "FROM [MyTable] "
  
  If [Forms]![OrderEntry]![YEAR] <> "" THEN 
       strListSQL = strListSQL & _
       "WHERE ( [MyTable].[Year] = " & _
       [Forms]![OrderEntry]![YEAR] & " "
       
        WhereDone = True
  End If

  If [Forms]![OrderEntry]![MONTH] <> "" THEN 
       If WhereDone Then
          strListSQL = strListSQL & " AND "
       Else
          strListSQL = strListSQL & " WHERE ( "
          WhereDone = True
       End If
       strListSQL = strListSQL & _
       "[MyTable].[Month] = " & [Forms]![OrderEntry]![Month] & " "
  End If

  If [Forms]![OrderEntry]![Carline] <> "" THEN 
       If WhereDone Then
          strListSQL = strListSQL & " AND "
       Else
          strListSQL = strListSQL & " WHERE ( "
          WhereDone = True
       End If
       strListSQL = strListSQL & _
       "[MyTable].[Carline] = '" & [Forms]![OrderEntry]![Carline] & "' "
  End If
      
  ' close the where if there is one
  If WhereDone Then
     strListSQL = strListSQL & ") "
  End If
            
  ' Add the order by clause if desired
  ' you could also put it in the order by of the form
  ' strListSQL = strListSQL & _
  '  "ORDER BY [MyTable].[Field1] "
  '
     
 ' for testing, show sql
 ' MsgBox strListSQL
 ' assign the SQL to the required place
   Me.RecordSource = strlistSQL 

 ' run sql
   Me.Requery
 
End Sub

[hockeyman9474]

i'll give it a shot thanks

[hockeyman9474]

ok let me see if i get what's going on here:

I am trying to interpret your code, and because of my novice status, I am having trouble. I am currently researching in my sql books your use of & and the quotes. Also, in several instances you have Wheredone but not =to true or false. Did you mean to put true or false in that situation?

I hope you don't mind the questions but this is a true learning experience for me.

[Dal Jeanis]

1) Purpose of this procedure: to build an SQL string. All the code there is building a string, not "doing" SQL, so you should be researching string functions and operators, not SQL. I'll explain your specific answers below, but here's a link to ALL the functions. -> http://www.techonthenet.com/access/functions/index.php

When you finish building the SQL, you'll have to make sure that it's good SQL. THEN, you'll need your SQL books.


2) & is a string concatenation operator. http://www.techonthenet.com/access/f...ing/concat.php.

c = a & b means that string C will become a string that combines the a string and the b string.

c = "Hello" & "world" means that c becomes "HelloWorld"


3) _ at the end of a line is a continuation operator, so the following code means the same as "c = a & b".

c = a & _
   b

Here's a whole page full of operators, including continuation. Most of them you already know. http://www.functionx.com/vbaccess/Lesson04.htm


4) A Boolean variable's value is either True or False (or Null).

"If Wheredone Then" has the same effect as saying "If (Wheredone = True) Then", but more concise.