Help with dropdown box for query criteria

[Paul B]

Very newbie here, only started using Access 2 days ago,

I have managed to make a very simple table to use for testing, made a query
and report, even managed to make a combo box on a form and used it for the
query criteria and got it all to work, almost all, the table I made just has
some names in a column and numbers in the next column 1, 2, 3, the combo box
shows all the numbers like 3 1's 4 2's 3 3's if I pick a 1, 2, or 3 from
the top of the list everything works fine, but if I pick one of the numbers
after the first 3 I get a query or report with no data.

So now the questions,

How do I fix this?

Is there a way to only show unique valves in the combo box?

Or am I going about this the wrong way?

This seams like a lot of work just to get a drop down box for a query
criteria, is there an easier way?

Also any links to a file like this to download so I could take a look at it
would be appreciated.

Almost forgot, is there a way to have a "show all" in the combo box?

Thanks
Using Access 2002

 

[Douglas J. Steele]

Create a query that uses SELECT DISTINCT rather than SELECT, and use that as
the Row Source for your combo box.

You can either go into the SQL view of the query (when the query's open,
look under the View menu and select SQL view), or you can look at the
Properties of the query: you should have choices for "UniqueValues" and
"UniqueRecords": you want UniqueValues (make sure you're looking at the
properties for the query itself, not for one of the fields in the query).
Just make sure that you've only got the necessary fields in the query so
that UniqueValues makes sense.

 

[Paul B]

Douglas, changed select to select distinct, I get the same thing, here is
the code that I am seeing

SELECT DISTINCT Table1.ID, Table1.Name, Table1.Number
FROM Table1
WHERE (((Table1.Number)=[Forms]![Table1form]![MyComboBox]));

how do I see the properties for the query, looks like I am seeing only the
field properties, I don't see an option for unique

Thanks for your help, but as I said I am very new to Access so if you could
break it down a little more would be great

--

 

[Douglas J. Steele]

First, I was attempting to address your complaint that "the combo box shows
all the numbers like 3 1's 4 2's 3 3's". The SQL you've posted is likely
your "real" query, not the row source for your combo box: I wasn't
suggesting using DISTINCT for that query.

Presumably you've got different values of ID and Name corresponding to the
same values of Number. That's what I was referring to when I said "Just make
sure that you've only got the necessary fields in the query so that
UniqueValues makes sense". The query you're using for the Row Source of the
combo box likely needs to be "SELECT DISTINCT Table1.[Number] FROM Table1
ORDER BY Table1.[Number]", unless you've got a second table that only holds
one row for each unique value of Number. (Incidentally, I'd recommend very
strongly not using Name and Number as field names: those are reserved words,
and should never be used for your own purposes. For more details about what
words are "bad", see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html)

Since you've gone in to the SQL and made your changes, there's no reason to
worry about the properties of the query: it's simpy another way of
accomplishing the same thing. For the record, though, to get at the
Properties window for the query itself, click in the upper part of the
graphic interface (where the tables are) to get the properties of the query.
(It should say "Query Properties" as the caption for the window)

To add an "all" selection to your combo box, see
http://www.mvps.org/access/forms/frm0043.htm at "The Access Web". Note that
your underlying query would then have to be changed to something like:

SELECT DISTINCT Table1.ID, Table1.Name, Table1.Number
FROM Table1
WHERE (((Table1.Number)=[Forms]![Table1form]![MyComboBox]))
OR [Forms]![Table1form]![MyComboBox] = "All"

I don't see any reason why your query shouldn't work with "higher" entries
in the combo box.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas, changed select to select distinct, I get the same thing, here is
the code that I am seeing

SELECT DISTINCT Table1.ID, Table1.Name, Table1.Number
FROM Table1
WHERE (((Table1.Number)=[Forms]![Table1form]![MyComboBox]));

how do I see the properties for the query, looks like I am seeing only the
field properties, I don't see an option for unique

Thanks for your help, but as I said I am very new to Access so if you
could
break it down a little more would be great

--

Create a query that uses SELECT DISTINCT rather than SELECT, and use that as
the Row Source for your combo box.

You can either go into the SQL view of the query (when the query's open,
look under the View menu and select SQL view), or you can look at the
Properties of the query: you should have choices for "UniqueValues" and
"UniqueRecords": you want UniqueValues (make sure you're looking at the
properties for the query itself, not for one of the fields in the query).
Just make sure that you've only got the necessary fields in the query so
that UniqueValues makes sense.

 

