Query by form

[Tom]

Hello,

I have a database of employee data in access and I am trying to create
a form with combo boxes for criteria (ex. gender, office, position,
etc.) that let the user select criteria from drop down menus (ex. they
may select Male, New York, Manager) and those criteria will be used to
run a query (ie Query by Form). I can do this with text boxes but when
I tried to use combo boxes no records were returned in the query. Any
idea what I am doing wrong?

Also, is there an easy way to remove duplicates from the combo boxes?
They are getting their entries/selections from the data table and are
displaying duplicates. For example, if the data table contained 5 rows
of data with 3 men and 2 women, then the Gender combo box would contain
something like: Male, Female, Male, Male, Female.

Thanks.

[pietlinden]

Tom wrote:

Hello,

I have a database of employee data in access and I am trying to create
a form with combo boxes for criteria (ex. gender, office, position,
etc.) that let the user select criteria from drop down menus (ex. they
may select Male, New York, Manager) and those criteria will be used to
run a query (ie Query by Form). I can do this with text boxes but when
I tried to use combo boxes no records were returned in the query. Any
idea what I am doing wrong?
Sounds like either you have a multi-column combobox and you're not
filtering on the column you think you are. Although how that would
happen in a true filter by form, I'm not sure.

Also, is there an easy way to remove duplicates from the combo boxes?

Change the SQL.

SELECT Position
FROM tblEmployees...

to

SELECT DISTINCT Position....

[fredg]

On 29 Jun 2006 07:23:43 -0700, Tom wrote:

Hello,

I have a database of employee data in access and I am trying to create
a form with combo boxes for criteria (ex. gender, office, position,
etc.) that let the user select criteria from drop down menus (ex. they
may select Male, New York, Manager) and those criteria will be used to
run a query (ie Query by Form). I can do this with text boxes but when
I tried to use combo boxes no records were returned in the query. Any
idea what I am doing wrong?
Are you sure the BOUND column of the combo box is the same datatype as
the Field in the query?
for example, if the City combo had "City" as text in it's bound
column, but the query Field was actually CityID ( a number datatype)?
Or Vice Versa?
Also, is there an easy way to remove duplicates from the combo boxes?
They are getting their entries/selections from the data table and are
displaying duplicates. For example, if the data table contained 5 rows
of data with 3 men and 2 women, then the Gender combo box would contain
something like: Male, Female, Male, Male, Female.

Thanks.

To remove duplicates from a combo box rowsource that is based upon a
table, create a query to select the values instea. Then change the
query SQL "Select YourTable.FieldName From TableName Order By
FieldName;" to
"Select Distinct YourTable.FieldName From TableName Order By
FieldName;"

--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Tom]

Adding the word "DISTINCT" to the SQL in the Row Source under
Properties for the combo boxes did not seem to make a difference. Am I
doing it wrong?

pi********@hotmail.com wrote:

Tom wrote:

Hello,

I have a database of employee data in access and I am trying to create
a form with combo boxes for criteria (ex. gender, office, position,
etc.) that let the user select criteria from drop down menus (ex. they
may select Male, New York, Manager) and those criteria will be used to
run a query (ie Query by Form). I can do this with text boxes but when
I tried to use combo boxes no records were returned in the query. Any
idea what I am doing wrong?

Sounds like either you have a multi-column combobox and you're not
filtering on the column you think you are. Although how that would
happen in a true filter by form, I'm not sure.

Also, is there an easy way to remove duplicates from the combo boxes?

Change the SQL.

SELECT Position
FROM tblEmployees...

to

SELECT DISTINCT Position....

[fredg]

On 29 Jun 2006 12:21:03 -0700, Tom wrote:

Adding the word "DISTINCT" to the SQL in the Row Source under
Properties for the combo boxes did not seem to make a difference. Am I
doing it wrong?

pi********@hotmail.com wrote:

Tom wrote:

Hello,

