Dynamic Fliter in Query or Form

[elfranzen]

OK I have a switch board that lets the user pick from a group of manufactures. Now they might want to pick form more then one. How would I set up the code to filter out the other manufactures. I am using radio buttons as the item they would check. I was thinking about a public string that I could (at the time the button is pressed to open the form) add all the radio buttons that are checked into a long string to go into the query or form filter is this possible or am I way off?

 

[ajetrumpet]

How would I set up the code to filter out the other manufactures. I am using radio buttons as the item they would check. I was thinking about a public string that I could (at the time the button is pressed to open the form) add all the radio buttons that are checked into a long string to go into the query or form filter is this possible or am I way off?

No, you're not way off. You are quite on the right track. I have done this with a couple of samples I made. I think it's best to give you an example of something like this. Consider the following:

**3 manufacturers: the names of them are man1, man2, man3
**1 field holding the manufacturers. The name of the field is fldMAN
**3 radio buttons, each named the same as the manufacturers man1, man2, man3

With the above information, I would write the following code with the click event of the button that opens the filtered form (I am assuming that your form is already based on a recordset of somekind, either table or query):

ON click

dim c as control, SQL as string

SQL = ""

for each c in me.controls
  if type of c is optionbutton then
    if c = -1 then
      SQL = SQL & "fldMAN = c.name OR "
    end if
  end if
next

SQL = Left(SQL, Len(SQL) - 4)

DoCmd.OpenForm "FormNameHere", acviewnormal, , SQL

So, this puts all of the fields that have been specified by the radio buttons into the Form's criteria (WHERE clause) before opening.

 

[elfranzen]

No, you're not way off. You are quite on the right track. I have done this with a couple of samples I made. I think it's best to give you an example of something like this. Consider the following:

**3 manufacturers: the names of them are man1, man2, man3
**1 field holding the manufacturers. The name of the field is fldMAN
**3 radio buttons, each named the same as the manufacturers man1, man2, man3

With the above information, I would write the following code with the click event of the button that opens the filtered form (I am assuming that your form is already based on a recordset of somekind, either table or query):

ON click

dim c as control, SQL as string

SQL = ""

for each c in me.controls
  if type of c is optionbutton then
    if c = -1 then
      SQL = SQL & "fldMAN = c.name OR "
    end if
  end if
next

SQL = Left(SQL, Len(SQL) - 4)

DoCmd.OpenForm "FormNameHere", acviewnormal, , SQL

So, this puts all of the fields that have been specified by the radio buttons into the Form's criteria (WHERE clause) before opening.

I don't have much SQL knowledge so can someone explain the code a little for me. I am trying to learn as much as possible so I don't want to just copy the code and get it to work I want to know who it works also. So if someone has the time just let me know what each line of code is doing and why? And what is the SQL[/code]So in the DoCmd.OpenForm Mean also?

 

[ajetrumpet]

So if someone has the time just let me know what each line of code is doing and why? And what is the SQL[/code]So in the DoCmd.OpenForm Mean also?

Here is the code once more Elf, with comments above each line:

[COLOR="SeaGreen"]'declare 'c' as a control object and 'SQL' as a string variable[/COLOR]
dim c as control, SQL as string