[Paul B]

Douglas, I will have a look at that, but after some more testing it looks
like it does not work after all, the query is picking up what position the
item is in, in the combo box not what it is, so if I pick the first item in
the box the query is looking for a 1, if I pick the next item down it is
looking for a 2.
So if the items in the box are cat, dog, horse if I pick dog the criteria
looks for 2.????

Maybe I just need DETAIL instructions on how to use a drop down box for a
query criteria

off to look at the links you gave

Thanks



--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

First, I was attempting to address your complaint that "the combo box shows
all the numbers like 3 1's 4 2's 3 3's". The SQL you've posted is likely
your "real" query, not the row source for your combo box: I wasn't
suggesting using DISTINCT for that query.

Presumably you've got different values of ID and Name corresponding to the
same values of Number. That's what I was referring to when I said "Just make
sure that you've only got the necessary fields in the query so that
UniqueValues makes sense". The query you're using for the Row Source of the
combo box likely needs to be "SELECT DISTINCT Table1.[Number] FROM Table1
ORDER BY Table1.[Number]", unless you've got a second table that only holds
one row for each unique value of Number. (Incidentally, I'd recommend very
strongly not using Name and Number as field names: those are reserved words,
and should never be used for your own purposes. For more details about what
words are "bad", see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html)

Since you've gone in to the SQL and made your changes, there's no reason to
worry about the properties of the query: it's simpy another way of
accomplishing the same thing. For the record, though, to get at the
Properties window for the query itself, click in the upper part of the
graphic interface (where the tables are) to get the properties of the query.
(It should say "Query Properties" as the caption for the window)

To add an "all" selection to your combo box, see
http://www.mvps.org/access/forms/frm0043.htm at "The Access Web". Note that
your underlying query would then have to be changed to something like:

SELECT DISTINCT Table1.ID, Table1.Name, Table1.Number
FROM Table1
WHERE (((Table1.Number)=[Forms]![Table1form]![MyComboBox]))
OR [Forms]![Table1form]![MyComboBox] = "All"

I don't see any reason why your query shouldn't work with "higher" entries
in the combo box.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas, changed select to select distinct, I get the same thing, here is
the code that I am seeing

SELECT DISTINCT Table1.ID, Table1.Name, Table1.Number
FROM Table1
WHERE (((Table1.Number)=[Forms]![Table1form]![MyComboBox]));

how do I see the properties for the query, looks like I am seeing only the
field properties, I don't see an option for unique

Thanks for your help, but as I said I am very new to Access so if you
could
break it down a little more would be great

--

Create a query that uses SELECT DISTINCT rather than SELECT, and use

that
as

the Row Source for your combo box.

You can either go into the SQL view of the query (when the query's open,
look under the View menu and select SQL view), or you can look at the
Properties of the query: you should have choices for "UniqueValues" and
"UniqueRecords": you want UniqueValues (make sure you're looking at the
properties for the query itself, not for one of the fields in the query).
Just make sure that you've only got the necessary fields in the query so
that UniqueValues makes sense.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Very newbie here, only started using Access 2 days ago,

I have managed to make a very simple table to use for testing, made a
query
and report, even managed to make a combo box on a form and used it

for
the

query criteria and got it all to work, almost all, the table I made
just
has
some names in a column and numbers in the next column 1, 2, 3, the
combo
box
shows all the numbers like 3 1's 4 2's 3 3's if I pick a 1, 2, or 3 from
the top of the list everything works fine, but if I pick one of the
numbers
after the first 3 I get a query or report with no data.

So now the questions,

How do I fix this?

Is there a way to only show unique valves in the combo box?

Or am I going about this the wrong way?

This seams like a lot of work just to get a drop down box for a query
criteria, is there an easier way?

Also any links to a file like this to download so I could take a look
at
it
would be appreciated.

Almost forgot, is there a way to have a "show all" in the combo box?

Thanks
Using Access 2002

 

[Paul B]

Ok looked at the links, had 2 BAD names changed them

"Row Source of the combo box likely needs to be "SELECT DISTINCT
Table1.[Number] FROM Table1 ORDER BY Table1.[Number]" "
when I put that in, changed Number to MyData, I got nothing in the combo box

this is what I have in the row source

SELECT Table1.ID, Table1.[MyData] FROM Table1;

Think I need to get this working before I try the add an "all" selection to
your combo box