I have a database of employee data in access and I am trying to create
a form with combo boxes for criteria (ex. gender, office, position,
etc.) that let the user select criteria from drop down menus (ex. they
may select Male, New York, Manager) and those criteria will be used to
run a query (ie Query by Form). I can do this with text boxes but when
I tried to use combo boxes no records were returned in the query. Any
idea what I am doing wrong?

Sounds like either you have a multi-column combobox and you're not
filtering on the column you think you are. Although how that would
happen in a true filter by form, I'm not sure.

Also, is there an easy way to remove duplicates from the combo boxes?

Change the SQL.

SELECT Position
FROM tblEmployees...

to

SELECT DISTINCT Position....

How can anyone tell? You haven't posted your actual SQL.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Tom]

In the design view of the recruiting survey for the gender control
combo box, my SQL code is:

SELECT DISTINCT RecruitingSurvey.ID, RecruitingSurvey.Gender FROM
RecruitingSurvey ORDER BY [Gender];

The code for the rest of the drop downs is parallel in structure...

fredg wrote:

On 29 Jun 2006 12:21:03 -0700, Tom wrote:

Adding the word "DISTINCT" to the SQL in the Row Source under
Properties for the combo boxes did not seem to make a difference. Am I
doing it wrong?

pi********@hotmail.com wrote:

Tom wrote:
Hello,

I have a database of employee data in access and I am trying to create
a form with combo boxes for criteria (ex. gender, office, position,
etc.) that let the user select criteria from drop down menus (ex. they
may select Male, New York, Manager) and those criteria will be used to
run a query (ie Query by Form). I can do this with text boxes but when
I tried to use combo boxes no records were returned in the query. Any
idea what I am doing wrong?

Sounds like either you have a multi-column combobox and you're not
filtering on the column you think you are. Although how that would
happen in a true filter by form, I'm not sure.
Also, is there an easy way to remove duplicates from the combo boxes?

Change the SQL.

SELECT Position
FROM tblEmployees...

to

SELECT DISTINCT Position....

How can anyone tell? You haven't posted your actual SQL.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

[Bob Quintal]

"Tom" <Th*************@gmail.com> wrote in
news:11**********************@b68g2000cwa.googlegr oups.com:

In the design view of the recruiting survey for the gender
control combo box, my SQL code is:

SELECT DISTINCT RecruitingSurvey.ID, RecruitingSurvey.Gender
FROM RecruitingSurvey ORDER BY [Gender];

The code for the rest of the drop downs is parallel in
structure...

You do not want the recruitingSurvey.ID in the rowsource for
your ComboBox. This is why you are getting the duplicates.

SELECT DISTINCT RecruitingSurvey.Gender
FROM RecruitingSurvey ORDER BY [Gender];

Is all you want. I know the Combobox Lizard wants to put the Id
there. If it were a Wizard it would be smart enough to know that
it's inappropriate.

Q
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Tom]

Bob,

I tried using that code...but then none of the choices (ex. Male,
Female) are displayed in the combo boxes on the form, just blank
spaces/lines. Any ideas? Thanks again.

Tom
Bob Quintal wrote:

"Tom" <Th*************@gmail.com> wrote in
news:11**********************@b68g2000cwa.googlegr oups.com:

In the design view of the recruiting survey for the gender
control combo box, my SQL code is:

SELECT DISTINCT RecruitingSurvey.ID, RecruitingSurvey.Gender
FROM RecruitingSurvey ORDER BY [Gender];

The code for the rest of the drop downs is parallel in
structure...

You do not want the recruitingSurvey.ID in the rowsource for
your ComboBox. This is why you are getting the duplicates.

SELECT DISTINCT RecruitingSurvey.Gender
FROM RecruitingSurvey ORDER BY [Gender];

Is all you want. I know the Combobox Lizard wants to put the Id
there. If it were a Wizard it would be smart enough to know that
it's inappropriate.

Q
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Bob Quintal]

"Tom" <Th*************@gmail.com> wrote in
news:11**********************@h44g2000cwa.googlegr oups.com:

Bob,

