Different data shown based on user input

[Maver1ck666]

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 spreadsheet.

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

Cheers for the help.

Maver1ck666

 

[Jeanette Cunningham]

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]

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]

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]

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 spreadsheet (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]

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

Jeanette Cunningham

 

[Jeanette Cunningham]

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 worksheet 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 worksheet (perhaps all in same workbook) for each
query/report.
Users can browse whichever worksheet they wish without making any selections
from checkboxes.
When the access data is updated, users use the refresh toolbar to refresh
their worksheet with the latest data from Access.

Jeanette Cunningham

 

[Maver1ck666]

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]

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

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

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 worksheet 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 worksheet (perhaps all in same workbook) for each
query/report.
Users can browse whichever worksheet they wish without making any
selections
from checkboxes.
When the access data is updated, users use the refresh toolbar to refresh
their worksheet with the latest data from Access.

Jeanette Cunningham

 

[Maver1ck666]

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).

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

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

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 worksheet 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 worksheet (perhaps all in same workbook) for each
query/report.
Users can browse whichever worksheet they wish without making any
selections
from checkboxes.
When the access data is updated, users use the refresh toolbar to refresh
their worksheet with the latest data from Access.

Jeanette Cunningham


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 spreadsheet (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.

:

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




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.

:

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

message
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 spreadsheet.

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

Cheers for the help.

Maver1ck666

 

[Jeanette Cunningham]

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

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).

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

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



:

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 worksheet 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 worksheet (perhaps all in same workbook) for each
query/report.
Users can browse whichever worksheet they wish without making any
selections
from checkboxes.
When the access data is updated, users use the refresh toolbar to
refresh
their worksheet with the latest data from Access.

Jeanette Cunningham


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 spreadsheet (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.

:

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




message
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.

:

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

message
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 spreadsheet.

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

Cheers for the help.

Maver1ck666