Query by Form

[Fred via AccessMonster.com]

Is there some code i could use to from a form:

1. have check boxes to pick fields
2. then run the query

the fields will all belong to one query which is called queryMe. The user
picks the fields that they want and then click on a Run Button that runs the
query.

Can someone help me with this?

Thanks,
Fred

 

[TonyT]

Hi Fred,

If you can post the field names and the checkbox names it would be a lot
easier to answer the question.

What you are asking is easy enough to do, but without more information I
think the answer may be more confusing then helpful.

 

[Fred via AccessMonster.com]

Hi Tony,
my fields are:

custid, custname, custNotes, prodID, prodName, prodNotes

so, the user in this query may only want to see prodname and custnotes Or
in later decide he only wants a query with custname, custnotes, prodname and
prodnotes

the key is custid

Hi Fred,

If you can post the field names and the checkbox names it would be a lot
easier to answer the question.

What you are asking is easy enough to do, but without more information I
think the answer may be more confusing then helpful.

Is there some code i could use to from a form:

[quoted text clipped - 9 lines]

Thanks,
Fred

 

[TonyT]

I'm getting confused here, which isn't uncommon. What are you wanting to
display where and how. How does having a checkbox saying search custid help
you if you don't have any other fields with a custid in? Do your users
actually know the custid and prodid of customers and products in the
database? Is this query based on a single table or query, if so, whats it
called.....more info please.

Hi Tony,
my fields are:

custid, custname, custNotes, prodID, prodName, prodNotes

so, the user in this query may only want to see prodname and custnotes Or
in later decide he only wants a query with custname, custnotes, prodname and
prodnotes

the key is custid

Hi Fred,

If you can post the field names and the checkbox names it would be a lot
easier to answer the question.

What you are asking is easy enough to do, but without more information I
think the answer may be more confusing then helpful.

Is there some code i could use to from a form:

[quoted text clipped - 9 lines]

Thanks,
Fred

 

[Fred via AccessMonster.com]

Ok, let me start over.

I have a single query with all the fields i need.

with this query, i need the user to pick the fields they want to see in this
query.

I thought the best way, is to create a form tied to this query.

on this form, the user would pick the fields they want to see in the query
from a dropdown.

