Relating fields in a form

[doylie]

I have two fields in a form which are linked to tables forming pull down lists. however these fields relate to each other and I am trying to make it so that if you pick an item from the first list, the second list only lets you choose items that are related to that list eg:-
CADM FRIER
CADM MISC
CADM OVEN
CADM REFRIG
CADM UTENSIL
CIVL BARRIER
CIVL DOOR
CIVL HYD_DOOR
CIVL LIFT
CIVL MISC
CIVL OIL_SKIMMER
CIVL TURNSTILE
CLNR STEAM
CLNR TANK
COMP AIR
COMP AIR_DRY
CRAN BLOCK
CRAN EOT
CRAN FORK_LIFT
CRAN HOIST
CRAN JIB
CRAN MISC
CRAN PORTABLE
CRAN RUNWAY_BEAMS
CRAN TOWER
CRAN TROLLEY
DOCK CAPSTAN
DOCK FAIRLEAD
DOCK MISC

So if I chose CADM in the first list I would only be able to pick either FRIER, MISC, OVEN, REFRIG or UTENSIL from the second list.
At the moment you can pick any from list one and any from list two? Any help would be great.

 

[simongallop]

Make list 2 based on a query rather than the table. Set the query to limit the records by the selected item from List1.

HTH

 

[doylie]

Limit options in list 2

Cheers Harry,

But I am trying to make it so that the options are limited to what has been selected in the first field. If this is what you meant how do you do it

 

[simongallop]

Here is a simple sample using 2 comboboxes called cmbFirst and cmbSecond on a form called MyForm. The data is in a table called MyTable with fields Field1 and Field2.

For the rowsource of cmbFirst: SELECT DISTINCTROW [Field1] FROM [MyTable] GROUP BY [Field1];

For the rowsource of cmbSecond:SELECT DISTINCTROW [Field1], [Field2] FROM [MyTable] WHERE ([Field1])=[Forms]![MyForm]![cmbFirst];

You now need a piece of code to update the second combo box after you have selected your group. So in cmbFirst use this code in the OnChange event:

Private Sub cmbFirst_Change()
Me.cmbSecond.Requery
End Sub

Now it will limit the selection in cmbSecond depending on what you choose in cmbFirst.

Naturally you will have to change: MyTable, Field1, Field2, MyForm, cmbFirst and cmbSecond to whatever they are called in your db.

HTH

 

[doylie]

Not quite right

This is nearly right, but all I am getting is a choice of what was selected in the first field.ie.
If CADM was selected in the first field, Then I get a choice of CADM,CADM,CADM,CADM,CADM,CADM etc.

 

[simongallop]

In the second combo box set the column count = 2 and column widths 0cm;3cm

 

[doylie]

Nearly

Now I get a list that relates to the other one but I have some blank spaces and a lot of duplicates.....

 

[simongallop]

Which is what exists in your table. If you want unique then you will need to change the SQL in cmbSecond to :

SELECT DISTINCTROW [Field1], [Field2] FROM [MyTable] GROUP BY [Field1], [Field2] HAVING ([Field1]=[Forms]![MyForm]![cmbFirst]);

HTH

 

[doylie]

Thankyou

Thankyou, what a Star!!!!!!!!!!!!!!!!!1

 

[doylie]

Nearly a star

It would seem that, whatever I pick from the list, only the first item in the list is displayed. Sorry to be a pain

 

[simongallop]

That would indicate that you are pulling in other fields into your comboboxes. If you are then please post the SQL for the two boxes. If not then you will have to check that the data is correct ie there aren't any errant spaces etc in the fields

 

[doylie]

Here is the SQL, erm.
SELECT DISTINCTROW [Asset Data - Cleansing].mcgrp
FROM [Asset Data - Cleansing]
GROUP BY [Asset Data - Cleansing].mcgrp;


SELECT DISTINCTROW [mcgrp], [SubMCGRP]
FROM [Asset Data - Cleansing]
GROUP BY [mcgrp], [subMCGRP]
HAVING ([mcgrp]=[Forms]![Asset Data - Cleansing Form]![mcgrp]);



What are errant spaces?

 

[simongallop]

What are the names of your combo boxes? Please don't tell me that you are using the same name for the combobox as for the field in the table!

 

[doylie]

Yes, Obviousley this is a problem , Erm....... The form was inherited, how do I change them

 

[doylie]

Forget that last one I've just changed the names, However it still does the same. Also I don't know if this matters but some records in the table don't yet contain a subMCGRP. Would this matter or would I be able to just pick the blank space in this case. Sorry.

 

[simongallop]

What is the code that you have for the On Change event of the first combo

 

[doylie]

I've just changed the names to box1 and box2 so the code is

Private Sub box1_Change()
Me.Box2.Requery
End Sub

I've also changed the SQL to suit.

Cheers.

 

[simongallop]

And it still doesn't work? Am now lost!!
Sorry

 

[doylie]

OK, no worries. Thanks for your time.

 

[doylie]

DONE IT

I had to change the bound column from 1 to 2 now it seems to work ok.