Looking like I might just give up on Access and go back to using Excel !

Douglas, I will have a look at that, but after some more testing it looks
like it does not work after all, the query is picking up what position the
item is in, in the combo box not what it is, so if I pick the first item in
the box the query is looking for a 1, if I pick the next item down it is
looking for a 2.
So if the items in the box are cat, dog, horse if I pick dog the criteria
looks for 2.????

Maybe I just need DETAIL instructions on how to use a drop down box for a
query criteria

off to look at the links you gave

Thanks



--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

First, I was attempting to address your complaint that "the combo box shows
all the numbers like 3 1's 4 2's 3 3's". The SQL you've posted is likely
your "real" query, not the row source for your combo box: I wasn't
suggesting using DISTINCT for that query.

Presumably you've got different values of ID and Name corresponding to the
same values of Number. That's what I was referring to when I said "Just make
sure that you've only got the necessary fields in the query so that
UniqueValues makes sense". The query you're using for the Row Source of the
combo box likely needs to be "SELECT DISTINCT Table1.[Number] FROM Table1
ORDER BY Table1.[Number]", unless you've got a second table that only holds
one row for each unique value of Number. (Incidentally, I'd recommend very
strongly not using Name and Number as field names: those are reserved words,
and should never be used for your own purposes. For more details about what
words are "bad", see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html)

Since you've gone in to the SQL and made your changes, there's no reason to
worry about the properties of the query: it's simpy another way of
accomplishing the same thing. For the record, though, to get at the
Properties window for the query itself, click in the upper part of the
graphic interface (where the tables are) to get the properties of the query.
(It should say "Query Properties" as the caption for the window)

To add an "all" selection to your combo box, see
http://www.mvps.org/access/forms/frm0043.htm at "The Access Web". Note that
your underlying query would then have to be changed to something like:

SELECT DISTINCT Table1.ID, Table1.Name, Table1.Number
FROM Table1
WHERE (((Table1.Number)=[Forms]![Table1form]![MyComboBox]))
OR [Forms]![Table1form]![MyComboBox] = "All"

I don't see any reason why your query shouldn't work with "higher" entries
in the combo box.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Douglas, changed select to select distinct, I get the same thing, here is
the code that I am seeing

SELECT DISTINCT Table1.ID, Table1.Name, Table1.Number
FROM Table1
WHERE (((Table1.Number)=[Forms]![Table1form]![MyComboBox]));

how do I see the properties for the query, looks like I am seeing only the
field properties, I don't see an option for unique

Thanks for your help, but as I said I am very new to Access so if you
could
break it down a little more would be great

--

Create a query that uses SELECT DISTINCT rather than SELECT, and use that
as
the Row Source for your combo box.

You can either go into the SQL view of the query (when the query's open,
look under the View menu and select SQL view), or you can look at the
Properties of the query: you should have choices for "UniqueValues" and
"UniqueRecords": you want UniqueValues (make sure you're looking at the
properties for the query itself, not for one of the fields in the query).
Just make sure that you've only got the necessary fields in the query so
that UniqueValues makes sense.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Very newbie here, only started using Access 2 days ago,

I have managed to make a very simple table to use for testing, made a
query
and report, even managed to make a combo box on a form and used it for
the
query criteria and got it all to work, almost all, the table I made
just
has
some names in a column and numbers in the next column 1, 2, 3, the
combo
box
shows all the numbers like 3 1's 4 2's 3 3's if I pick a 1, 2, or 3
from
the top of the list everything works fine, but if I pick one of the
numbers
after the first 3 I get a query or report with no data.

So now the questions,

How do I fix this?

Is there a way to only show unique valves in the combo box?

Or am I going about this the wrong way?

This seams like a lot of work just to get a drop down box for a query
criteria, is there an easier way?

Also any links to a file like this to download so I could take a look
at
it
would be appreciated.

Almost forgot, is there a way to have a "show all" in the combo box?

Thanks
Using Access 2002

 

[Douglas J. Steele]

How did you create the combo box in the first place? If you used the wizard,
it probably made certain assumptions for you, and set values accordingly.

Look at the properties of the combo box. What's the ColumnCount set to? With
that Row Source, you need it set to 1. What's the BoundColumn set to? Again,
since you've only got 1 column, it needs to be set to 1.

I'm guessing that in your previous combo box, the BoundColumn property
(which is what referring to the control will return) was set to 1, whereas
your Number field was the 3rd column in the control.

