Cascading Combos in Subform v9999

[gaccess]

Solved Thanks All

It feels like Ive trawled 10000 posts on this and tried almost as many attempts. Could somebody please look at this as Im bewildered. Not the normal problem. I have this set up just the way I want bar my 2nd combo after choosing the item from dropdown after update the control shows the Primary Key number. And yes i have bound and column count and widths right. (The column widths are set at .5;.5;3cm at the mo to try and let me see what is going on,,, it hasn't)
Thank you in advance

 

[Kiwiman]

Howzit

What do you want it to show? As all your column widths are > 0 when you choose the second combo box it will show the value in the bound column. BTW - did you realise that your qry underlying the second combo box produces a cartesian join (every combination of both tables) asa you do not have a join?

 

[gaccess]

I want the 2nd cbo to show the text value from col 3
I temporarily set the col widths to >0 to try to see what was happening
No I didn't realise that until i read your reply (although it doesnt change the result on the form

So when the cols are set .5;.5;3cm
Choose 1st cbo - options appear, move to 2ndcbo - updated options appear in the drop down including Pk and FK col 1 and 2 as expected
as soon as the choice is made the PK only for that option appears in the cbo. as you move on and choose more options in subsequent rows the PK in the 2ndcbo remain with the correct value as I would hope. Except I want to see the text from the 3rd col. not the PK number

However when the cols are set 0;0;3cm all goes well until you move on to the next row and select 1stcbo and the 1st row 2ndcbo goes blank if you choose a first cbo option that is different to the 1st row 1stcbo

 

[spikepl]

Cascading combos in datasheet/continuous form are tricky, because they share the row source across rows. There is a workaround somewhere on this site - look into sample db's

 

[gaccess]

Hi spikepl wow quick reply , Im just looking at something at the moment to do with superimposing a text box and having a Dlookup sounds odd but if it works
skin your cat whichever way. Ill post more Thanks

 

[gaccess]

OK Heres a copy of another post with 2 options
I tried the 2nd one first must have done something wrong.
looked at the first option to make the primary key the text entry
and it was a five minute fix

Thankyou all

In a continuous form or datatsheet, the combo appears on each row but there
is not a separate RowSource for every row. Therefore the combo will display
as blank if:
a) the display column is not the bound column, and
b) you filter the RowSource so it does not have the value to display.

You cannot change that behavior, but there are a couple of ways to work
around it.

One is not to hide the bound column. Could your Locations table use the
Location_Name as the primary key instead of the hidden ID number? Then the
related table would also use a Text field (for the Location_Name) as its
foreign key. Since the combo does not need a hidden number, it has the value
it needs to display for every row, and so it displays properly even when
filtered.

Another approach is to place a text box over top of the combo. Base the form
on a query that uses your main table and the locations table, so it can
return the location_name field. Bind the text box to this field, and set its
Locked proeprty to Yes so the user can't change it. In its Enter event,
SetFocus to the combo behind it.

What happens is that the text box has the values to show for every row, so
the form looks right. As you tab through the form (or click on the text
box), the combo takes focus, but Access draws the combo in front of the text
box on the current row only. Therefore the fact that the combo is restricted
to the current locations only means that it shows the desired values when
dropped down, but the other rows still display correctly.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org