Basing one combo box on a query result and auto fill a second fiel

[TOMM]

I have created a small data base to track Cpk values of several part numbers,
and it works almost the way that I need it to work but not quite.

I want to have a combo box display the results of a query so that I may
choose a single value from several and then with that value selected I need
to have the detail for that value auto fill another combo box.

Results of query look like this:

Part Number Balloon Number Feature Detail
0020-02348 25 .005 Max Radius
0020-02348 27 .002 Flatness
0020-02348 35 TP .005
0020-02348 41 1.234 +/- .005

To avoid looking at multiple values in the Part Number list I have a table
structured to list the part number only once, so my first combo box looks at
this table to get the correct part number then the second combo box asks for
a part number parameter value. Once this opens it lists the balloon numbers
for that part number but now I need to have the Feature description autofill
the final box.

 

[Damon Heron]

Your description is a bit confusing, but you seem to be saying that the
second combobox is working and displaying the balloon #s correctly, based on
the selection of the part # in the first combobox. Amend your second
combobox to include the feature detail field, and set the column numbers
accordingly. Then in the afterupdate event of the second combobox, set the
value of a textbox on your form:
me.text1 = 2ndCombobox.column(1)

The above assumes you have two columns, 0 and 1. If you have more then
adjust.

Damon

 

[TOMM]

thanks,

I know that i was rambling, sorry. I have 3 combo boxes to enter the data on
my form.

Combo box # 1 finds the part number from a table which puts that part number
into my table.

Combo box # 2 runs a query using the same part number as listed in combo box
# 1. I have to type my choice in this parameter query. I would like it to
look at combo box # 1 and use the part number there to run the query. From
the query I get a list of all balloon numbers associated with the part number
(could be as few as 1 or up to maybe 15) I would like to have my choice of
balloon number in combo box # 2 auto fill combo box number # 3 to avoid all
of the typing mistakes possible by the many choices.

It looks like the sugggestion you gave me will auto fill combo box # 3 but I
am still stuck typing in the part number to initiate the query. Is there any
way around this?
--
THANK YOU IN ADVANCE FOR YOU HELP WITH MY CHALLENGE

TOMM

Your description is a bit confusing, but you seem to be saying that the
second combobox is working and displaying the balloon #s correctly, based on
the selection of the part # in the first combobox. Amend your second
combobox to include the feature detail field, and set the column numbers
accordingly. Then in the afterupdate event of the second combobox, set the
value of a textbox on your form:
me.text1 = 2ndCombobox.column(1)

The above assumes you have two columns, 0 and 1. If you have more then
adjust.

Damon

 

[Damon Heron]

Your source for combo2 should be something like
"Select yourtblname.BalloonNumber, yourtblName.Feature from yourtblName
where yourtblName.PartNumber = [combobox1]"
substituting your names, of course. The combo2 should have two columns, one
for balloon # and one for Feature. Since there is only one feature for each
balloon, once a balloon # is selected in combo2, no need for combo3. You
have the feature already selected as combobox2.column(1). A combobox is for
multiple selections, so the feature could be assigned to a textbox, as I
suggested in first post.
Damon

thanks,

I know that i was rambling, sorry. I have 3 combo boxes to enter the data
on
my form.

Combo box # 1 finds the part number from a table which puts that part
number
into my table.

Combo box # 2 runs a query using the same part number as listed in combo
box
# 1. I have to type my choice in this parameter query. I would like it to
look at combo box # 1 and use the part number there to run the query. From
the query I get a list of all balloon numbers associated with the part
number
(could be as few as 1 or up to maybe 15) I would like to have my choice of
balloon number in combo box # 2 auto fill combo box number # 3 to avoid
all
of the typing mistakes possible by the many choices.

It looks like the sugggestion you gave me will auto fill combo box # 3 but
I
am still stuck typing in the part number to initiate the query. Is there
any
way around this?

 

[TOMM]

Thanks, Damon

My apologies, I noted the final box as a Combo Box when in fact it is a text
box. The suggested code you gave me in your first response identifies;
me.text = 2nd Combobox.column(1) to be used in the afterupdate line. Is this
still a correct statement with a text box in the 3rd position? I hope you
bear with me on this, I am not exactly new to access but this is one task I
have never needed to understand before.
--
THANK YOU IN ADVANCE FOR YOU HELP WITH MY CHALLENGE

