Re: Different data shown based on user input

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









.

Loading