To double-check what the control's returning, go to the Immediate Windows
(Ctrl-G), type the following in the box and hit Enter:

?[Forms]![Table1form]![MyComboBox]

(Make sure the form is open, with something selected in the combo box when
you do this)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ok looked at the links, had 2 BAD names changed them

"Row Source of the combo box likely needs to be "SELECT DISTINCT
Table1.[Number] FROM Table1 ORDER BY Table1.[Number]" "
when I put that in, changed Number to MyData, I got nothing in the combo
box

this is what I have in the row source

SELECT Table1.ID, Table1.[MyData] FROM Table1;

Think I need to get this working before I try the add an "all" selection
to
your combo box

Looking like I might just give up on Access and go back to using Excel !

Douglas, I will have a look at that, but after some more testing it looks
like it does not work after all, the query is picking up what position
the
item is in, in the combo box not what it is, so if I pick the first item in
the box the query is looking for a 1, if I pick the next item down it is
looking for a 2.
So if the items in the box are cat, dog, horse if I pick dog the criteria
looks for 2.????

Maybe I just need DETAIL instructions on how to use a drop down box for a
query criteria

off to look at the links you gave

Thanks



--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

First, I was attempting to address your complaint that "the combo box shows
all the numbers like 3 1's 4 2's 3 3's". The SQL you've posted is likely
your "real" query, not the row source for your combo box: I wasn't
suggesting using DISTINCT for that query.

Presumably you've got different values of ID and Name corresponding to the
same values of Number. That's what I was referring to when I said "Just make
sure that you've only got the necessary fields in the query so that
UniqueValues makes sense". The query you're using for the Row Source of the
combo box likely needs to be "SELECT DISTINCT Table1.[Number] FROM Table1
ORDER BY Table1.[Number]", unless you've got a second table that only holds
one row for each unique value of Number. (Incidentally, I'd recommend very
strongly not using Name and Number as field names: those are reserved words,
and should never be used for your own purposes. For more details about what
words are "bad", see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html)

Since you've gone in to the SQL and made your changes, there's no
reason to
worry about the properties of the query: it's simpy another way of
accomplishing the same thing. For the record, though, to get at the
Properties window for the query itself, click in the upper part of the
graphic interface (where the tables are) to get the properties of the query.
(It should say "Query Properties" as the caption for the window)

To add an "all" selection to your combo box, see
http://www.mvps.org/access/forms/frm0043.htm at "The Access Web". Note that
your underlying query would then have to be changed to something like:

SELECT DISTINCT Table1.ID, Table1.Name, Table1.Number
FROM Table1
WHERE (((Table1.Number)=[Forms]![Table1form]![MyComboBox]))
OR [Forms]![Table1form]![MyComboBox] = "All"

I don't see any reason why your query shouldn't work with "higher" entries
in the combo box.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas, changed select to select distinct, I get the same thing,
here is
the code that I am seeing

SELECT DISTINCT Table1.ID, Table1.Name, Table1.Number
FROM Table1
WHERE (((Table1.Number)=[Forms]![Table1form]![MyComboBox]));

how do I see the properties for the query, looks like I am seeing
only the
field properties, I don't see an option for unique

Thanks for your help, but as I said I am very new to Access so if you
could
break it down a little more would be great

--

Create a query that uses SELECT DISTINCT rather than SELECT, and use that
as
the Row Source for your combo box.

You can either go into the SQL view of the query (when the query's open,
look under the View menu and select SQL view), or you can look at
the
Properties of the query: you should have choices for "UniqueValues" and
"UniqueRecords": you want UniqueValues (make sure you're looking at the
properties for the query itself, not for one of the fields in the query).
Just make sure that you've only got the necessary fields in the
query so
that UniqueValues makes sense.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Very newbie here, only started using Access 2 days ago,

I have managed to make a very simple table to use for testing,
made a
query
and report, even managed to make a combo box on a form and used it for
the
query criteria and got it all to work, almost all, the table I
made
just
has
some names in a column and numbers in the next column 1, 2, 3, the
combo
box
shows all the numbers like 3 1's 4 2's 3 3's if I pick a 1, 2,
or 3
from
the top of the list everything works fine, but if I pick one of
the
numbers
after the first 3 I get a query or report with no data.

So now the questions,

How do I fix this?

Is there a way to only show unique valves in the combo box?

Or am I going about this the wrong way?