TOMM

Your source for combo2 should be something like
"Select yourtblname.BalloonNumber, yourtblName.Feature from yourtblName
where yourtblName.PartNumber = [combobox1]"
substituting your names, of course. The combo2 should have two columns, one
for balloon # and one for Feature. Since there is only one feature for each
balloon, once a balloon # is selected in combo2, no need for combo3. You
have the feature already selected as combobox2.column(1). A combobox is for
multiple selections, so the feature could be assigned to a textbox, as I
suggested in first post.
Damon

thanks,

I know that i was rambling, sorry. I have 3 combo boxes to enter the data
on
my form.

Combo box # 1 finds the part number from a table which puts that part
number
into my table.

Combo box # 2 runs a query using the same part number as listed in combo
box
# 1. I have to type my choice in this parameter query. I would like it to
look at combo box # 1 and use the part number there to run the query. From
the query I get a list of all balloon numbers associated with the part
number
(could be as few as 1 or up to maybe 15) I would like to have my choice of
balloon number in combo box # 2 auto fill combo box number # 3 to avoid
all
of the typing mistakes possible by the many choices.

It looks like the sugggestion you gave me will auto fill combo box # 3 but
I
am still stuck typing in the part number to initiate the query. Is there
any
way around this?

 

[Damon Heron]

Yes,
"me.text" meaning "me.[your textbox name] = [yourcombobox2name].column(1)"
You know how to get to the vba window, right? The code goes there, not in
the property box.

Damon

Thanks, Damon

My apologies, I noted the final box as a Combo Box when in fact it is a
text
box. The suggested code you gave me in your first response identifies;
me.text = 2nd Combobox.column(1) to be used in the afterupdate line. Is
this
still a correct statement with a text box in the 3rd position? I hope you
bear with me on this, I am not exactly new to access but this is one task
I
have never needed to understand before.
--
THANK YOU IN ADVANCE FOR YOU HELP WITH MY CHALLENGE

TOMM

Your source for combo2 should be something like
"Select yourtblname.BalloonNumber, yourtblName.Feature from yourtblName
where yourtblName.PartNumber = [combobox1]"
substituting your names, of course. The combo2 should have two columns,
one
for balloon # and one for Feature. Since there is only one feature for
each
balloon, once a balloon # is selected in combo2, no need for combo3. You
have the feature already selected as combobox2.column(1). A combobox is
for
multiple selections, so the feature could be assigned to a textbox, as I
suggested in first post.
Damon

thanks,

I know that i was rambling, sorry. I have 3 combo boxes to enter the
data
on
my form.

Combo box # 1 finds the part number from a table which puts that part
number
into my table.

Combo box # 2 runs a query using the same part number as listed in
combo
box
# 1. I have to type my choice in this parameter query. I would like it
to
look at combo box # 1 and use the part number there to run the query.
From
the query I get a list of all balloon numbers associated with the part
number
(could be as few as 1 or up to maybe 15) I would like to have my choice
of
balloon number in combo box # 2 auto fill combo box number # 3 to avoid
all
of the typing mistakes possible by the many choices.

It looks like the sugggestion you gave me will auto fill combo box # 3
but
I
am still stuck typing in the part number to initiate the query. Is
there
any
way around this?
--
THANK YOU IN ADVANCE FOR YOU HELP WITH MY CHALLENGE

TOMM


:

Your description is a bit confusing, but you seem to be saying that
the
second combobox is working and displaying the balloon #s correctly,
based
on
the selection of the part # in the first combobox. Amend your second
combobox to include the feature detail field, and set the column
numbers
accordingly. Then in the afterupdate event of the second combobox,
set
the
value of a textbox on your form:
me.text1 = 2ndCombobox.column(1)

The above assumes you have two columns, 0 and 1. If you have more
then
adjust.

Damon



I have created a small data base to track Cpk values of several part
numbers,
and it works almost the way that I need it to work but not quite.

I want to have a combo box display the results of a query so that I
may
choose a single value from several and then with that value selected
I
need
to have the detail for that value auto fill another combo box.

Results of query look like this:

Part Number Balloon Number Feature Detail
0020-02348 25 .005 Max
Radius
0020-02348 27 .002 Flatness
0020-02348 35 TP .005
0020-02348 41 1.234 +/- .005

To avoid looking at multiple values in the Part Number list I have a
table
structured to list the part number only once, so my first combo box
looks
at
this table to get the correct part number then the second combo box
asks
for
a part number parameter value. Once this opens it lists the balloon
numbers
for that part number but now I need to have the Feature description
autofill
the final box.

 

[TOMM]

Damon,

Yes, i know how to go to the vba. window using the ... at the end of the
afterupdate line. I will now try all of your suggestions. Hopefully, I can
make these changes and my problems will be solved. Thank you. If I fail I
will need to get back to you once again.
--
THANK YOU IN ADVANCE FOR YOU HELP WITH MY CHALLENGE

TOMM

Yes,
"me.text" meaning "me.[your textbox name] = [yourcombobox2name].column(1)"
You know how to get to the vba window, right? The code goes there, not in
the property box.

Damon

Thanks, Damon

My apologies, I noted the final box as a Combo Box when in fact it is a
text
box. The suggested code you gave me in your first response identifies;
me.text = 2nd Combobox.column(1) to be used in the afterupdate line. Is
this
still a correct statement with a text box in the 3rd position? I hope you
bear with me on this, I am not exactly new to access but this is one task
I
have never needed to understand before.
--
THANK YOU IN ADVANCE FOR YOU HELP WITH MY CHALLENGE

TOMM

Your source for combo2 should be something like
"Select yourtblname.BalloonNumber, yourtblName.Feature from yourtblName
where yourtblName.PartNumber = [combobox1]"
substituting your names, of course. The combo2 should have two columns,
one
for balloon # and one for Feature. Since there is only one feature for
each
balloon, once a balloon # is selected in combo2, no need for combo3. You
have the feature already selected as combobox2.column(1). A combobox is
for
multiple selections, so the feature could be assigned to a textbox, as I
suggested in first post.
Damon

thanks,

I know that i was rambling, sorry. I have 3 combo boxes to enter the
data
on
my form.

Combo box # 1 finds the part number from a table which puts that part
number
into my table.

Combo box # 2 runs a query using the same part number as listed in
combo
box
# 1. I have to type my choice in this parameter query. I would like it
to
look at combo box # 1 and use the part number there to run the query.
From
the query I get a list of all balloon numbers associated with the part
number
(could be as few as 1 or up to maybe 15) I would like to have my choice
of
balloon number in combo box # 2 auto fill combo box number # 3 to avoid
all
of the typing mistakes possible by the many choices.

It looks like the sugggestion you gave me will auto fill combo box # 3
but
I
am still stuck typing in the part number to initiate the query. Is
there
any
way around this?
--
THANK YOU IN ADVANCE FOR YOU HELP WITH MY CHALLENGE

TOMM


:

Your description is a bit confusing, but you seem to be saying that
the
second combobox is working and displaying the balloon #s correctly,
based
on
the selection of the part # in the first combobox. Amend your second
combobox to include the feature detail field, and set the column
numbers
accordingly. Then in the afterupdate event of the second combobox,
set
the
value of a textbox on your form:
me.text1 = 2ndCombobox.column(1)

The above assumes you have two columns, 0 and 1. If you have more
then
adjust.

Damon



I have created a small data base to track Cpk values of several part
numbers,
and it works almost the way that I need it to work but not quite.

I want to have a combo box display the results of a query so that I
may
choose a single value from several and then with that value selected
I
need
to have the detail for that value auto fill another combo box.

Results of query look like this:

Part Number Balloon Number Feature Detail
0020-02348 25 .005 Max
Radius
0020-02348 27 .002 Flatness
0020-02348 35 TP .005
0020-02348 41 1.234 +/- .005

To avoid looking at multiple values in the Part Number list I have a
table
structured to list the part number only once, so my first combo box
looks
at
this table to get the correct part number then the second combo box
asks
for
a part number parameter value. Once this opens it lists the balloon
numbers
for that part number but now I need to have the Feature description
autofill
the final box.

 

[TOMM]

Damon,

Here is what the properties are set for in the two combo boxes and one text
box.