[COLOR="SeaGreen"]'set SQL equal to a zero-length string (Do this to simply give 
the variable a [B][U]value[/U][/B] (you don't want it having no value at all)[/COLOR]
SQL = ""


for each c in me.controls [COLOR="SeaGreen"]'loop through all of the controls on the current form[/COLOR]
  if type of c is optionbutton then [COLOR="SeaGreen"]'check the control to see if it is a radio button[/COLOR]
    if c = -1 then [COLOR="SeaGreen"]'check to see if it has been selected[/COLOR]
      SQL = [COLOR="Red"]SQL & "fldMAN = " & c.name & " OR "[/COLOR] [COLOR="SeaGreen"]'add the necessary portion of the WHERE clause to the criteria string[/COLOR]
    end if
  end if
next [COLOR="SeaGreen"]'go to the next control on the form[/COLOR]

[COLOR="SeaGreen"]'trim the right side of the SQL string that was created 
with all of the "true" conditions for the radio butttons in the above 
control loop (You're taking out the "OR" and the two spaces 
that make up the last 4 placeholders (as of the last run through the loop)[/COLOR]
SQL = Left(SQL, Len(SQL) - 4)

[COLOR="SeaGreen"]'Open the Filtered form, with the SQL string serving 
as the WHERE clause criteria specification[/COLOR]
DoCmd.OpenForm "FormNameHere", acviewnormal, , SQL

If you simply want to see what's happening to the SQL string as the code is running, type debug.print SQL on a new line right after the SQL line in the loop, and then type it on another line after the "Left" function (if you want to see the end product too. Read the results in the immediate window (Ctrl + G). If you want a visual plus an explanation of what the control loop does to the SQL string, consider this:

All 3 manufacturer buttons have been selected. The loop encounters the buttons in the following order: man1, man2, man3

After the loop cycles through the first button, the SQL string will read: "fldMAN = 'man1' OR " Now, after the second button has been evaluated, it will read "fldMAN = 'man1' OR fldMAN = 'man2' OR " And finally, after the third button, it will say: "fldMAN = 'man1' OR fldMAN = 'man2' OR fldMAN = 'man3' OR "

Now you can't have an SQL line with that " OR " section hanging out there, so the next line trims if off of the entire string:

[U]SQL = Left(SQL, Len(SQL) - 4)[/U] will result in the following:

"fldMAN = 'man1' OR fldMAN = 'man2' OR fldMAN = 'man3'"

Thus, in this example, the last line that will execute what you want, will be evaluated like this:

DoCmd.OpenForm "FormNameHere", acviewnormal, , _
   "fldMAN = 'man1' OR fldMAN = 'man2' OR fldMAN = 'man3'"

Also, Please note the code in red. That line has been changed from the original line that I posted. The original is incorrect, and it will probably bring up an error. Sorry!

 

[elfranzen]

Here is the code once more Elf, with comments above each line:

[COLOR="SeaGreen"]'declare 'c' as a control object and 'SQL' as a string variable[/COLOR]
dim c as control, SQL as string

[COLOR="SeaGreen"]'set SQL equal to a zero-length string (Do this to simply give 
the variable a [B][U]value[/U][/B] (you don't want it having no value at all)[/COLOR]
SQL = ""


for each c in me.controls [COLOR="SeaGreen"]'loop through all of the controls on the current form[/COLOR]
  if type of c is optionbutton then [COLOR="SeaGreen"]'check the control to see if it is a radio button[/COLOR]
    if c = -1 then [COLOR="SeaGreen"]'check to see if it has been selected[/COLOR]
      SQL = [COLOR="Red"]SQL & "fldMAN = " & c.name & " OR "[/COLOR] [COLOR="SeaGreen"]'add the necessary portion of the WHERE clause to the criteria string[/COLOR]
    end if
  end if
next [COLOR="SeaGreen"]'go to the next control on the form[/COLOR]

[COLOR="SeaGreen"]'trim the right side of the SQL string that was created 
with all of the "true" conditions for the radio butttons in the above 
control loop (You're taking out the "OR" and the two spaces 
that make up the last 4 placeholders (as of the last run through the loop)[/COLOR]
SQL = Left(SQL, Len(SQL) - 4)

[COLOR="SeaGreen"]'Open the Filtered form, with the SQL string serving 
as the WHERE clause criteria specification[/COLOR]
DoCmd.OpenForm "FormNameHere", acviewnormal, , SQL

If you simply want to see what's happening to the SQL string as the code is running, type debug.print SQL on a new line right after the SQL line in the loop, and then type it on another line after the "Left" function (if you want to see the end product too. Read the results in the immediate window (Ctrl + G). If you want a visual plus an explanation of what the control loop does to the SQL string, consider this:

All 3 manufacturer buttons have been selected. The loop encounters the buttons in the following order: man1, man2, man3

After the loop cycles through the first button, the SQL string will read: "fldMAN = 'man1' OR " Now, after the second button has been evaluated, it will read "fldMAN = 'man1' OR fldMAN = 'man2' OR " And finally, after the third button, it will say: "fldMAN = 'man1' OR fldMAN = 'man2' OR fldMAN = 'man3' OR "

Now you can't have an SQL line with that " OR " section hanging out there, so the next line trims if off of the entire string:

[U]SQL = Left(SQL, Len(SQL) - 4)[/U] will result in the following:

"fldMAN = 'man1' OR fldMAN = 'man2' OR fldMAN = 'man3'"

Thus, in this example, the last line that will execute what you want, will be evaluated like this:

DoCmd.OpenForm "FormNameHere", acviewnormal, , _
   "fldMAN = 'man1' OR fldMAN = 'man2' OR fldMAN = 'man3'"

Also, Please note the code in red. That line has been changed from the original line that I posted. The original is incorrect, and it will probably bring up an error. Sorry!

Thanks this helps alot to be able to see what the code is doing and why it's doing it.

 

[elfranzen]

Here is the code once more Elf, with comments above each line:

[COLOR="SeaGreen"]'declare 'c' as a control object and 'SQL' as a string variable[/COLOR]
dim c as control, SQL as string

[COLOR="SeaGreen"]'set SQL equal to a zero-length string (Do this to simply give 
the variable a [B][U]value[/U][/B] (you don't want it having no value at all)[/COLOR]
SQL = ""


for each c in me.controls [COLOR="SeaGreen"]'loop through all of the controls on the current form[/COLOR]
  if type of c is optionbutton then [COLOR="SeaGreen"]'check the control to see if it is a radio button[/COLOR]
    if c = -1 then [COLOR="SeaGreen"]'check to see if it has been selected[/COLOR]
      SQL = [COLOR="Red"]SQL & "fldMAN = " & c.name & " OR "[/COLOR] [COLOR="SeaGreen"]'add the necessary portion of the WHERE clause to the criteria string[/COLOR]
    end if
  end if
next [COLOR="SeaGreen"]'go to the next control on the form[/COLOR]

[COLOR="SeaGreen"]'trim the right side of the SQL string that was created 
with all of the "true" conditions for the radio butttons in the above 
control loop (You're taking out the "OR" and the two spaces 
that make up the last 4 placeholders (as of the last run through the loop)[/COLOR]
SQL = Left(SQL, Len(SQL) - 4)

[COLOR="SeaGreen"]'Open the Filtered form, with the SQL string serving 
as the WHERE clause criteria specification[/COLOR]
DoCmd.OpenForm "FormNameHere", acviewnormal, , SQL

If you simply want to see what's happening to the SQL string as the code is running, type debug.print SQL on a new line right after the SQL line in the loop, and then type it on another line after the "Left" function (if you want to see the end product too. Read the results in the immediate window (Ctrl + G). If you want a visual plus an explanation of what the control loop does to the SQL string, consider this:

All 3 manufacturer buttons have been selected. The loop encounters the buttons in the following order: man1, man2, man3

After the loop cycles through the first button, the SQL string will read: "fldMAN = 'man1' OR " Now, after the second button has been evaluated, it will read "fldMAN = 'man1' OR fldMAN = 'man2' OR " And finally, after the third button, it will say: "fldMAN = 'man1' OR fldMAN = 'man2' OR fldMAN = 'man3' OR "

Now you can't have an SQL line with that " OR " section hanging out there, so the next line trims if off of the entire string:

[U]SQL = Left(SQL, Len(SQL) - 4)[/U] will result in the following:

"fldMAN = 'man1' OR fldMAN = 'man2' OR fldMAN = 'man3'"

Thus, in this example, the last line that will execute what you want, will be evaluated like this:

DoCmd.OpenForm "FormNameHere", acviewnormal, , _
   "fldMAN = 'man1' OR fldMAN = 'man2' OR fldMAN = 'man3'"

Also, Please note the code in red. That line has been changed from the original line that I posted. The original is incorrect, and it will probably bring up an error. Sorry!

what if you wanted to sort by two different fields

set of radio buttons for one field and another set of radio buttons for another field would there be any way to split this loop for the two controls.

 

[ajetrumpet]

what if you wanted to sort by two different fields

Elf,

With the form that opens, use the OrderBy property on the Load event to order it however you want it:

MyForm On Load

me.orderby = "FirstFieldToOrderBy", "Second", "etc..."
Me.orderbyOn = true

 

[elfranzen]

Elf,

With the form that opens, use the OrderBy property on the Load event to order it however you want it:

MyForm On Load

me.orderby = "FirstFieldToOrderBy", "Second", "etc..."
Me.orderbyOn = true

Not Order but two filters in two different fields

 

[ajetrumpet]

What do you mean by "filters"? How can you have a filter located on two different fields? The form is one entity, and you can't treat it like it's more than one.

I'll need a bit more explanation to answer this Elf. Either some clarification, or a file to look at, so I can get a visual combined with your explanations thus far.

 

[elfranzen]

What do you mean by "filters"? How can you have a filter located on two different fields? The form is one entity, and you can't treat it like it's more than one.

I'll need a bit more explanation to answer this Elf. Either some clarification, or a file to look at, so I can get a visual combined with your explanations thus far.

OK I have one field that has manufactures and one that is location.

Lets say they want one or two manufactures and they want only those manufactures in the Chicago area. So I would have one field that would list manufactures and another field for what city the manufacture is in. Hopefully this help.

 

[ajetrumpet]

OK I have one field that has manufactures and one that is location.

OK, got it.

Lets say they want one or two manufactures and they want only those manufactures in the Chicago area.

You will have to set up a filtering mechanism for the city. You can do this anywhere, and you have plenty of options here too! If you want the form filtered in this way before it opens, then you will have to do it with an input object on the same form where the manufacturers are chosen from the buttons. Maybe you can do it with a combo box? List box? Input box? Any of these will work just fine.

All you're doing here is adding more criteria to the form's WHERE clause. Whatever you choose to use, just make sure you add the relevant code to the SQL string and looping process that I already showed you. The portion that you add (for the "location" choice) to the SQL string will have to be connected with the "AND" operator instead of the "OR". Do you need help writing this?

Another note too, just in case there is a mix up here:
**A field is not the same as a control. A form control just displays field data, it's not a source for the data.

 

[elfranzen]

I think I am going to use a combo box for the location because they shouldn't need to pick more then one city at a time. I think I could get this code to work just fine. Thanks for all the help.