Query question

[Jeff Cichocki]

I am a newbie to Access so please forgive me if I don't use the correct
terminology or if I'm missing something that would better help you to help
me.

I am trying to create a simple PO system for my engineering department who
currently manages their PO's via paper and Excell.

My form is comprised of a master list of fields (the header area) and a
sub-form that contains the details of each item ordered on the PO. The
problem I am having is that I want one field to be dependant on what is
entered into another field. I want to make the part number dependant on the
manufacturer that is selected. If no manufacturer is selected the part
number drop down list should be empty. If the manufacturer is filled in,
the part number list is filled with parts that are from that manufacturer
only. Right now, I am getting all parts from all manufacturers. Here is
the query I am using.

SELECT DISTINCTROW Products.* FROM [Order Details] INNER JOIN Products ON
[Order Details].ManufacturerID = Products.ManufacturerID ORDER BY
Products.ProductName;

Can what I am trying to do be done? If so, is there anything that can be
changed in my query to make it work? If not, is there a way that I can get
it to work (events or something else)?

Thanks for any help you can give!

Jeff

 

[Klatuu]

You need a WHERE clause to limit the records returned to the selected
manufacturer. The example below uses a control on your form name
txtManufacturerID that will have the manufacturer ID.

In the AfterUpdate event of that text box, you will need to requery the
combo box so it will update the list in the combo.

SELECT DISTINCTROW Products.* FROM [Order Details] INNER JOIN Products ON
[Order Details].ManufacturerID = Products.ManufacturerID
WHERE [Order Details].ManufacturerID = Forms!MyFormName!txtManufacturerID
ORDER BY
Products.ProductName;

Now, that is not really a good plan. We humans would not know what the ID
is. What would be most useful would be if your manufacturer control were a
combo box based on the manufacturer table. If you don't have one, your
database design is incorrect. You should have a table that contains the
manufacturer's name and other demographic information about the manufacturer.

The combo needs only the manufacture's ID and the manufacturer's name. Then
the product list combo can be based on the value selected in the manufacturer
combo.

What this is is a technique called Cascading Combos. It is a common way to
do what you are wanting to do.

 

[Jeff Cichocki]

Thanks for your info. It has been very helpful. I have been able to at
least get it to populate from my products table.

I still seem to have a couple of problems though. My manufacturer field is
driven by a manufactures table that has all the pertenant information about
the manufacturer. However, my query seems to be the same no matter which
vendor I choose. I don't quite know how to get it to populate based on the
information that is in the manufacturers field. I changed the query a
little from the original I sent you and it does run, just not quite right.
It is...

SELECT DISTINCTROW Products.* FROM Products WHERE Products.ManufacturerID =
[Order Details Subform]!ManufacturerID ORDER BY Products.ProductName;

The problem is that no matter which manufacturer I choose, I get the same
list of products in the product field drop down list. It is this way even
when I first open the form and I am looking at a pre-existing record as
opposed to a new one.

I feel like I am close. But, my lack of experience is definitley keeping me
from knowing what to do next.

Any other suggestions?

You need a WHERE clause to limit the records returned to the selected
manufacturer. The example below uses a control on your form name
txtManufacturerID that will have the manufacturer ID.

In the AfterUpdate event of that text box, you will need to requery the
combo box so it will update the list in the combo.

SELECT DISTINCTROW Products.* FROM [Order Details] INNER JOIN Products ON
[Order Details].ManufacturerID = Products.ManufacturerID
WHERE [Order Details].ManufacturerID = Forms!MyFormName!txtManufacturerID
ORDER BY
Products.ProductName;

Now, that is not really a good plan. We humans would not know what the ID
is. What would be most useful would be if your manufacturer control were
a
combo box based on the manufacturer table. If you don't have one, your
database design is incorrect. You should have a table that contains the
manufacturer's name and other demographic information about the
manufacturer.

The combo needs only the manufacture's ID and the manufacturer's name.
Then
the product list combo can be based on the value selected in the
manufacturer
combo.

What this is is a technique called Cascading Combos. It is a common way
to
do what you are wanting to do.
--
Dave Hargis, Microsoft Access MVP

