combobox on tabular form (1 Viewer)

[CJBIRKIN]

Hi

Not sure what's going on here.

I have a tabular form with 2 comboxes CmboX and CmboY.

CmboX gets its row source from TBL_X and CmboY from Tbl_Y.

In the rowsource query for the CmboY i have put the criteria that if the value in cmboX is <=3 then display only records 1,2 and 3 from TBL_Y.

Then on the afterupdate event of CmboX i have told it to requery CmboY.

There are 2 problems

1 (which i don't think is solvable) all of the cmboY's are requeried not just the record i am working on. This just means it's going to a tinsy bit slower.

2 After the requery no data is displayed in the cmboY's for the previously completed records that don't have the same value as the one thats being created!.

The controlsource and the rowsource are 2 seperate things so why should this be?


Any clues?

Cheers

Chris

 

[Fizzio]

When you say tabulated form, I assume you mean continuous forms (not your mistake just so I'm clear)
The cascasing combo is great except on a continuous form. The reason for this is that you requery the combo and filter the recordset. All the combos are identical except for the data that they display from the underlying bound field.
The reason you are seeing blank cells is that when you requery comboY, it recreates the rowsource for every instance of that combo, on every record and if the underlying value stored is not in the recordset of the combo, a blank will be seen

eg
Filtered ComboY has values 1,2,3.

Record No  Value  Combo Shows
1           1          1
2           2          2
3           5          Nothing!
4           7          Nothing!

This is unavoidable and because the controlsource and rowsource are different is the reason for the problems.
The only fix is to use textboxes, move to a single form format or live with it!

 

[CJBIRKIN]

Hi Fizz

That's just what i hoped you weren't going to say !. I guessed as much from how it was behaving but you can never be sure.

Well i guess i'll go with the single form option for now.

Thanks for explaining it so clearly.


Cheers

Chris


PS i did mean continououou... i just could stop spelling it!

 

[Fizzio]

PS i did mean continououou... i just could stop spelling it!

Thought you did - just wanted you to try to type it , just like asking my wife to say phenomenon - ends up being "phenomenomemom"

 

[Rich]

Why not store the selected values ?

 

[CJBIRKIN]

Hi Rich

Not sure what you mean by store.

I need the values selected to be entered into the table, which is what happens. The data just doesn't display correctly on the form.

Can you explain further pls.

Thanks

Chris

 

[Rich]

Does table Y have a PK, is that the value you are displaying, is it the bound column?

 

[CJBIRKIN]

Hi Rich


TBL_Y does have a pk and that is the data i am trying to store in TBL_Z, and yes it is the bound column, but it's not the data i am trying to display to the user, that is the second column of TBL_Y

Like

Select data from TBL_X to display in Cmbo_X

0cm,2.5cm = 1(bound), "Before" etc


Select data from Cmbo_X, store in TBL_Z.Field_X

Filter Rowsource of Cmbo_Y using Cmbo_X

if Cmbo_X = 1 then Cmbo_Y = 0cm,2.5cm = 3(bound), "End"

Select data from Cmbo_Y, store in TBL_Z.Field_Y

Does that help??

Chris

 

[Fizzio]

Rich, you've got me puzzled as well
Do you have some mysterious secret master plan?

 

[Rich]

Sadly you have to display the pk field, ie bound column, but you can add another textbox, combo to display the value from the table. You can also paste a label over the second combo to hide the pk when it no longer has the focus
hth

 

[CJBIRKIN]

Hi Rich

Well i didn't know that (surprised? I'm not!). I'll have to give that a try. I wonder why it only works with the bound column displayed?

Cheers

Chris

 

[Rich]

and it has to be big enough to display the value, .005 cm won't work

 

[Fizzio]

I get you now.