Different data shown based on user input

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
0
Utf
12/31/2007 10:45:01 AM
access.queries 6343 articles. 1 followers. Follow

10 Replies
1140 Views

Similar Articles

[PageSpeed] 27

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@discussions.microsoft.com> wrote in message 
news:748FF996-5734-4CB8-B3EC-A8D121412B3C@microsoft.com...
> 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 


0
Jeanette
1/1/2008 5:51:21 AM
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@discussions.microsoft.com> wrote in message 
> news:748FF996-5734-4CB8-B3EC-A8D121412B3C@microsoft.com...
> > 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 
> 
> 
> 
0
Utf
1/2/2008 8:08:01 AM
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@discussions.microsoft.com> wrote in message
news:0076F176-FA2F-4879-9716-E4D8CD792E40@microsoft.com...
> 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@discussions.microsoft.com> wrote in message
>> news:748FF996-5734-4CB8-B3EC-A8D121412B3C@microsoft.com...
>> > 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
>>
>>
>>



0
Jeanette
1/2/2008 10:05:43 AM
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" 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@discussions.microsoft.com> wrote in message
> news:0076F176-FA2F-4879-9716-E4D8CD792E40@microsoft.com...
> > 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@discussions.microsoft.com> wrote in message
> >> news:748FF996-5734-4CB8-B3EC-A8D121412B3C@microsoft.com...
> >> > 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
> >>
> >>
> >>
> 
> 
> 
> 
0
Utf
1/2/2008 10:34:01 AM
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


"Maver1ck666" <Maver1ck666@discussions.microsoft.com> wrote in message 
news:57A9E560-17DF-44BB-B75E-ABDD29782132@microsoft.com...
> 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" 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@discussions.microsoft.com> wrote in message
>> news:0076F176-FA2F-4879-9716-E4D8CD792E40@microsoft.com...
>> > 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@discussions.microsoft.com> wrote in message
>> >> news:748FF996-5734-4CB8-B3EC-A8D121412B3C@microsoft.com...
>> >> > 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
>> >>
>> >>
>> >>
>>
>>
>>
>> 


0
Jeanette
1/2/2008 9:40:36 PM
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" <Maver1ck666@discussions.microsoft.com> wrote in message 
news:57A9E560-17DF-44BB-B75E-ABDD29782132@microsoft.com...
> 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" 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@discussions.microsoft.com> wrote in message
>> news:0076F176-FA2F-4879-9716-E4D8CD792E40@microsoft.com...
>> > 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@discussions.microsoft.com> wrote in message
>> >> news:748FF996-5734-4CB8-B3EC-A8D121412B3C@microsoft.com...
>> >> > 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
>> >>
>> >>
>> >>
>>
>>
>>
>> 


0
Jeanette
1/2/2008 10:44:28 PM
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 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" <Maver1ck666@discussions.microsoft.com> wrote in message 
> news:57A9E560-17DF-44BB-B75E-ABDD29782132@microsoft.com...
> > 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" 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@discussions.microsoft.com> wrote in message
> >> news:0076F176-FA2F-4879-9716-E4D8CD792E40@microsoft.com...
> >> > 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@discussions.microsoft.com> wrote in message
> >> >> news:748FF996-5734-4CB8-B3EC-A8D121412B3C@microsoft.com...
> >> >> > 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
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
> >> 
> 
> 
> 
0
Utf
1/3/2008 7:33:00 AM
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@discussions.microsoft.com> wrote in message 
news:549C7CCE-1045-4D79-AC44-DA84E2C3448E@microsoft.com...
> 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 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" <Maver1ck666@discussions.microsoft.com> wrote in message
>> news:57A9E560-17DF-44BB-B75E-ABDD29782132@microsoft.com...
>> > 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" 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@discussions.microsoft.com> wrote in message
>> >> news:0076F176-FA2F-4879-9716-E4D8CD792E40@microsoft.com...
>> >> > 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@discussions.microsoft.com> wrote in 
>> >> >> message
>> >> >> news:748FF996-5734-4CB8-B3EC-A8D121412B3C@microsoft.com...
>> >> >> > 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
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>> >>
>>
>>
>> 