This seams like a lot of work just to get a drop down box for a query
criteria, is there an easier way?

Also any links to a file like this to download so I could take a look
at
it
would be appreciated.

Almost forgot, is there a way to have a "show all" in the combo box?

Thanks
Using Access 2002

 

[Paul B]

Tried to set this up from some information I found on Google

This is what I was working from:

"Use this in the query as the criterion:
Forms!YourFormName!YourComboBoxName

Create a form with a button that runs your report and a combo box on it for
the PONumber, and set the criteria in the query to
Forms!FormName!ComboBoxName"


Answers to your questions:

What's the ColumnCount set to? 2

Set it to 1 and the combo box would only show a 1

What's the BoundColumn set to? 1

When I ran this ?[Forms]![Table1form]![MyComboBox] I got a 2

Here is a report on the query sheet, does that tell you anything?

Thanks


C:\My Documents\!test data.mdb Sunday, December 10, 2006
Query: Table1 Query Page: 1
Properties
DateCreated: 12/9/06 10:28:32 PM DefaultView: Datasheet
DOL: Long binary data GUID: {guid {373E2E0F-87D4-11DB-
9067-8AB3EC129C63}}
LastUpdated: 12/10/06 11:11:39 AM MaxRecords: 0
NameMap: Long binary data ODBCTimeout: 60
OrderByOn: False Orientation: Left-to-Right
RecordLocks: No Locks RecordsAffected: 0
RecordsetType: Dynaset ReturnsRecords: True
Type: 0 Updatable: True
SQL
SELECT Table1.FName, Table1.MyData
FROM Table1
WHERE (((Table1.MyData)=Forms!Table1form![MyComboBox]));
Query Parameters
Name Type
Forms!Table1form![MyCombo Text
Box]
Columns
Name Type Size
FName Text 50
AllowZeroLength: True
Attributes: Variable Length, Updatable
CollatingOrder: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: 2385
DataUpdatable: True
DisplayControl: Text Box
GUID: {guid {373E2E12-87D4-11DB-9067-8AB3EC129C63}}
IMEMode: 0
IMESentenceMode: 3
OrdinalPosition: 0
Required: False
SourceField: FName
SourceTable: Table1
UnicodeCompression: True
MyData Text 50
AllowZeroLength: True
Attributes: Variable Length, Updatable
CollatingOrder: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
DataUpdatable: True
DisplayControl: Text Box

C:\My Documents\!test data.mdb Sunday, December 10, 2006
Query: Table1 Query Page: 2
GUID: {guid {5C0F152C-8838-11DB-9067-444553540000}}
IMEMode: 0
IMESentenceMode: 3
OrdinalPosition: 1
Required: False
SourceField: MyData
SourceTable: Table1
UnicodeCompression: False
Table Indexes
Name Number of Fields
Number 1
Clustered: False
DistinctCount: 2
Foreign: False
IgnoreNulls: False
Name: Number
Primary: False
Required: False
Unique: False
Fields:
MyData Ascending
PrimaryKey 1
Clustered: False
DistinctCount: 12
Foreign: False
IgnoreNulls: False
Name: PrimaryKey
Primary: True
Required: True
Unique: True
Fields:
ID Ascending
User Permissions
admin Delete, Read Permissions, Set Permissions, Change Owner, Read
Definition,
Write Definition, Read Data, Insert Data, Update Data, Delete Data
Group Permissions
Admins Delete, Read Permissions, Set Permissions, Change Owner, Read
Definition,
Write Definition, Read Data, Insert Data, Update Data, Delete Data
Users Delete, Read Permissions, Set Permissions, Change Owner, Read
Definition,
Write Definition, Read Data, Insert Data, Update Data, Delete Data

How did you create the combo box in the first place? If you used the wizard,
it probably made certain assumptions for you, and set values accordingly.

Look at the properties of the combo box. What's the ColumnCount set to? With
that Row Source, you need it set to 1. What's the BoundColumn set to? Again,
since you've only got 1 column, it needs to be set to 1.

I'm guessing that in your previous combo box, the BoundColumn property
(which is what referring to the control will return) was set to 1, whereas
your Number field was the 3rd column in the control.

To double-check what the control's returning, go to the Immediate Windows
(Ctrl-G), type the following in the box and hit Enter:

?[Forms]![Table1form]![MyComboBox]

(Make sure the form is open, with something selected in the combo box when
you do this)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ok looked at the links, had 2 BAD names changed them