I tried using that code...but then none of the choices (ex. Male, Female) are displayed in the combo boxes on the form, just blank spaces/lines. Any ideas? Thanks again.

Tom
Forget the Lizard, turn it off.

Set the following properties for the gender combobox as follows.
From the fromat tab
Column Count: 1
Column heads: No
List Rows: 2
Column Widths: 1";

from the Data tab.
Row source type: table/query
Row Source: "SELECT DISTINCT [Gender] from RecruitingSurvey
ORDER BY [Gender];"
Bound column: 1

the other properties: read the help, set as desired.

set the other Combobozes similarly.

Bob Quintal wrote:

"Tom" <Th*************@gmail.com> wrote in
news:11**********************@b68g2000cwa.googlegr oups.com:

> In the design view of the recruiting survey for the gender
> control combo box, my SQL code is:
>
> SELECT DISTINCT RecruitingSurvey.ID, RecruitingSurvey.Gender > FROM RecruitingSurvey ORDER BY [Gender];
>
> The code for the rest of the drop downs is parallel in
> structure...

You do not want the recruitingSurvey.ID in the rowsource for
your ComboBox. This is why you are getting the duplicates.

SELECT DISTINCT RecruitingSurvey.Gender
FROM RecruitingSurvey ORDER BY [Gender];

Is all you want. I know the Combobox Lizard wants to put the Id there. If it were a Wizard it would be smart enough to know that it's inappropriate.

Q
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Tom]

I am now running a chain of 6 queries on queries from a form.
Everything works welland the results are all displayed in the final
query window. My question is whether there is a way to run the first 5
queries without having the query windows pop open. Right now I set the
first 5 queries to run and then close after the next query has used the
results of the prior query, however, the user will then still see the
first 5 windows flash open then close. I would prefer to simply have
only the results window for the 6th query pop open. Please let me know
if there is some setting that would make this possible.

Thanks,

Tom

Bob Quintal wrote:

"Tom" <Th*************@gmail.comwrote in
news:11**********************@h44g2000cwa.googlegr oups.com:

Bob,

I tried using that code...but then none of the choices (ex.

Male,

Female) are displayed in the combo boxes on the form, just

blank

spaces/lines. Any ideas? Thanks again.

Tom

Forget the Lizard, turn it off.

Set the following properties for the gender combobox as follows.
From the fromat tab
Column Count: 1
Column heads: No
List Rows: 2
Column Widths: 1";

from the Data tab.
Row source type: table/query
Row Source: "SELECT DISTINCT [Gender] from RecruitingSurvey
ORDER BY [Gender];"
Bound column: 1

the other properties: read the help, set as desired.

set the other Combobozes similarly.

Bob Quintal wrote:

"Tom" <Th*************@gmail.comwrote in
news:11**********************@b68g2000cwa.googlegr oups.com:

In the design view of the recruiting survey for the gender
control combo box, my SQL code is:

SELECT DISTINCT RecruitingSurvey.ID,

RecruitingSurvey.Gender

FROM RecruitingSurvey ORDER BY [Gender];

The code for the rest of the drop downs is parallel in
structure...

You do not want the recruitingSurvey.ID in the rowsource for
your ComboBox. This is why you are getting the duplicates.

SELECT DISTINCT RecruitingSurvey.Gender
FROM RecruitingSurvey ORDER BY [Gender];

Is all you want. I know the Combobox Lizard wants to put the

Id

there. If it were a Wizard it would be smart enough to know

that

it's inappropriate.

Q
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Bob Quintal]

"Tom" <Th*************@gmail.comwrote in
news:11**********************@m73g2000cwd.googlegr oups.com:

I am now running a chain of 6 queries on queries from a form.
Everything works welland the results are all displayed in the
final query window. My question is whether there is a way to
run the first 5 queries without having the query windows pop
open. Right now I set the first 5 queries to run and then
close after the next query has used the results of the prior
query, however, the user will then still see the first 5
windows flash open then close. I would prefer to simply have
only the results window for the 6th query pop open. Please
let me know if there is some setting that would make this
possible.