Code is new to me and usually ACCESS does what I want so I only understand
some of it.

Combo Box # 1

Row Source Type Table/ Query
Row Source
SELECT[PartNumberMasterTable].[PartNumber]FROM[PartNumberMastertable]ORDERBY[PartNumber]DESC;

Combo Box # 2

Row Source Type Table/Query
Row Source
SELECT[tblballoonnumberquery].[BalloonNumbers],[tblballoonNumbersquery].FeatureDescription]From[tblballoonNumbersquery];

Text Box

Control Source FeatureDescription

Does any of this help you understand what I am working with?

--
THANK YOU IN ADVANCE FOR YOU HELP WITH MY CHALLENGE

TOMM

Thanks, Damon

My apologies, I noted the final box as a Combo Box when in fact it is a text
box. The suggested code you gave me in your first response identifies;
me.text = 2nd Combobox.column(1) to be used in the afterupdate line. Is this
still a correct statement with a text box in the 3rd position? I hope you
bear with me on this, I am not exactly new to access but this is one task I
have never needed to understand before.
--
THANK YOU IN ADVANCE FOR YOU HELP WITH MY CHALLENGE

TOMM

Your source for combo2 should be something like
"Select yourtblname.BalloonNumber, yourtblName.Feature from yourtblName
where yourtblName.PartNumber = [combobox1]"
substituting your names, of course. The combo2 should have two columns, one
for balloon # and one for Feature. Since there is only one feature for each
balloon, once a balloon # is selected in combo2, no need for combo3. You
have the feature already selected as combobox2.column(1). A combobox is for
multiple selections, so the feature could be assigned to a textbox, as I
suggested in first post.
Damon

thanks,

I know that i was rambling, sorry. I have 3 combo boxes to enter the data
on
my form.

Combo box # 1 finds the part number from a table which puts that part
number
into my table.

Combo box # 2 runs a query using the same part number as listed in combo
box
# 1. I have to type my choice in this parameter query. I would like it to
look at combo box # 1 and use the part number there to run the query. From
the query I get a list of all balloon numbers associated with the part
number
(could be as few as 1 or up to maybe 15) I would like to have my choice of
balloon number in combo box # 2 auto fill combo box number # 3 to avoid
all
of the typing mistakes possible by the many choices.

It looks like the sugggestion you gave me will auto fill combo box # 3 but
I
am still stuck typing in the part number to initiate the query. Is there
any
way around this?
--
THANK YOU IN ADVANCE FOR YOU HELP WITH MY CHALLENGE

TOMM


:

Your description is a bit confusing, but you seem to be saying that the
second combobox is working and displaying the balloon #s correctly, based
on
the selection of the part # in the first combobox. Amend your second
combobox to include the feature detail field, and set the column numbers
accordingly. Then in the afterupdate event of the second combobox, set
the
value of a textbox on your form:
me.text1 = 2ndCombobox.column(1)

The above assumes you have two columns, 0 and 1. If you have more then
adjust.

Damon



I have created a small data base to track Cpk values of several part
numbers,
and it works almost the way that I need it to work but not quite.

I want to have a combo box display the results of a query so that I may
choose a single value from several and then with that value selected I
need
to have the detail for that value auto fill another combo box.

Results of query look like this:

Part Number Balloon Number Feature Detail
0020-02348 25 .005 Max Radius
0020-02348 27 .002 Flatness
0020-02348 35 TP .005
0020-02348 41 1.234 +/- .005

To avoid looking at multiple values in the Part Number list I have a
table
structured to list the part number only once, so my first combo box
looks
at
this table to get the correct part number then the second combo box
asks
for
a part number parameter value. Once this opens it lists the balloon
numbers
for that part number but now I need to have the Feature description
autofill
the final box.

 

[Damon Heron]

Well, its obvious that there is no relationship stated between 1 and 2. The
2nd combo needs to have
a partnumber field in the query. Is there one? When you open it up for
view, does the query have a field that is the partnumber? If so, then just
amend the 2nd combos select statement to include the where statement:

SELECT [tblballoonnumberquery].[BalloonNumbers],
[tblballoonNumbersquery].FeatureDescription] From[tblballoonNumbersquery]
WHERE tblballoonNumbersquery.PartNumber = [combobox1];