"Row Source of the combo box likely needs to be "SELECT DISTINCT
Table1.[Number] FROM Table1 ORDER BY Table1.[Number]" "
when I put that in, changed Number to MyData, I got nothing in the combo
box

this is what I have in the row source

SELECT Table1.ID, Table1.[MyData] FROM Table1;

Think I need to get this working before I try the add an "all" selection
to
your combo box

Looking like I might just give up on Access and go back to using Excel !

Douglas, I will have a look at that, but after some more testing it looks
like it does not work after all, the query is picking up what position
the
item is in, in the combo box not what it is, so if I pick the first

item
in

the box the query is looking for a 1, if I pick the next item down it is
looking for a 2.
So if the items in the box are cat, dog, horse if I pick dog the criteria
looks for 2.????

Maybe I just need DETAIL instructions on how to use a drop down box for a
query criteria

off to look at the links you gave

Thanks



--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

First, I was attempting to address your complaint that "the combo box
shows
all the numbers like 3 1's 4 2's 3 3's". The SQL you've posted is likely
your "real" query, not the row source for your combo box: I wasn't
suggesting using DISTINCT for that query.

Presumably you've got different values of ID and Name corresponding

to
the

same values of Number. That's what I was referring to when I said "Just
make
sure that you've only got the necessary fields in the query so that
UniqueValues makes sense". The query you're using for the Row Source of
the
combo box likely needs to be "SELECT DISTINCT Table1.[Number] FROM Table1
ORDER BY Table1.[Number]", unless you've got a second table that only
holds
one row for each unique value of Number. (Incidentally, I'd recommend very
strongly not using Name and Number as field names: those are reserved
words,
and should never be used for your own purposes. For more details about
what
words are "bad", see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html)

Since you've gone in to the SQL and made your changes, there's no
reason
to
worry about the properties of the query: it's simpy another way of
accomplishing the same thing. For the record, though, to get at the
Properties window for the query itself, click in the upper part of the
graphic interface (where the tables are) to get the properties of the
query.
(It should say "Query Properties" as the caption for the window)

To add an "all" selection to your combo box, see
http://www.mvps.org/access/forms/frm0043.htm at "The Access Web". Note
that
your underlying query would then have to be changed to something like:

SELECT DISTINCT Table1.ID, Table1.Name, Table1.Number
FROM Table1
WHERE (((Table1.Number)=[Forms]![Table1form]![MyComboBox]))
OR [Forms]![Table1form]![MyComboBox] = "All"

I don't see any reason why your query shouldn't work with "higher" entries
in the combo box.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas, changed select to select distinct, I get the same thing,
here
is
the code that I am seeing

SELECT DISTINCT Table1.ID, Table1.Name, Table1.Number
FROM Table1
WHERE (((Table1.Number)=[Forms]![Table1form]![MyComboBox]));

how do I see the properties for the query, looks like I am seeing
only
the
field properties, I don't see an option for unique

Thanks for your help, but as I said I am very new to Access so if you
could
break it down a little more would be great

--

Create a query that uses SELECT DISTINCT rather than SELECT, and use
that
as
the Row Source for your combo box.

You can either go into the SQL view of the query (when the query's
open,
look under the View menu and select SQL view), or you can look at
the
Properties of the query: you should have choices for

"UniqueValues"
and

"UniqueRecords": you want UniqueValues (make sure you're looking

at
the

properties for the query itself, not for one of the fields in the
query).
Just make sure that you've only got the necessary fields in the
query
so
that UniqueValues makes sense.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Very newbie here, only started using Access 2 days ago,

I have managed to make a very simple table to use for testing,
made a
query
and report, even managed to make a combo box on a form and used it
for
the
query criteria and got it all to work, almost all, the table I
made
just
has
some names in a column and numbers in the next column 1, 2, 3, the
combo
box
shows all the numbers like 3 1's 4 2's 3 3's if I pick a 1, 2,
or 3
from
the top of the list everything works fine, but if I pick one of
the
numbers
after the first 3 I get a query or report with no data.

So now the questions,

How do I fix this?

Is there a way to only show unique valves in the combo box?

Or am I going about this the wrong way?

This seams like a lot of work just to get a drop down box for a query
criteria, is there an easier way?

Also any links to a file like this to download so I could take a look
at
it
would be appreciated.

Almost forgot, is there a way to have a "show all" in the combo box?

Thanks
Using Access 2002

 

[Paul B]

