Re: Different data shown based on user input

Tech-Archive recommends: Speed Up your PC by fixing your registry

Mav,
glad the checkbox thing worked. Yes you can export to excel using transfer
spread*** without using a form or report that shows the data. The VBA help
on transfer spread*** is easy to understand. Build your SQL and export the
SQL using transfer spread***.

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









.

Quantcast