so, the dropdowns would be two columns. the fields in each drop down are
below:
customer Product (these are label names for the dropdown columns
______________________
custname custprod
custaddr prodid
custnote prodnote

these are three rows of drop downs. The user picks custname and custnote in
the first column. And picks custprod and prodid in the next column. then
clicks on the "run" button to run the query.

I'm getting confused here, which isn't uncommon. What are you wanting to
display where and how. How does having a checkbox saying search custid help
you if you don't have any other fields with a custid in? Do your users
actually know the custid and prodid of customers and products in the
database? Is this query based on a single table or query, if so, whats it
called.....more info please.

Hi Tony,
my fields are:

[quoted text clipped - 20 lines]

 

[Fred via AccessMonster.com]

Still trying to figure this one out... please help.

Ok, let me start over.

I have a single query with all the fields i need.

with this query, i need the user to pick the fields they want to see in this
query.

I thought the best way, is to create a form tied to this query.

on this form, the user would pick the fields they want to see in the query
from a dropdown.

so, the dropdowns would be two columns. the fields in each drop down are
below:
customer Product (these are label names for the dropdown columns
______________________
custname custprod
custaddr prodid
custnote prodnote

these are three rows of drop downs. The user picks custname and custnote in
the first column. And picks custprod and prodid in the next column. then
clicks on the "run" button to run the query.

I'm getting confused here, which isn't uncommon. What are you wanting to
display where and how. How does having a checkbox saying search custid help

[quoted text clipped - 8 lines]

 

[TonyT]

Ok, now I understand better what you are doing. The coding is easier if you
do use checkboxes rather than listboxes, so below is with multiple
checkboxes, but is written simply rather than looping as I might if there are
many checkboxes;

Private Sub MyButton_Click()
Dim strQueryFlds as String

If Me.chkCustName = -1 Then
strQueryFlds = "custname, "
End If
If Me.chkCustAddr = -1 Then
strQueryFlds = strQueryFlds & "custaddr, "
End If
If Me.chkCustNote = -1 Then
strQueryFlds = strQueryFlds & "custnote, "
End If
.....
and so on for each checkbox, then at the end;

If not strQueryFlds = "" Then
strQueryFlds = "SELECT " & strQueryFlds & "FROM yourQueryNameHere"
Else
strQueryFlds = "SELECT * FROM yourQueryNameHere"
End If

Now you can do what you want with the recordset definition you have created,
eg run it or set it as the recordsource for a subform, another form or
report, or use it as a rowsource for a listbox.

You could also add " ORDER BY anyFieldNameYouLike" at the end too if want to
see it in a particular order.

Without wishing to confuse you, you can actually remove the '= -1' part from
each of the If's above, but to make it easier to understand I put them in
anyway. Basically if there is only a yes/no or true/false or -1/0 possible
value of a field or string you can omit the = True/Yes/-1 part as Access will
automatically evaluate it as looking for True unless you state otherwise.

hope this helps, and check the code compiles as it's only aircode and not
tested, make sure you leave the spaces after the commas for each line of
strQueryFlds and keep the same spaces in the last part otherwise the query
will fail.

TonyT

Ok, let me start over.

I have a single query with all the fields i need.

with this query, i need the user to pick the fields they want to see in this
query.

I thought the best way, is to create a form tied to this query.

on this form, the user would pick the fields they want to see in the query
from a dropdown.

so, the dropdowns would be two columns. the fields in each drop down are
below:
customer Product (these are label names for the dropdown columns
______________________
custname custprod
custaddr prodid
custnote prodnote

these are three rows of drop downs. The user picks custname and custnote in
the first column. And picks custprod and prodid in the next column. then
clicks on the "run" button to run the query.

I'm getting confused here, which isn't uncommon. What are you wanting to
display where and how. How does having a checkbox saying search custid help
you if you don't have any other fields with a custid in? Do your users
actually know the custid and prodid of customers and products in the
database? Is this query based on a single table or query, if so, whats it
called.....more info please.

Hi Tony,
my fields are:

[quoted text clipped - 20 lines]

Thanks,
Fred

 

[Fred via AccessMonster.com]

Thank you for the checkbox code. It works great. But I'm trying to run the
recordset from a subform. I have

strQueryFlds = "custname"
Me.Query1_subform.Form.RecordSource = "SELECT " & strQueryFlds & " FROM
Query1"

I'm getting syntax error. Please help.

Ok, now I understand better what you are doing. The coding is easier if you
do use checkboxes rather than listboxes, so below is with multiple
checkboxes, but is written simply rather than looping as I might if there are
many checkboxes;

Private Sub MyButton_Click()
Dim strQueryFlds as String

If Me.chkCustName = -1 Then
strQueryFlds = "custname, "
End If
If Me.chkCustAddr = -1 Then
strQueryFlds = strQueryFlds & "custaddr, "
End If
If Me.chkCustNote = -1 Then
strQueryFlds = strQueryFlds & "custnote, "
End If
....
and so on for each checkbox, then at the end;

If not strQueryFlds = "" Then
strQueryFlds = "SELECT " & strQueryFlds & "FROM yourQueryNameHere"
Else
strQueryFlds = "SELECT * FROM yourQueryNameHere"
End If

Now you can do what you want with the recordset definition you have created,
eg run it or set it as the recordsource for a subform, another form or
report, or use it as a rowsource for a listbox.

You could also add " ORDER BY anyFieldNameYouLike" at the end too if want to
see it in a particular order.

Without wishing to confuse you, you can actually remove the '= -1' part from
each of the If's above, but to make it easier to understand I put them in
anyway. Basically if there is only a yes/no or true/false or -1/0 possible
value of a field or string you can omit the = True/Yes/-1 part as Access will
automatically evaluate it as looking for True unless you state otherwise.

hope this helps, and check the code compiles as it's only aircode and not
tested, make sure you leave the spaces after the commas for each line of
strQueryFlds and keep the same spaces in the last part otherwise the query
will fail.

TonyT

Ok, let me start over.

[quoted text clipped - 32 lines]

 

[TonyT]

use;
Me!Query1_subform.Form.Recordsource = "SELECT '" & strQueryFlds & "' FROM
Query1"

all on one line.

You need ' ' around the custsurname field to tell it that you are passing a
string. Omit them for numbers and use # # instead for dates. They should be
inside the SQL string like " ' " & & " ' " (hard to see when al put
together on here)

TonyT

Thank you for the checkbox code. It works great. But I'm trying to run the
recordset from a subform. I have

strQueryFlds = "custname"
Me.Query1_subform.Form.RecordSource = "SELECT " & strQueryFlds & " FROM
Query1"

I'm getting syntax error. Please help.

Ok, now I understand better what you are doing. The coding is easier if you
do use checkboxes rather than listboxes, so below is with multiple
checkboxes, but is written simply rather than looping as I might if there are
many checkboxes;

Private Sub MyButton_Click()
Dim strQueryFlds as String

If Me.chkCustName = -1 Then
strQueryFlds = "custname, "
End If
If Me.chkCustAddr = -1 Then
strQueryFlds = strQueryFlds & "custaddr, "
End If
If Me.chkCustNote = -1 Then
strQueryFlds = strQueryFlds & "custnote, "
End If
....
and so on for each checkbox, then at the end;

If not strQueryFlds = "" Then
strQueryFlds = "SELECT " & strQueryFlds & "FROM yourQueryNameHere"
Else
strQueryFlds = "SELECT * FROM yourQueryNameHere"
End If

Now you can do what you want with the recordset definition you have created,
eg run it or set it as the recordsource for a subform, another form or
report, or use it as a rowsource for a listbox.

You could also add " ORDER BY anyFieldNameYouLike" at the end too if want to
see it in a particular order.

Without wishing to confuse you, you can actually remove the '= -1' part from
each of the If's above, but to make it easier to understand I put them in
anyway. Basically if there is only a yes/no or true/false or -1/0 possible
value of a field or string you can omit the = True/Yes/-1 part as Access will
automatically evaluate it as looking for True unless you state otherwise.

hope this helps, and check the code compiles as it's only aircode and not
tested, make sure you leave the spaces after the commas for each line of
strQueryFlds and keep the same spaces in the last part otherwise the query
will fail.

TonyT

Ok, let me start over.

[quoted text clipped - 32 lines]

Thanks,
Fred