Ok, think I got it, had to set up a new table that only had the criteria in
it, not sure if that is the way to do it but it works

Thanks

Tried to set this up from some information I found on Google

This is what I was working from:

"Use this in the query as the criterion:
Forms!YourFormName!YourComboBoxName

Create a form with a button that runs your report and a combo box on it
for
the PONumber, and set the criteria in the query to
Forms!FormName!ComboBoxName"


Answers to your questions:

What's the ColumnCount set to? 2

Set it to 1 and the combo box would only show a 1

What's the BoundColumn set to? 1

When I ran this ?[Forms]![Table1form]![MyComboBox] I got a 2

Here is a report on the query sheet, does that tell you anything?

Thanks


C:\My Documents\!test data.mdb Sunday, December 10, 2006
Query: Table1 Query Page: 1
Properties
DateCreated: 12/9/06 10:28:32 PM DefaultView: Datasheet
DOL: Long binary data GUID: {guid {373E2E0F-87D4-11DB-
9067-8AB3EC129C63}}
LastUpdated: 12/10/06 11:11:39 AM MaxRecords: 0
NameMap: Long binary data ODBCTimeout: 60
OrderByOn: False Orientation: Left-to-Right
RecordLocks: No Locks RecordsAffected: 0
RecordsetType: Dynaset ReturnsRecords: True
Type: 0 Updatable: True
SQL
SELECT Table1.FName, Table1.MyData
FROM Table1
WHERE (((Table1.MyData)=Forms!Table1form![MyComboBox]));
Query Parameters
Name Type
Forms!Table1form![MyCombo Text
Box]
Columns
Name Type Size
FName Text 50
AllowZeroLength: True
Attributes: Variable Length, Updatable
CollatingOrder: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: 2385
DataUpdatable: True
DisplayControl: Text Box
GUID: {guid {373E2E12-87D4-11DB-9067-8AB3EC129C63}}
IMEMode: 0
IMESentenceMode: 3
OrdinalPosition: 0
Required: False
SourceField: FName
SourceTable: Table1
UnicodeCompression: True
MyData Text 50
AllowZeroLength: True
Attributes: Variable Length, Updatable
CollatingOrder: General
ColumnHidden: False
ColumnOrder: Default
ColumnWidth: Default
DataUpdatable: True
DisplayControl: Text Box

C:\My Documents\!test data.mdb Sunday, December 10, 2006
Query: Table1 Query Page: 2
GUID: {guid {5C0F152C-8838-11DB-9067-444553540000}}
IMEMode: 0
IMESentenceMode: 3
OrdinalPosition: 1
Required: False
SourceField: MyData
SourceTable: Table1
UnicodeCompression: False
Table Indexes
Name Number of Fields
Number 1
Clustered: False
DistinctCount: 2
Foreign: False
IgnoreNulls: False
Name: Number
Primary: False
Required: False
Unique: False
Fields:
MyData Ascending
PrimaryKey 1
Clustered: False
DistinctCount: 12
Foreign: False
IgnoreNulls: False
Name: PrimaryKey
Primary: True
Required: True
Unique: True
Fields:
ID Ascending
User Permissions
admin Delete, Read Permissions, Set Permissions, Change Owner, Read
Definition,
Write Definition, Read Data, Insert Data, Update Data, Delete Data
Group Permissions
Admins Delete, Read Permissions, Set Permissions, Change Owner, Read
Definition,
Write Definition, Read Data, Insert Data, Update Data, Delete Data
Users Delete, Read Permissions, Set Permissions, Change Owner, Read
Definition,
Write Definition, Read Data, Insert Data, Update Data, Delete Data

How did you create the combo box in the first place? If you used the wizard,
it probably made certain assumptions for you, and set values accordingly.

Look at the properties of the combo box. What's the ColumnCount set to? With
that Row Source, you need it set to 1. What's the BoundColumn set to? Again,
since you've only got 1 column, it needs to be set to 1.

I'm guessing that in your previous combo box, the BoundColumn property
(which is what referring to the control will return) was set to 1,
whereas
your Number field was the 3rd column in the control.

To double-check what the control's returning, go to the Immediate Windows
(Ctrl-G), type the following in the box and hit Enter:

?[Forms]![Table1form]![MyComboBox]

(Make sure the form is open, with something selected in the combo box
when
you do this)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Ok looked at the links, had 2 BAD names changed them

