Re: Tying check boxes to data

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Specifically I want to exclude the column of data that the check box
represents if the box is not checked. I tried your code and played with it a
fair bit but for unknown reasons it cause a compile error in one of my early
on query expressions and wont let the data go through blanking out my chart
completely and no matter how I try to rewrite the equations they still wont
compile although as soon as I remove the module and code it works like a
charm again. I'm gonna keep playing with the code and trying to write
something into the criteria, but if you have any ideas on this or other code
I'd gladly take them.

"Dale_Fye via AccessMonster.com" wrote:

You indicate that each checkbox represents an entire column of data. Do I
understand that you want to exclude entire columns from the query results if
the checkbox is not checked? If so, you are going to need to build the SQL
string dynamically. Start out by writing a function that builds the string
dynamically, something like:

Public fnBuildSQL() as String

dim frm as Form
Dim strSQL as string

set frm = Forms("yourFormName")

strSQL = "SELECT [Field1], [Field2], " _
& (iif(frm.check1, [Field3], NULL) + ", ") _
& (iif(frm.check2, [Field4], NULL) + ", ") _
& (iif(frm.check3, [Field5], NULL) + ", ") _
& (iif(frm.check4, [Field6], NULL) + ", ") _
& (iif(frm.check5, [Field7], NULL) + " ")

if Right(strSQL, 2) = ", " Then
strSQL = left(strSQL, len(strSQL) - 2)
end if

strSQL = strSQL & "FROM yourTable " _
& "WHERE [SomeField] = " & frm.Combo2

fnBuildSQL = strSQL

End Function

Then, in the AfterUpdate event of the checkboxes (or in a command button -
cmdRequery) you can call this function and set the Record Source of the form,
subform, or report that contains the graph to this value. Something like:

Private Sub Check1_AfterUpdate

me.subGraph.form.RecordSource = fnBuildSQL

End Sub

BTU_needs_assistance_43 wrote:
Your on the right track but I dont think this is simple enough to write into
that line. Each check box of mine represents an entire column of data with 5
various values within each set of data. Also what combo box 2 displays is
completely dependent on the selection made it combo box 1, where combo box 1
displays a constant list of choices. When I check one of the check boxes I
want it to display all the data associated with it, and if unchecked to hide
all that data. I've tried things like writing into the criteria section

[myForm]![Check1] = True

or IIf([Check1] = True, [myTable]![Section], *)

For the first one it will either blank out the entire graph or allow the
entire graph if i check any of the check boxes. Its just frustrating!

One way would be something like:

[quoted text clipped - 36 lines]
the data shown to where it matched the combo boxes. How do I make the query
only show the data from the boxes which I have checked?

--
HTH

Dale Fye

Message posted via http://www.accessmonster.com


.

Relevant Pages

  • Re: Tying check boxes to data
    ... I figured out how to do it within the query with an expression as opposed to ... Start out by writing a function that builds the string ... When I check one of the check boxes I ... For the first one it will either blank out the entire graph or allow the ...
    (microsoft.public.access.modulesdaovba)
  • Re: Tying check boxes to data
    ... You indicate that each checkbox represents an entire column of data. ... Start out by writing a function that builds the string ... When I check one of the check boxes I ... For the first one it will either blank out the entire graph or allow the ...
    (microsoft.public.access.modulesdaovba)
  • Re: values in graph
    ... then count medians, change them to string and assign these strings to ... boxes in the graph right? ... I did not suggest it because of your mention of "object inspector", which suggests that you do not want to create permanent information but instead wish to be able to point to an arbitrary position in the graph and have information relevant to that point come up. ...
    (comp.soft-sys.matlab)
  • Re: Search Form Results Error
    ... Are the control sources for the text boxes on the form valid? ... listedn in the Field List match the names in the text boxes on the form? ... Build up the criteria string form the non-blank search ... and apply to the form's Filter. ...
    (microsoft.public.access.formscoding)
  • Re: Search Form Results Error
    ... both the text boxes and refrences clear. ... Private Sub cmdFilter_Click ... Build up the criteria string form the non-blank search ... and apply to the form's Filter. ...
    (microsoft.public.access.formscoding)