Re: Different data shown based on user input

Mav,
my advice changes with the info that there are over 200 checkboxes.
200 would be a bit too confusing for users.
I suggest you use combo boxes. I usually use combo boxes when I want users
to choose combinations of fields to run reports.
You can also use a report menu screen that lets users pick the general type
of report, when they make their selection another form opens with the combo
boxes.
After you know what type of report they want, you show only the relevant
combo boxes, and also set the row sources for the combo boxes to something
suitable for the type of report required.

Jeanette Cunningham



"Maver1ck666" <Maver1ck666@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:2315B8AD-627A-4E71-ABBA-8A97CE9C8388@xxxxxxxxxxxxxxxx
Thanks for the reply Jeanette.

Could be a problem as ideally, there will be over 200 tick boxes so lots
of
different combinations :(

Am I to presume then that it wont be possible to get the data from the
query? What if we adapted the SQL statement to make it a create table
query
and export the data that way? (which I have tried but miseraly failed).

"Jeanette Cunningham" wrote:

Mav,
To do a saved query for the equivalent to checkbox 1 being checked:
You would create a query in the query design grid, you would include Tbl
ComplaintDetails.Account as well as any other fields needed for the
query
and save the query with a name such as qRptComplaintAccount.
For the equivalent of checkbox 2, save query as qRptPlanType and include
the
field Tbl ComplaintDetails.PlanType
For the equivalent of check 1 and 2 include both Tbl
ComplaintDetails.Account and Tbl ComplaintDetails.PlanType
and so on. As you have only 4 checkboxes, doing it this way should be
manageable with the number of saved queries you would need.

Jeanette Cunningham


"Maver1ck666" <Maver1ck666@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:549C7CCE-1045-4D79-AC44-DA84E2C3448E@xxxxxxxxxxxxxxxx
Smashing!

Im not sure though how I can save the query (Im fairy new at writing
SQL
stuff).

This is the code I have so far:

Dim strStub As String
Dim strSQL As String
Dim lngLen As Long
Dim db As Database
Dim rst As Recordset
Dim Qdf As QueryDef

strStub = "SELECT "

If Me.Chk1 = True Then
strStub = strStub & "[Tbl ComplaintDetails].Account, "
End If

If Me.chk2 = True Then
strStub = strStub & "[Tbl ComplaintDetails].PlanType, "
End If

If Me.chk3 = True Then
strStub = strStub & "[Tbl ClientDetails].ClTitle, "
End If

If Me.chk4 = True Then
strStub = strStub & "[Tbl ClientDetails].ClSurname, "
End If

'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
'export the query
strSQL = strStub & " FROM [Tbl ComplaintDetails], [Tbl
ClientDetails];"
Else
End If

Set db = CurrentDb

Set Qdf = db.CreateQueryDef("")
With Qdf

.SQL = strSQL

Set rst = .OpenRecordset()

End With



"Jeanette Cunningham" wrote:

Mav,
it would be possible to let users see the results in excel without
going
to
the bother of exporting.
If you have a saved query in your database, it can be opened in excel.
--open a new work*** in excel
--Data >> Import External Data >> Import Data
--navigate to your saved query
--follow the prompts as excel guides you through the process

Back in access, you will need a few saved queries - one for each
report.
Create a separate work*** (perhaps all in same workbook) for each
query/report.
Users can browse whichever work*** they wish without making any
selections
from checkboxes.
When the access data is updated, users use the refresh toolbar to
refresh
their work*** with the latest data from Access.

Jeanette Cunningham


"Maver1ck666" <Maver1ck666@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:57A9E560-17DF-44BB-B75E-ABDD29782132@xxxxxxxxxxxxxxxx
Hey that works! The correct tick boxes were showing this time.

At the moment, there is no form/report for showing the data. All I
want
it
to do is to export the data straight into an excel spread*** (or
to
even
publish the results with Excel so that the user can save the data
theirselves
if its easier). Any suggestions on how to do this please?

Mav.

"Jeanette Cunningham" wrote:

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















.