How do I set up a query with combo-box?

[LittleMissComputerGeek1]

Hello,
I am trying to set up a query which displays a combo box asking the user to
select 'the type of item'.

the item type is a field on a table i have called stock and this may have
new ones entered regularly so i cant use fixed choices i have to use the
fields that are entered into the table as the choices on the combobox,

ive tried so many ways and I cant do it.
Anyone got any advice?

Id be so grateful

 

[Marshall Barton]

I am trying to set up a query which displays a combo box asking the user to
select 'the type of item'.

the item type is a field on a table i have called stock and this may have
new ones entered regularly so i cant use fixed choices i have to use the
fields that are entered into the table as the choices on the combobox,

Typically you set the combo box's RowSource to a query
something like:

SELECT DISTINCT TypID, TypeName
FROM thetable
ORDER BY TypeName

 

[LittleMissComputerGeek1]

Typically you set the combo box's RowSource to a query
something like:

SELECT DISTINCT TypID, TypeName
FROM thetable
ORDER BY TypeName

I dont actually know how to make the query display a combo box and ask for a
list of types though

..

 

[Duane Hookom]

You need to create a form with a combo box that can be used as a criteria in
your query.

How you would do this depends on the context of the query. Is it a record
source of a form or report?

IMHO, parameter prompts have no place in proper applications.

 

[Ken Sheridan]

The query doesn't display the combo box. You open a form containing the
combo box first and open the query, or better still a form or report based on
the query, from the form. So the steps are:

1. Create an unbound form, frmTypeDlg say.

2. Add a combo box, cboType say, to the form and set its RowSource property
to:

SELECT [Item Type] FROM Stock Order BY [Item Type]

3. Add a command button to the form and, assuming you will use a form,
frmMyForm, based on the query put the following in the button's Click event
procedure:

Const conMESSAGE = "Please select and item first."

If Not IsNull(Me.cboType) Then
DoCmd.OpenForm "frmMyForm"
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If

To do this select the button in form design view and open its properties
sheet if its not already open. Then select the On Click event property in
the properties sheet. Click on the 'build' button; that's the one on the
right with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The
VBA window will open at the event procedure with the first and last lines
already in place. Enter the above lines of code between these two existing
lines.

4. Create your query and in design view put the following in the 'criteria'
row of the Item Type column:

Forms!frmTypeDlg!cboType

5. Create the form frmMyForm based on the query.

The user now opens the unbound frmTypeDlg dialogue form, not the query or
the form based on it, selects an item and clicks the button to open the bound
form, which will show rows with the selected item only.

One word of warning: If the Stock table has a numeric primary key column,
usually an autonumber, and you've used the dreaded 'lookup wizard' for the
data type when creating whatever table the query is based on, the value in
the column, despite what you see, will not be the item type text value but an
arbitrary number, so a criterion on this column which tries to match the text
values in the combo box won't work. In this scenario you have to set up the
combo box like this:

RowSource: SELECT [Item TypeID], [ItemType] FROM Stock ORDER BY
[ItemType];

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

For an insight into the evil nature of the 'lookup wizard' take a look at:


http://www.mvps.org/access/lookupfields.htm


Ken Sheridan
Stafford, England

 

[LittleMissComputerGeek1]

I understand what your saying and I have tried it but I am getting stuck at
the step 5 where it mentions frmMyForm.
What is this form?
Am i creating another form.

I have done all of the above steps.

Thanks so much 4 ur time
sorry to be a pain

The query doesn't display the combo box. You open a form containing the
combo box first and open the query, or better still a form or report based on
the query, from the form. So the steps are:

1. Create an unbound form, frmTypeDlg say.

2. Add a combo box, cboType say, to the form and set its RowSource property
to:

SELECT [Item Type] FROM Stock Order BY [Item Type]

3. Add a command button to the form and, assuming you will use a form,
frmMyForm, based on the query put the following in the button's Click event
procedure:

Const conMESSAGE = "Please select and item first."

If Not IsNull(Me.cboType) Then
DoCmd.OpenForm "frmMyForm"
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If

To do this select the button in form design view and open its properties
sheet if its not already open. Then select the On Click event property in
the properties sheet. Click on the 'build' button; that's the one on the
right with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The
VBA window will open at the event procedure with the first and last lines
already in place. Enter the above lines of code between these two existing
lines.

4. Create your query and in design view put the following in the 'criteria'
row of the Item Type column:

Forms!frmTypeDlg!cboType

5. Create the form frmMyForm based on the query.

The user now opens the unbound frmTypeDlg dialogue form, not the query or
the form based on it, selects an item and clicks the button to open the bound
form, which will show rows with the selected item only.

One word of warning: If the Stock table has a numeric primary key column,
usually an autonumber, and you've used the dreaded 'lookup wizard' for the
data type when creating whatever table the query is based on, the value in
the column, despite what you see, will not be the item type text value but an
arbitrary number, so a criterion on this column which tries to match the text
values in the combo box won't work. In this scenario you have to set up the
combo box like this:

RowSource: SELECT [Item TypeID], [ItemType] FROM Stock ORDER BY
[ItemType];

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

For an insight into the evil nature of the 'lookup wizard' take a look at:


http://www.mvps.org/access/lookupfields.htm


Ken Sheridan
Stafford, England

I dont actually know how to make the query display a combo box and ask for a
list of types though

.

 

[LittleMissComputerGeek1]

