limit a combobox 2

[Joep1st]

How do I limit the contents of one combo box based on the selection in another? i know how to do this in access, but now i'm working with a *.adp file and sql server an the old syntax is not working anymore.
before it was something like this: "where productID= forms!myform!mycontrol"

How does it work in my situation?

thanks.

 

[jes]

Hey Joe,

Have you requeried the combo box after the user has selected from the first? Does the syntax not work at all?

I know jack about .adb files by the way, but it might be something as simple as that.

Regards,

Jes

 

[micada]

Joe,

How did you do it in Access before? I'm still very green at Access and would love to know how to limit lists.

I need to have one box return a specific set of values based on the value the user selects in another. For example, if machine#1 is selected out of a list of machines, only the parts pertaining to that machine will be available in the next box.

Thanks,
Mike

 

[SBendBuckeye]

Mike,

Assume your first combobox is named cboMachine. Then modify the rowsource for your second combobox to include a where clause something like this:

"Where MachineNo = " & me.cboMachine.value

If you go into the builder option in query and select forms, etc you can watch it build one for you in case the above syntax is not quite correct (I think it is but I can't test it and this is off the top of my head)

Have a great day!

 

[tEkHEd]

Yo Joep1st


Sorry to have to be the one to tell you this.. but...

You cannot use a combo box as a control source for an ADP file

I had exactly the same problem, and have resorted to using an MDB with linked SQL tables... Check out MSDN for more details...

 

[tEkHEd]

Oh a 2nd point that I just picked up too is that if you are refering to "RecordSets" with the combo boxes in an ADP/SQL env, and/or using the Microsoft Data Engine (XP) then you need to use ADODB rather than DAO..

Hope that this is of use..

 

[tEkHEd]

BTW here is how u do it with an MDB just incase ur still wondering...

Query for combobox 1:

SELECT Field1
FROM tblMyTable
GROUP BY tblMyTable.Field1
ORDER BY tblMyTable.Field1;

Then Query for Combobox 2 (being dependant upon selection from combobox 1)

SELECT Field2
FROM tblMyTable
GROUP BY tblMyTable.Field2, tblMyTable.Field1
HAVING (((tblMyTable.Field1)='[Forms]![frmMyForm]![cboMyCombo1]'))
ORDER BY tblMyTable.Field2;

Pretty much same for a 3rd box....

SELECT tblMyTable.Field3 , tblMyTable.Field2, tblMyTable.Field3
FROM tblMyTable
GROUP BY tblMyTable.Field3, tblMyTable.Field1, tblMyTable.Field2
HAVING (((tblMyTable.Field1)=[cboCombo1]) AND ((tblMyTable.Field2)=[cboCombo2]));

 

[SBendBuckeye]

Hello TekHed,

I always use Where clauses to restrict things but I like your idea of using Group By and Having because most SQL would not have those clauses making it easier to add on the fly, etc.

A question on your example above. Since each combobox in turn will be restricted to the preceeding one, do you need to concatenate all preceeding combobox restrictions or can you just use the preceeding one?

Thanks and have a great day!

 

[tEkHEd]

Hey SBendBuckeye...

I personally do not concatenate the preceeding combo's.. I don't think that there is a need.. you are already concatenating, by means of the combo filter...

or am I misunderstanding your Q??

The example I gave above is being used to identify pieces of software so my table looks something like:

Manufacturer Product Version

Microsoft Office 95
Microsoft office 97
Microsoft office 2000
Hummingbird exceed 6.0
hummingbird exceed 7.0

So if u have Microsoft listed it will only show Office in combo2 but would show 95/97/200 in combo3

I also have some error checking (IF IsNull cboCombo1 etc...)

 

[SBendBuckeye]

My question was regarding having all of the criteria concatenated. If you use Having ctl1 to limit clt2 then I don't think you to repeat the Having ctl1 in ctl3 as you did above since it was already used to limit ctl2. I was just asking.

Have a great day!

 

[tEkHEd]

ah sorry my mistake.. now I understand..

yes you do need to specify that the version is dependant upon both the manufacturer and product, otherwise you can end up with multiple like version numbers (e.g. 3 x 7.0)..

This is not good for my form as I am using this information to display the license type that we have for that soft (e.g select, individual etc..) If I didn't have my query based on the output of the combo boxes I couldn't rely 100% on the validity of the License type..