Re: Different data shown based on user input

Hi Mav,
lol about the check boxes, what a joke.
First things first, get this code working without any errors in the code
module for the form before doing anything about putting in into a standard
module.
A form that knows which check boxes have been checked can be created like
this.
--Create a new form in design view with its default view set to single form
--Put 4 checkboxes on the form and 1 command button
--put the BuildQuery code I gave you earlier in the form and hook it up to
the click event of the command button
--save the form as frmCheckboxes
--open the form in form view
--check only one check box and observe that the other checkboxes are
unchecked
--click the button to run the code and step through the code


How is your current form for doing the reports set up? Can you change it so
that it knows which checkboxes have been checked?

Jeanette Cunningham




"Maver1ck666" <Maver1ck666@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0076F176-FA2F-4879-9716-E4D8CD792E40@xxxxxxxxxxxxxxxx
Hi Jeanette,

Hope you had a nice new yar!

Thanks for the reply, sounds like exactly what I need.

Have a small problem though. I have inserted the code into a new module
and
got the form to call it but it just runs through it and does nothing at
the
end. Is there something I am missing?

Also, for some reason it wouldn't allow the Me. to be added so I had to
put
the full path of the field name instead (Forms!Frm....). Is that correct?

Oh, and another thing lol, when stepping through the code, I noticed the
result for each check box said true even though some weren't selected. Any
ideas on that please?

Kind regards,
Mav.

"Jeanette Cunningham" wrote:

Hi,
are you using code to do this and not a macro?
The basic process is to build the query using the fields that have been
ticked one checkbox at a time.

Here is an example:

Private Function BuildQuery
Dim strStub as String
Dim strSQL as String
Dim lngLen as Long

strStub = "SELECT tblEmployees.Name, "

If me.chk1 = True then
strStub = strStub & "tblEmployees.Address1, "
End if

If me.chk2 = True then
strStub = strStub & "tblEmployees.City, "
End if

'continue for all the checkboxes

'build the query string by removing the trailing comma and space
lngLen = Len(strStub) -2
If lngLen > 0 Then
strStub = Left$(strStub, lngLen)
End If
'Debug.Print strStub

If len(strStub) > 0 Then
'build the rest of the query such as the from, where and orderby
clauses
'export the query
strSQL = strStub & "rest of the query"
Else
End if

End Sub

Jeanette Cunningham

"Maver1ck666" <Maver1ck666@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:748FF996-5734-4CB8-B3EC-A8D121412B3C@xxxxxxxxxxxxxxxx
Bit of a strange one for you.

I have a form which has a number of different tick boxes (unlinked to a
recordset). Each tick box represents a field within a number of
different
tables.

What I want to do is to have a user tick a number of fields and click a
button which will export only the fields they have ticked/selected into
an
excel spread***.

Does that make sense? Let me know if you want me to explain it any
better.

Cheers for the help.

Maver1ck666






.