"Row Source of the combo box likely needs to be "SELECT DISTINCT
Table1.[Number] FROM Table1 ORDER BY Table1.[Number]" "
when I put that in, changed Number to MyData, I got nothing in the
combo
box

this is what I have in the row source

SELECT Table1.ID, Table1.[MyData] FROM Table1;

Think I need to get this working before I try the add an "all"
selection
to
your combo box

Looking like I might just give up on Access and go back to using Excel
!


Douglas, I will have a look at that, but after some more testing it looks
like it does not work after all, the query is picking up what position
the
item is in, in the combo box not what it is, so if I pick the first item
in
the box the query is looking for a 1, if I pick the next item down it is
looking for a 2.
So if the items in the box are cat, dog, horse if I pick dog the criteria
looks for 2.????

Maybe I just need DETAIL instructions on how to use a drop down box
for a
query criteria

off to look at the links you gave

Thanks



--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

First, I was attempting to address your complaint that "the combo
box
shows
all the numbers like 3 1's 4 2's 3 3's". The SQL you've posted is
likely
your "real" query, not the row source for your combo box: I wasn't
suggesting using DISTINCT for that query.

Presumably you've got different values of ID and Name corresponding to
the
same values of Number. That's what I was referring to when I said "Just
make
sure that you've only got the necessary fields in the query so that
UniqueValues makes sense". The query you're using for the Row Source of
the
combo box likely needs to be "SELECT DISTINCT Table1.[Number] FROM
Table1
ORDER BY Table1.[Number]", unless you've got a second table that
only
holds
one row for each unique value of Number. (Incidentally, I'd
recommend
very
strongly not using Name and Number as field names: those are
reserved
words,
and should never be used for your own purposes. For more details about
what
words are "bad", see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html)

Since you've gone in to the SQL and made your changes, there's no
reason
to
worry about the properties of the query: it's simpy another way of
accomplishing the same thing. For the record, though, to get at the
Properties window for the query itself, click in the upper part of the
graphic interface (where the tables are) to get the properties of
the
query.
(It should say "Query Properties" as the caption for the window)

To add an "all" selection to your combo box, see
http://www.mvps.org/access/forms/frm0043.htm at "The Access Web". Note
that
your underlying query would then have to be changed to something like:

SELECT DISTINCT Table1.ID, Table1.Name, Table1.Number
FROM Table1
WHERE (((Table1.Number)=[Forms]![Table1form]![MyComboBox]))
OR [Forms]![Table1form]![MyComboBox] = "All"

I don't see any reason why your query shouldn't work with "higher"
entries
in the combo box.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Douglas, changed select to select distinct, I get the same thing,
here
is
the code that I am seeing

SELECT DISTINCT Table1.ID, Table1.Name, Table1.Number
FROM Table1
WHERE (((Table1.Number)=[Forms]![Table1form]![MyComboBox]));

how do I see the properties for the query, looks like I am seeing
only
the
field properties, I don't see an option for unique

Thanks for your help, but as I said I am very new to Access so if you
could
break it down a little more would be great

--

message
Create a query that uses SELECT DISTINCT rather than SELECT, and use
that
as
the Row Source for your combo box.

You can either go into the SQL view of the query (when the
query's
open,
look under the View menu and select SQL view), or you can look at
the
Properties of the query: you should have choices for "UniqueValues"
and
"UniqueRecords": you want UniqueValues (make sure you're looking at
the
properties for the query itself, not for one of the fields in the
query).
Just make sure that you've only got the necessary fields in the
query
so
that UniqueValues makes sense.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Very newbie here, only started using Access 2 days ago,

I have managed to make a very simple table to use for testing,
made
a
query
and report, even managed to make a combo box on a form and used it
for
the
query criteria and got it all to work, almost all, the table I
made
just
has
some names in a column and numbers in the next column 1, 2, 3, the
combo
box
shows all the numbers like 3 1's 4 2's 3 3's if I pick a 1,
2,
or
3
from
the top of the list everything works fine, but if I pick one of
the
numbers
after the first 3 I get a query or report with no data.

So now the questions,

How do I fix this?

Is there a way to only show unique valves in the combo box?

Or am I going about this the wrong way?

This seams like a lot of work just to get a drop down box for a
query
criteria, is there an easier way?

Also any links to a file like this to download so I could take
a
look
at
it
would be appreciated.

Almost forgot, is there a way to have a "show all" in the combo
box?

Thanks
Using Access 2002