If not, then amend the query to include the partnumber. Note that the
select statement needs spaces between the words - your example wasn't clear
on that....

Damon

 

[TOMM]

Damon,

I know that this is a simple thing to do and I apologize that I continue to
fail to understand exactly what you are suggesting and wasting your time and
energy.

I typed the suggested statement into the row source for the second combo box
and get a reply Missing operator in query expression tblballoon Numbers
Query.PartNumber = [Combo42]


My query tblballon Numbers Query does in fact present a part number, balloon
number and feature description when queried, but only allows me to save one
of the 3 values in one field so I choose balloon number saved in the balloon
number field. So I don't understand how this can auto fill the text box.

Does what you are suggesting do this; allow me to select a part number from
a list in a seperate table in Combo box # 1 , present me with a drop down
list of the balloon numbers associated with only this particular part number
and finally autofill the text box with the feature description associated
with the balloon number selected in combo box # 2? Or has my rambling methods
clouded the intent of my question?


--
THANK YOU IN ADVANCE FOR YOU HELP WITH MY CHALLENGE

TOMM

Well, its obvious that there is no relationship stated between 1 and 2. The
2nd combo needs to have
a partnumber field in the query. Is there one? When you open it up for
view, does the query have a field that is the partnumber? If so, then just
amend the 2nd combos select statement to include the where statement:

SELECT [tblballoonnumberquery].[BalloonNumbers],
[tblballoonNumbersquery].FeatureDescription] From[tblballoonNumbersquery]
WHERE tblballoonNumbersquery.PartNumber = [combobox1];

If not, then amend the query to include the partnumber. Note that the
select statement needs spaces between the words - your example wasn't clear
on that....

Damon

Damon,

Here is what the properties are set for in the two combo boxes and one
text
box.

Code is new to me and usually ACCESS does what I want so I only understand
some of it.

Combo Box # 1

Row Source Type Table/ Query
Row Source
SELECT[PartNumberMasterTable].[PartNumber]FROM[PartNumberMastertable]ORDERBY[PartNumber]DESC;

Combo Box # 2

Row Source Type Table/Query
Row Source
SELECT[tblballoonnumberquery].[BalloonNumbers],[tblballoonNumbersquery].FeatureDescription]From[tblballoonNumbersquery];

Text Box

Control Source FeatureDescription

Does any of this help you understand what I am working with?

 

[Damon Heron]

It works for me. Post the exact statement you have as a row source.
You might also try opening a query window and post the statement,
substituting the [Combobox] with [?]. When run, the query will ask for the
value of [?]. That means your statement has the correct syntax.

Incidentally, you said the query only "allows" one field - that can't be
right. In the combobox properties, you can set the number of columns to be
displayed, and set the columnwidth like this (say for 3 columns)- .5";1";1"
In this case, the column(0) would be 1/2 inch, column(1) would be 1 inch,
etc.
Then you can refer to them by column. So"
me.textbox1 = me.combobox1.column(1) or whatever.

Damon

Damon,

I know that this is a simple thing to do and I apologize that I continue
to
fail to understand exactly what you are suggesting and wasting your time
and
energy.

I typed the suggested statement into the row source for the second combo
box
and get a reply Missing operator in query expression tblballoon Numbers
Query.PartNumber = [Combo42]


My query tblballon Numbers Query does in fact present a part number,
balloon
number and feature description when queried, but only allows me to save
one
of the 3 values in one field so I choose balloon number saved in the
balloon
number field. So I don't understand how this can auto fill the text box.

Does what you are suggesting do this; allow me to select a part number
from
a list in a seperate table in Combo box # 1 , present me with a drop down
list of the balloon numbers associated with only this particular part
number
and finally autofill the text box with the feature description associated
with the balloon number selected in combo box # 2? Or has my rambling
methods
clouded the intent of my question?


--
THANK YOU IN ADVANCE FOR YOU HELP WITH MY CHALLENGE

TOMM

Well, its obvious that there is no relationship stated between 1 and 2.
The
2nd combo needs to have
a partnumber field in the query. Is there one? When you open it up for
view, does the query have a field that is the partnumber? If so, then
just
amend the 2nd combos select statement to include the where statement:

SELECT [tblballoonnumberquery].[BalloonNumbers],
[tblballoonNumbersquery].FeatureDescription] From[tblballoonNumbersquery]
WHERE tblballoonNumbersquery.PartNumber = [combobox1];

If not, then amend the query to include the partnumber. Note that the
select statement needs spaces between the words - your example wasn't
clear
on that....

Damon

Damon,

Here is what the properties are set for in the two combo boxes and one
text
box.

Code is new to me and usually ACCESS does what I want so I only
understand
some of it.

Combo Box # 1

Row Source Type Table/ Query
Row Source
SELECT[PartNumberMasterTable].[PartNumber]FROM[PartNumberMastertable]ORDERBY[PartNumber]DESC;

Combo Box # 2

Row Source Type Table/Query
Row Source
SELECT[tblballoonnumberquery].[BalloonNumbers],[tblballoonNumbersquery].FeatureDescription]From[tblballoonNumbersquery];

Text Box

Control Source FeatureDescription

Does any of this help you understand what I am working with?

 

[TOMM]

Damon,

Thanks much for your help on this. It looks like my understanding of VBA is
so limited that I am without hope of completing this task even with your good
help. I am asking another employee to review this entire thread and see if he
can manage to make it work; if not we will just make it work without the ease
I had hoped to accomplish with your help.
--
THANK YOU IN ADVANCE FOR YOU HELP WITH MY CHALLENGE

TOMM

It works for me. Post the exact statement you have as a row source.
You might also try opening a query window and post the statement,
substituting the [Combobox] with [?]. When run, the query will ask for the
value of [?]. That means your statement has the correct syntax.

Incidentally, you said the query only "allows" one field - that can't be
right. In the combobox properties, you can set the number of columns to be
displayed, and set the columnwidth like this (say for 3 columns)- .5";1";1"
In this case, the column(0) would be 1/2 inch, column(1) would be 1 inch,
etc.
Then you can refer to them by column. So"
me.textbox1 = me.combobox1.column(1) or whatever.

Damon

Damon,

I know that this is a simple thing to do and I apologize that I continue
to
fail to understand exactly what you are suggesting and wasting your time
and
energy.

I typed the suggested statement into the row source for the second combo
box
and get a reply Missing operator in query expression tblballoon Numbers
Query.PartNumber = [Combo42]


My query tblballon Numbers Query does in fact present a part number,
balloon
number and feature description when queried, but only allows me to save
one
of the 3 values in one field so I choose balloon number saved in the
balloon
number field. So I don't understand how this can auto fill the text box.

Does what you are suggesting do this; allow me to select a part number
from
a list in a seperate table in Combo box # 1 , present me with a drop down
list of the balloon numbers associated with only this particular part
number
and finally autofill the text box with the feature description associated
with the balloon number selected in combo box # 2? Or has my rambling
methods
clouded the intent of my question?


--
THANK YOU IN ADVANCE FOR YOU HELP WITH MY CHALLENGE

TOMM

Well, its obvious that there is no relationship stated between 1 and 2.
The
2nd combo needs to have
a partnumber field in the query. Is there one? When you open it up for
view, does the query have a field that is the partnumber? If so, then
just
amend the 2nd combos select statement to include the where statement:

SELECT [tblballoonnumberquery].[BalloonNumbers],
[tblballoonNumbersquery].FeatureDescription] From[tblballoonNumbersquery]
WHERE tblballoonNumbersquery.PartNumber = [combobox1];

If not, then amend the query to include the partnumber. Note that the
select statement needs spaces between the words - your example wasn't
clear
on that....

Damon

Damon,

Here is what the properties are set for in the two combo boxes and one
text
box.

Code is new to me and usually ACCESS does what I want so I only
understand
some of it.

Combo Box # 1

Row Source Type Table/ Query
Row Source
SELECT[PartNumberMasterTable].[PartNumber]FROM[PartNumberMastertable]ORDERBY[PartNumber]DESC;

Combo Box # 2

Row Source Type Table/Query
Row Source
SELECT[tblballoonnumberquery].[BalloonNumbers],[tblballoonNumbersquery].FeatureDescription]From[tblballoonNumbersquery];

Text Box

Control Source FeatureDescription

Does any of this help you understand what I am working with?