I am a newbie to Access so please forgive me if I don't use the correct
terminology or if I'm missing something that would better help you to
help
me.

I am trying to create a simple PO system for my engineering department
who
currently manages their PO's via paper and Excell.

My form is comprised of a master list of fields (the header area) and a
sub-form that contains the details of each item ordered on the PO. The
problem I am having is that I want one field to be dependant on what is
entered into another field. I want to make the part number dependant on
the
manufacturer that is selected. If no manufacturer is selected the part
number drop down list should be empty. If the manufacturer is filled in,
the part number list is filled with parts that are from that manufacturer
only. Right now, I am getting all parts from all manufacturers. Here is
the query I am using.

SELECT DISTINCTROW Products.* FROM [Order Details] INNER JOIN Products ON
[Order Details].ManufacturerID = Products.ManufacturerID ORDER BY
Products.ProductName;

Can what I am trying to do be done? If so, is there anything that can be
changed in my query to make it work? If not, is there a way that I can
get
it to work (events or something else)?

Thanks for any help you can give!

Jeff

 

[Jeff Cichocki]

OK. I've got the requery executing the query each time the Product field
has focus and it is returning the correct list of values for the
manufacturer. The problem I have now is that I have 3 manufactures listed
in my grid and the only product I can see is the one I have selected. The
other 2 go blank when they lose focus. I'm sure I'm not controlling this
corectly yet, but I am one step closer. Any ideas as to how I can get them
to stay put?

Thanks

Jeff

Thanks for your info. It has been very helpful. I have been able to at
least get it to populate from my products table.

I still seem to have a couple of problems though. My manufacturer field
is driven by a manufactures table that has all the pertenant information
about the manufacturer. However, my query seems to be the same no matter
which vendor I choose. I don't quite know how to get it to populate based
on the information that is in the manufacturers field. I changed the
query a little from the original I sent you and it does run, just not
quite right. It is...

SELECT DISTINCTROW Products.* FROM Products WHERE Products.ManufacturerID
= [Order Details Subform]!ManufacturerID ORDER BY Products.ProductName;

The problem is that no matter which manufacturer I choose, I get the same
list of products in the product field drop down list. It is this way even
when I first open the form and I am looking at a pre-existing record as
opposed to a new one.

I feel like I am close. But, my lack of experience is definitley keeping
me from knowing what to do next.

Any other suggestions?

You need a WHERE clause to limit the records returned to the selected
manufacturer. The example below uses a control on your form name
txtManufacturerID that will have the manufacturer ID.

In the AfterUpdate event of that text box, you will need to requery the
combo box so it will update the list in the combo.

SELECT DISTINCTROW Products.* FROM [Order Details] INNER JOIN Products ON
[Order Details].ManufacturerID = Products.ManufacturerID
WHERE [Order Details].ManufacturerID = Forms!MyFormName!txtManufacturerID
ORDER BY
Products.ProductName;

Now, that is not really a good plan. We humans would not know what the
ID
is. What would be most useful would be if your manufacturer control were
a
combo box based on the manufacturer table. If you don't have one, your
database design is incorrect. You should have a table that contains the
manufacturer's name and other demographic information about the
manufacturer.

The combo needs only the manufacture's ID and the manufacturer's name.
Then
the product list combo can be based on the value selected in the
manufacturer
combo.

What this is is a technique called Cascading Combos. It is a common way
to
do what you are wanting to do.
--
Dave Hargis, Microsoft Access MVP

I am a newbie to Access so please forgive me if I don't use the correct
terminology or if I'm missing something that would better help you to
help
me.

I am trying to create a simple PO system for my engineering department
who
currently manages their PO's via paper and Excell.

My form is comprised of a master list of fields (the header area) and a
sub-form that contains the details of each item ordered on the PO. The
problem I am having is that I want one field to be dependant on what is
entered into another field. I want to make the part number dependant on
the
manufacturer that is selected. If no manufacturer is selected the part
number drop down list should be empty. If the manufacturer is filled
in,
the part number list is filled with parts that are from that
manufacturer
only. Right now, I am getting all parts from all manufacturers. Here
is
the query I am using.