me again
ive done it all now, figured it out.
its just then when i open frmTypeDlg now it and click on the combo box to
select a value it comes up with a dialogue box called Enter Parameter Value
and the text says 'Item Type'.
if i type in e.g. mouthwash then that now appears as an option in the
combobox and for some reason its there twice

however i automatically wanted the combo box to give me the options that
were avaliable from my table stock.

pleeease help

confused
thankyiu

The query doesn't display the combo box. You open a form containing the
combo box first and open the query, or better still a form or report based on
the query, from the form. So the steps are:

1. Create an unbound form, frmTypeDlg say.

2. Add a combo box, cboType say, to the form and set its RowSource property
to:

SELECT [Item Type] FROM Stock Order BY [Item Type]

3. Add a command button to the form and, assuming you will use a form,
frmMyForm, based on the query put the following in the button's Click event
procedure:

Const conMESSAGE = "Please select and item first."

If Not IsNull(Me.cboType) Then
DoCmd.OpenForm "frmMyForm"
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If

To do this select the button in form design view and open its properties
sheet if its not already open. Then select the On Click event property in
the properties sheet. Click on the 'build' button; that's the one on the
right with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The
VBA window will open at the event procedure with the first and last lines
already in place. Enter the above lines of code between these two existing
lines.

4. Create your query and in design view put the following in the 'criteria'
row of the Item Type column:

Forms!frmTypeDlg!cboType

5. Create the form frmMyForm based on the query.

The user now opens the unbound frmTypeDlg dialogue form, not the query or
the form based on it, selects an item and clicks the button to open the bound
form, which will show rows with the selected item only.

One word of warning: If the Stock table has a numeric primary key column,
usually an autonumber, and you've used the dreaded 'lookup wizard' for the
data type when creating whatever table the query is based on, the value in
the column, despite what you see, will not be the item type text value but an
arbitrary number, so a criterion on this column which tries to match the text
values in the combo box won't work. In this scenario you have to set up the
combo box like this:

RowSource: SELECT [Item TypeID], [ItemType] FROM Stock ORDER BY
[ItemType];

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

For an insight into the evil nature of the 'lookup wizard' take a look at:


http://www.mvps.org/access/lookupfields.htm


Ken Sheridan
Stafford, England

I dont actually know how to make the query display a combo box and ask for a
list of types though

.

 

[Ken Sheridan]

You said in your original post that Item Type was a column (field) in a
table Stock, but it sounds like that might not be so. It would help if you
would give us the exact names of the columns in the Stock table and of the
respective foreign key column in the table on which the query underlying the
bound form you are creating.

Ken Sheridan
Stafford, England

me again
ive done it all now, figured it out.
its just then when i open frmTypeDlg now it and click on the combo box to
select a value it comes up with a dialogue box called Enter Parameter Value
and the text says 'Item Type'.
if i type in e.g. mouthwash then that now appears as an option in the
combobox and for some reason its there twice

however i automatically wanted the combo box to give me the options that
were avaliable from my table stock.

pleeease help

confused
thankyiu

The query doesn't display the combo box. You open a form containing the
combo box first and open the query, or better still a form or report based on
the query, from the form. So the steps are:

1. Create an unbound form, frmTypeDlg say.

2. Add a combo box, cboType say, to the form and set its RowSource property
to:

SELECT [Item Type] FROM Stock Order BY [Item Type]

3. Add a command button to the form and, assuming you will use a form,
frmMyForm, based on the query put the following in the button's Click event
procedure:

Const conMESSAGE = "Please select and item first."

If Not IsNull(Me.cboType) Then
DoCmd.OpenForm "frmMyForm"
Else
MsgBox conMESSAGE, vbExclamation, "Invalid Operation"
End If

To do this select the button in form design view and open its properties
sheet if its not already open. Then select the On Click event property in
the properties sheet. Click on the 'build' button; that's the one on the
right with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The
VBA window will open at the event procedure with the first and last lines
already in place. Enter the above lines of code between these two existing
lines.

4. Create your query and in design view put the following in the 'criteria'
row of the Item Type column:

Forms!frmTypeDlg!cboType

5. Create the form frmMyForm based on the query.

The user now opens the unbound frmTypeDlg dialogue form, not the query or
the form based on it, selects an item and clicks the button to open the bound
form, which will show rows with the selected item only.

One word of warning: If the Stock table has a numeric primary key column,
usually an autonumber, and you've used the dreaded 'lookup wizard' for the
data type when creating whatever table the query is based on, the value in
the column, despite what you see, will not be the item type text value but an
arbitrary number, so a criterion on this column which tries to match the text
values in the combo box won't work. In this scenario you have to set up the
combo box like this:

RowSource: SELECT [Item TypeID], [ItemType] FROM Stock ORDER BY
[ItemType];

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

For an insight into the evil nature of the 'lookup wizard' take a look at:


http://www.mvps.org/access/lookupfields.htm


Ken Sheridan
Stafford, England

:

LittleMissComputerGeek1 wrote:
I am trying to set up a query which displays a combo box asking the user to
select 'the type of item'.

the item type is a field on a table i have called stock and this may have
new ones entered regularly so i cant use fixed choices i have to use the
fields that are entered into the table as the choices on the combobox,


Typically you set the combo box's RowSource to a query
something like:

SELECT DISTINCT TypID, TypeName
FROM thetable
ORDER BY TypeName

--
Marsh
MVP [MS Access]




I dont actually know how to make the query display a combo box and ask for a
list of types though

.