Thanks,

Tom

Sounds like you are trying to do do something extra. If the 6th
query calls the other five, it should automatically execute
those queries without opening any wndows. If you are also
manually executing those queries, you are doing it for nothing.


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Tom]

I am now trying to query a data table based on values the user selects
in a series of checkboxes on a form. Essentially, there are roughly 20
checkboxes on a form, all of which I want to be "yes" if they are
checked. In the corresponding data table, there is a column that
corresponds to each checkbox (roughly 20 columns). Each column is full
of "Yes" and "No" values. My question is how do you set up the
criteria so that if the user checks a specific combination of
checkboxes on the form then when you run the query only the records
with "Yes" in those columns will show up, while if they do not check
them then all values will show up?


Bob Quintal wrote:

"Tom" <Th*************@gmail.comwrote in
news:11**********************@m73g2000cwd.googlegr oups.com:

I am now running a chain of 6 queries on queries from a form.
Everything works welland the results are all displayed in the
final query window. My question is whether there is a way to
run the first 5 queries without having the query windows pop
open. Right now I set the first 5 queries to run and then
close after the next query has used the results of the prior
query, however, the user will then still see the first 5
windows flash open then close. I would prefer to simply have
only the results window for the 6th query pop open. Please
let me know if there is some setting that would make this
possible.

Thanks,

Tom

Sounds like you are trying to do do something extra. If the 6th
query calls the other five, it should automatically execute
those queries without opening any wndows. If you are also
manually executing those queries, you are doing it for nothing.


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Bob Quintal]

"Tom" <Th*************@gmail.comwrote in
news:11**********************@m79g2000cwm.googlegr oups.com:

I am now trying to query a data table based on values the user
selects in a series of checkboxes on a form. Essentially,
there are roughly 20 checkboxes on a form, all of which I want
to be "yes" if they are checked. In the corresponding data
table, there is a column that corresponds to each checkbox
(roughly 20 columns). Each column is full of "Yes" and "No"
values. My question is how do you set up the criteria so that
if the user checks a specific combination of checkboxes on the
form then when you run the query only the records with "Yes"
in those columns will show up, while if they do not check them
then all values will show up?

You'll want to do this in the OnClickEvent code for the command
button that runs the query..

Let's generate the code, then we'll talk about querying the
result.

Dim stCriteria as String
If me.chk01 = true then
stCriteria = stCriteria & "[chk01] = true AND "
end if
If me.chk02 = true then
stCriteria = stCriteria & "[chk02] = true AND "
end if
If me.chk03 = true then
stCriteria = stCriteria & "[chk03] = true AND "
end if
' add as many as you need.

'Now strip off the final AND if there is one
If len(stCriteria 0 then
stCriteria = left(stCriteria,len(stCriteria)-5)
end if

' We now have a valid whereclause, without the 'Where'
If you are opening a form or report, use it as the 'where'
parameter of the docmd.openform method.

If you are opening the query to the user, please create a
continuous form and set it's viewmode to datasheet, and use the
..openform method.

If you are running the query as part of a .transfertext method.
you will need to have the code create/modify the query and save
it before running the .transfertext.

>

Bob Quintal wrote:

>"Tom" <Th*************@gmail.comwrote in
news:11**********************@m73g2000cwd.googleg roups.com:

I am now running a chain of 6 queries on queries from a
form. Everything works welland the results are all
displayed in the final query window. My question is
whether there is a way to run the first 5 queries without
having the query windows pop open. Right now I set the
first 5 queries to run and then close after the next query
has used the results of the prior query, however, the user
will then still see the first 5 windows flash open then
close. I would prefer to simply have only the results
window for the 6th query pop open. Please let me know if
there is some setting that would make this possible.

Thanks,

Tom

Sounds like you are trying to do do something extra. If the
6th query calls the other five, it should automatically
execute those queries without opening any wndows. If you are
also manually executing those queries, you are doing it for
nothing.


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com