SELECT DISTINCTROW Products.* FROM [Order Details] INNER JOIN Products
ON
[Order Details].ManufacturerID = Products.ManufacturerID ORDER BY
Products.ProductName;

Can what I am trying to do be done? If so, is there anything that can
be
changed in my query to make it work? If not, is there a way that I can
get
it to work (events or something else)?

Thanks for any help you can give!

Jeff

 

[Armen Stein]

OK. I've got the requery executing the query each time the Product field
has focus and it is returning the correct list of values for the
manufacturer. The problem I have now is that I have 3 manufactures listed
in my grid and the only product I can see is the one I have selected. The
other 2 go blank when they lose focus. I'm sure I'm not controlling this
corectly yet, but I am one step closer. Any ideas as to how I can get them
to stay put?

So your comboboxes are in different records on a continuous form? If
so, this is a problem when using this technique, as Access treats all
the records the same - in other words, they all have the same
rowsource. When you change one, you change them all, causing the
others to "blank out" because that value is no longer in the list. The
underlying key values themselves aren't lost, they just can't be
looked up from the list.

There's no great solution to this. You can display the records
read-only, then double-click them to pop up an edit form with the
cascading combos. Or you can have an edit area right on the screen
(probably below your list) that is populated with the current record
when you click on each one. Both methods require some coding, and
neither is as nice a true independent rowsource property would be.

I've asked the Access team to give us the ability to do this - but it
wouldn't be easy, so I wouldn't be surprised if they can't do it.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Jeff Cichocki]

Unfortunately, yes. I'm trying to do this with a continuous form.

I'll have to figure out a different way to do this.

Thanks for all your help.

Jeff

 

[Douglas J. Steele]

So your comboboxes are in different records on a continuous form? If
so, this is a problem when using this technique, as Access treats all
the records the same - in other words, they all have the same
rowsource. When you change one, you change them all, causing the
others to "blank out" because that value is no longer in the list. The
underlying key values themselves aren't lost, they just can't be
looked up from the list.

There's no great solution to this. You can display the records
read-only, then double-click them to pop up an edit form with the
cascading combos. Or you can have an edit area right on the screen
(probably below your list) that is populated with the current record
when you click on each one. Both methods require some coding, and
neither is as nice a true independent rowsource property would be.

While it's true that the combo boxes will be the same on each row, so that
it's not possible to display values on rows where the necessary value isn't
relevant to the current row, I showed a (relatively simple) way to handle
this in my February, 2006 "Access Answers" column in Pinnacle Publication's
"Smart Access" magazine. You can download the column (and sample database)
for free at http://www.accessmvp.com/DJSteele/SmartAccess.html

Or have I misinterpretted the problem being encountered?

 

[Armen Stein]

While it's true that the combo boxes will be the same on each row, so that
it's not possible to display values on rows where the necessary value isn't
relevant to the current row, I showed a (relatively simple) way to handle
this in my February, 2006 "Access Answers" column in Pinnacle Publication's
"Smart Access" magazine. You can download the column (and sample database)
for free at http://www.accessmvp.com/DJSteele/SmartAccess.html

Or have I misinterpretted the problem being encountered?

Hi Doug,

No, you haven't misinterpreted. I'd heard of that technique, but
hadn't seen your article or tried it. I'm a bit wary of
overlapping-control techniques in general, but your writeup makes it
look quite straightforward. A bit more overhead in the query join,
but that shouldn't be too bad. I like the self-positioning code too.

Have you encountered any problems or incompatibilities with that
method, like visual artifacts or anything else like that?

Thanks,

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Douglas J. Steele]

No, you haven't misinterpreted. I'd heard of that technique, but
hadn't seen your article or tried it. I'm a bit wary of
overlapping-control techniques in general, but your writeup makes it
look quite straightforward. A bit more overhead in the query join,
but that shouldn't be too bad. I like the self-positioning code too.

Have you encountered any problems or incompatibilities with that
method, like visual artifacts or anything else like that?

Can't say that I have.