0
Jeanette
1/3/2008 11:02:44 AM
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@discussions.microsoft.com> wrote in message 
> news:549C7CCE-1045-4D79-AC44-DA84E2C3448E@microsoft.com...
> > 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 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" <Maver1ck666@discussions.microsoft.com> wrote in message
> >> news:57A9E560-17DF-44BB-B75E-ABDD29782132@microsoft.com...
> >> > 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" 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@discussions.microsoft.com> wrote in message
> >> >> news:0076F176-FA2F-4879-9716-E4D8CD792E40@microsoft.com...
> >> >> > 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@discussions.microsoft.com> wrote in 
> >> >> >> message
> >> >> >> news:748FF996-5734-4CB8-B3EC-A8D121412B3C@microsoft.com...
> >> >> >> > 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
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
Utf
1/3/2008 11:13:01 AM
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@discussions.microsoft.com> wrote in message 
news:2315B8AD-627A-4E71-ABBA-8A97CE9C8388@microsoft.com...
> 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@discussions.microsoft.com> wrote in message
>> news:549C7CCE-1045-4D79-AC44-DA84E2C3448E@microsoft.com...
>> > 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 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" <Maver1ck666@discussions.microsoft.com> wrote in message
>> >> news:57A9E560-17DF-44BB-B75E-ABDD29782132@microsoft.com...
>> >> > 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" 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@discussions.microsoft.com> wrote in 
>> >> >> message
>> >> >> news:0076F176-FA2F-4879-9716-E4D8CD792E40@microsoft.com...
>> >> >> > 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@discussions.microsoft.com> wrote in
>> >> >> >> message
>> >> >> >> news:748FF996-5734-4CB8-B3EC-A8D121412B3C@microsoft.com...
>> >> >> >> > 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
>> >> >> >>
>> >> >> >>
>> >> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>> 


0
Jeanette
1/3/2008 7:40:45 PM
Reply:

Similar Artilces:

Combobox Help needed
Hello, I have been trying to populate a Combobox with a filter set to what is being typed in the combo's edit control. If I type in "C" it selects the first 40 "C" then I would like to type in "u" clear the listbox portion and put in the first 40 "Cu" etc.. I would need to clear only the combo's edit box but unfortunately ResetContent()also clears the edit box. It gets the first 40 "C" but then the edit box is cleared. I have also tried using DeleteString. void CCustView::OnEditupdateCombo1() { UpdateData(); if (i>0) { m_CbCust....

Comparing Fields in Different Columns
I was wondering if there is a way two compare the fields in 2 different columns. Then if those fields are identical they would be removed. Is there a basic function to do this or do I need to write a macro? Thanks for the help. Hi......... Assuming your columns are A and B, in cell C1 put this formula and copy it down column C =IF(A1=B1,1,"") then sort on column C and delete all the rows that have a 1 in column C And, if you do this frequently, it could be recorded into a macro........ Vaya con Dios, Chuck CABGx3 "Guggenheim" <johlstrom@gmail.com> wrote in me...

Writing programs for the manipulation of data cells?
I'm new to Excel and I think I can do this with macros. In that case, where can I find a list of Excel functions? On 18 Mar, 10:38, "Scott H" <nospam> wrote: > I'm new to Excel and I think I can do this with macros. In that case, where > can I find a list of Excel functions? Have you tried the help? I would also highly recommend Chip Pearson's site: http://www.cpearson.com/excel/MainPage.aspx The great thing about Excel is there are so many high quality advice sites that are easily found. ...

comparing data in different sheets
I have data set for each quarter, like sales, profits, margins. The dat is for more than 1000 companies and in different worksheets: Eac quarter is in each seperate worksheet. It gets frustrating to keep on switching between each worksheet to ge a comparative number. Is there a way out -- Message posted from http://www.ExcelForum.com In situations like this I generally create a summary sheet and reference the cells from all of the other worksheets. Set up the layout for the summary sheet and then select a cell you want to mirror the value from another worksheet. With the cell selected, ...

extend XY data ranges?
I was wondering if there is a way to extend the X and Y data ranges on a line graph? I am plotting some data, and I want to be able to extend the x and y axes so that I can predict future vaules, however I cannot seem to figure out how to do this. Any suggestions would be greatly appreciated. Thank you! Jeff hi Jeff, if you select the Y-axis and then right-click, select "Format Axis". On the "Scale" tab, uncheck the relevant "Auto" checkboxes and enter in the scale that you're after. HTH, Katherine "Jeff" <anonymous@discussions.micro...

Receiving Blank Emails from Outlook 2003 User
Hi! One of my users is sending a blank email to everyone that sends her an email. I have no idea how this is happening. But I send her an email and get a blank one from her. Any clues? She uses Outlook 2003 with the latest patches. PMD Is Out of Office turned on? Pascal Dorion wrote: > Hi! > > > One of my users is sending a blank email to everyone that sends her an > email. I have no idea how this is happening. But I send her an email and get > a blank one from her. > > Any clues? > > She uses Outlook 2003 with the latest patches. > > > P...

Data to Chart Question/How-to
Below is my data layout as it is right now. What I want my chart to look like is described as... The Vertical Left Area is the Document ID Column The Horizontal Bottom Row is the Reviewer (Revwr #?) The Secondary Vertical Right is the Days Overdue Numbers So far I've tried to use a Scatter, Line, Bar, and also Combining them too but nothing seems to yield the results I am looking for. I hope that the data outline below shows up well enough but if not then let me know and I'll try again. Document ID Revwr #1 Revwr #2 Revwr #3 Revwr #4 Days Overdue Document #001 X X ...

Rules Based On Numeric Field Values
Hi, I sometimes get emails which have special standardized notifications in them. Any "special attention" areas are in a consistent format which will also contain numbers that are normally 0. If they aren't 0, then there is some kind of problem. So, the number values can be from 0 to any value. Is there a way to "flag" these in a rule? Ideally, I'd like to change the message color of or priority based on the values seen. Below is partial output from one of the emails with example fields high-lighted: Successes : 10 Failures : 0 <==== Aborted : ...

two users on one computer
I have Windows 7 and use Outlook 2003. I really want to separate my husbands emails from mine, so I created a new email account for him. How can I have his emails sent to a different location within Outlook? You need to create his personal username in Windows 7 so that he can manage his own emails and documents; and you can manage your own emails and documents. If you don't know how to create his own username then I suggest post your query here: http://social.technet.microsoft.com/Forums/en/w7itproinstall/threads hth "karen" <karen@discussions.micro...

excel charts look drastically different on different computers
When we open excel charts on different computers in our office the formatting looks drastically different (for example, data labels and arrows on charts are in different locations). This is a huge problem because it means that the formatting has to be fixed each time someone opens an excel chart. This was never a problem until recently when some of our staff got new computers and for a short time they were using Office 2007 (everyone else in the office was using Office 2003). We've switched everyone back to Office 2003, but the problem with excel charts persists. When u ad...

Grouping to included ranges for which no base data exists
I have a set of data which has numbers ranging from 1700 to 32100. I'd like to pivot table them and group them in bands of 100, starting at 1700. I'd like my pivot table to show all of the bands that exist. The problem is, if I have no data within the band 2200 to 2300 that band wont appear in my grouped pivot table. Any help most appreciated. Thanks in advance. Gerry. ...

Users locked out after changing password
We are issues with users changing their passwords in Dynamics GP, existing the application, and upon logging in again with their new password, getting immediately locked out of the system. They then have to be unlocked at the sql level, but upon changing the password, the same thing happens. We have Enforce Password Policy on, and we are on SP3 of Dynamics GP. Does anybody have any insight to resolving this issue? ------=_NextPart_0001_6BB9EA6B Content-Type: text/plain Content-Transfer-Encoding: 7bit Hi Randall It is possible that the user is getting locked out as they have changed so...

Offline User does not have Licence or Role
I have an issue where all users can successfully go offline, no errors etc. When disconnected from the network the users get CRM error, no licence or role, so they can not use CRM. If they are connected to the domain but CRM is offline it works fine, when they go back online there are no errors. I have uninstalled and re installed one client including deleting the local db and registry keys etc but to no avail. Has anyone else had this issue or know why this would occur please, the clients are on XP Pro SP1, the CRM is 1.2 and Office 2000. Colin can you tell us the exact message the...

Users can't browse or see local site in GAL
My users cannot view RECIPIENTS CONTAINER objects, of our local EXCHANGE SITE, in the GAL. However they can still send email to users in our local site. Our site doesn't even show up in the GAL, but they can browse other Exchange Sites in the GAL. Now here's the kicker, Domain/Exchange Admins can view see the local site name in the GAL and browse to see the recipient containers within the local site. This leads me to believe that permission some how got screwed up. But, nothing jumps out at me! Thanks for the support, BR Check out http://support.microsoft.com/default.aspx?scid...

building an external link based on a cell value
I would like to build an external link in a spreadsheet that automatically changes based on a cell value. For example, I have the following link: =SUMIF('[Location Income Statements 2004.xls]Mar04'! $14:$14,P5,'[Location Income Statements 2004.xls]Mar04'! $18:$18) Instead of Mar04 in the formula above, I would like to use a cell's contents.... cell A8 for example. Is this possible? Thanks, Jason Jason You can use the INDIRECT function. =SUMIF(INDIRECT("'[Location Income Statements 2004.xls]" & A8 & "'!$14:$14"),P5... Indirect ...

Difference between these two PUBLIC FOLDERS
FIRST ORGANIZATION > ADMINISTRATIVE GROUPS > FIRST ADMINISTRATIVE GROUP > SERVERS > Server name > FIRST STORAGE GROUP > PUBLIC FOLDER STORE > PUBLIC FOLDERS and FIRST ORGANIZATION > ADMINISTRATIVE GROUPS > FIRST ADMINISTRATIVE GROUP > FOLDERS > PUBLIC FOLDERS What are the difference????? the first one is the folders that exist in that pubic information store...the second one is your entire public folder hierarchy... "Calvin Lai" <Calvin.Lai@shaw.ca> wrote in message news:1ff01a54.0409271350.96236c@posting.google.com... > FIRST ORGANIZATI...

Calculations based on PivotTable information
I am trying to create calculations for Commissions based on Pivot Table information. However, When i try to filter(change the dimension) to show individual Salespeople the columns that i refer to in my calculations change, thus my calculations are in accurate. This is what i am using to calculate when all salespeople are present on the pivot table In Column J IF(AND(ISNUMBER(SEARCH("*total",C5)),F5<>""),F5*0.03,"") In Column L IF(AND(ISNUMBER(SEARCH("*total",C5)),G5<>""),G5*0.02,"") any help would be appreciated. ...

collect data via email
We're looking for solution to collect intake data from multiple agencies (not on network), and get into our (currently 2003) Access database. With 2007, the 'EMail data collect' facility - can that collect and insert multiple rows with 1 email - or each email/form processes only 1 row..? thanks! don You will send a mail asking the user either to update a certain row or add information to a new record. So in both situations you are tied to single row processing... Secondly you have two options to choose from. Plain html which will present the user with a html emai...

formulas and external data
i have an issue trying to calculate data based on a query i import the data with all my columns and records, at the end i insert a column with a formula that calculates 4 different cells in the row then i run a query to remove null (zero) value data from a certain field, then the formual column that i added manually doesn't look right. for example, if i have 800 rows of data, i copied that formula to all 800 rows in the last column following the external data when i run the query, all the zero values are removed (doing what it should) and the formulas stay, except the last row that is...

Different Prices in Different Stores
I run 8 stores, and HO and want two of the stores to have different prices to the other stores. We handle all pricing from HO. I've never done this before, and although I can read the manual, it's often better to ask the people that have done it for their experience, what mistakes have been made, and what to look out for. thanks Chris England Style 304 Change Item Prices (regular) worksheet, check mark the stores you want affected by those prices, so the worksheet gets created just for those two stores...if all items will have a different price, select ALL DEPARTMENTS so a...

userS list
How is possible to get the list of all users defined on a PC (win XP) ? Thanks -- Alberto if you want to contact me remove NOSPAM. from email address Take a look at the NetUserEnum() & NetUserGetInfo() API's. -- Cheers Check Abdoul [ VC++ MVP ] ----------------------------------- "Alberto" <a.forlai@nospam.virgilio.it> wrote in message news:eQoyEHgUDHA.1832@TK2MSFTNGP09.phx.gbl... > How is possible to get the list of all users defined on a PC (win XP) ? > > Thanks > > -- > Alberto > > if you want to contact me remove NOSPAM. from ema...

Restricting data
Hi I am trying to put value restrictions on certain elements in an xml file using a schema file. The syntax in my schema file looks like the following <xs:element name="AXYZMachines" msdata:IsDataSet="true" msdata:UseCurrentLocale="true"> <xs:complexType> <xs:choice minOccurs="0" maxOccurs="unbounded"> <xs:element name="MotionParameters"> <xs:complexType> <xs:sequence> <xs:element name="FeedRateMAX" type="xs:double"...

Extract Specific Data
Hi Groupies The data in my Job Number field looks like this: DC-2010-1-63 - City Hazard AB-2010-5-960 - Shop How can I extract everything up to the 4th dash so that I am left with: DC-2010-1-63 AB-2010-5-960 Thanks in advance. -- Thanks for taking the time! CJ --------------------------------------------------------- Know thyself, know thy limits....know thy newsgroups! CJ wrote: >The data in my Job Number field looks like this: >DC-2010-1-63 - City Hazard >AB-2010-5-960 - Shop > >How can I extract everything up to the 4th dash so that I am left wit...

Retrieve data #2
I have a spreadsheet "franchise Stores" containing franchises in column "a" and their stores store# in column "b", a simple database with most franchises having more than one store. Every day different stores submit their problems to us using only their store numbers. I have a seperate spreadsheet for these calls "call log". I manually look up the stores franchise with the store number using the Find Function in "franchise Stores", and copy, paste it to "call log". Is there a way to have excel do this on its own? There are too many to...

Only The Report Header Is Printing
I use an Access application here at work, which includes a button on a form to print a report. When I try to print the report nothing comes out but the header of the report. I'm very new to using Access and really don't know the ins and outs of it at all. I have looked in a couple of different Access training books, but I don't know what I'm really looking for. I'm frustated and need help. Does the report have a RecordSource? A table or query that is providing the data to the report? Do you have controls on the report that are bound to fields in that RecordSource table...