Combo Box QUESTION

[kealaz]

On my form, I have a combo box w/ the following properties.

Row Source Type: Table/Query
Row Source: SELECT table001.item1, table001.FIELD1, table001.FIELD2,
table001.FIELD3 FROM table001;
column count: 4
column widths: 2"; 0"; 0"; 0"

This combo box is working correctly and giving me the results I want. I
checked by giving the 2nd, 3rd and 4th columns width, and the results are
there and correct.

The next combo box is the one I am working on and asked about in my original
post. I want the values in column 3, 4 & 5 of the first combo box to be
selections in this second combo box, such that the choices are

[value of FIELD 1]
[value of FIELD 2]
[value of FIELD 3]


How do I make this happen? Would I use After Update on the first box? or
Row Source on the second combo box. Can my source be a control in the same
form? What is the code to make this work?


I hope this makes more sense. THANK YOU so much for your help with this.

 

[ntc]

I don't think this is easily accomplished. Going from left-right (query 1)
to stacked (query 2) is not normalized data.

if there is always just 3 fields - rather than a 2nd drop down; it would be
easier if you stack 3 text boxes that are sourced on those 3 fields from
combo1...and then perhaps made your 'selection' via a checkbox method....

however if there are a variable quantity of fields then it is challenging -
would be interested in what other advice you receive on this.....

 

[rbeach]

Try the below:

RecordSource: SELECT [table001].[item1], [table001].[item2],
[table001].[item3], [table001].[item4] FROM table001;

'You only want those Items visible that are in the store that has been
selected from cboStore.

'To do this you will need to modify the cboItem RecordSource in the
AfterUpdate event of cboStore:



Private Sub cboStore_AfterUpdate()
Dim sItemSource As String

sItemSource = "SELECT [table001].[item1], [table001].[item2],
[table001].[item3], [table001].[item4] " & _
"FROM table001 " & _
"WHERE [item2] = " & Me.cboStore.Value
Me.cboItem.RowSource = sItemSource
Me.cboItem.Requery

 

[rbeach]

RecordSource: SELECT [table001].[item1], [table001].[item2],
[table001].[item3], [table001].[item4] FROM table001;

' You only want those Items visible that are in the store that has been
selected from cboStore.

' To do this you will need to modify the cboItem RecordSource in the
AfterUpdate event of cboStore:



Private Sub cboStore_AfterUpdate()
Dim sItemSource As String

sItemSource = "SELECT [table001].[item1], [table001].[item2],
[table001].[item3], [table001].[item4] " & _
"FROM table001 " & _
"WHERE [item2] = " & Me.cboStore.Value
Me.cboItem.RowSource = sItemSource
Me.cboItem.Requery

 

[kealaz]

Hi Rick,
I'm don't really understand your answer. I think the cboStore and
cboItems are confusing me, because the application that I'm using this for is
not a store.

It is for purchasing. I have a part and each part can have up to, but never
more than 3 manufacturers. Since there would not be more than 3 mfgs, I
think I can get it to work, if I can figure out a way to get the fields in
that second combo box to populate with columns 2, 3, and 4 from the first
combo box.

So, in my example, item1 is my part number and FIELD1, 2 & 3 are the 3
possible manufacturers. I need to allow my user to select one of the 3
available mfgs., which is why I'm trying to get them into that combo box.
I'm sure you've already answered my question, but I'm not able to make sense
of it, because I'm not sure where your example coincides with my example.
Please help again.

Thank you!!!

 

[rbeach]

cboStore is just the value stored from the first combo box. cbo items is the
combo box items listed in the second combo box.