setting combo.rowsource fires subform.current() event

[arnelgp]

i see what you mean.
what i did, just for my experiment No.1, i added the subform Inside another subform.
i added txtID to the "main subform" and set master/child field link to sub-subform:

master: txtID
child: invID

now, on current event of main form, i set txtID of subform to invID (see current event of main form).
changing the rowsource of the unrelated combo does not fire the sub-subform's current event.

run "copy of frmInv" form.

 

[Zakraket]

Yes, that seems to make some difference

but a subform in a subform just to fix this seems a bit ... awkward, just to fix this

At least it (also) works normally when you use a paramquery as recordsource which is a solution for my case

It looks to me this is really unintended, unnecessary (and unwanted) behaviour from MsAccess

 

[MajP]

It looks to me this is really unintended, unnecessary (and unwanted) behaviour from MsAccess

I think there is something else going on, because I tried to recreate this on other forms and was not able. I do not believe this is default, but cannot answer why this is happening.

 

[Zakraket]

I can easily reproduce it now. Did you try my testdb? It's built from scratch so no leftovers from anything and you don't need SQL linked tables, it the same with local Access tables

 

[MajP]

I can easily reproduce it now. Did you try my testdb? It's built from scratch so no leftovers from anything and you don't need SQL linked tables, it the same with local Access tables

I tried to do it with my own database and forms and was unable. I should try importing your tables and build my own forms to see if I can replicate. Maybe it is some kind of corruption because I do not believe this is default behaviour.

 

[Pat Hartman]

As soon as I do, the subform will fire twice on setting a rowsource on the combo

Did you try my suggestion to NOT replace the rowsource? For cascading combos, I use RowSource queries that refer to the previous combo.

Select ... From ... Where SomeField = Forms!myform!previousCombo

Then in the AfterUpdate event of a combo, I requery the dependent combo
Me.cbo2.Requery

 

[Zakraket]

No, because my problem is already solved just by using a parameterquery (based on the SQL view) as the mainform recordsource; with that the subform does not requery anymore when I setv any of the combo's

(select * from * where field = forms!form!ID)

Your suggestion might solve the problem also, but it's much more work at this point. If I would run in a situation where replacing the mainform recordsource would not be possible I will surely try that.

@MajP I can easily reproduce the problem, in my testdatabase (has nothing from the original FE) there is nothing special (the tables a just simple tables with an ID field and one or two extra mockup fields
Don't ask me why, apperently it's the way access works in this case... I think it is some unintentional behaviour for which there is not reason at all it should work this way.

 

[MajP]

@MajP I can easily reproduce the problem, in my testdatabase (has nothing from the original FE) there is nothing special (the tables a just simple tables with an ID field and one or two extra mockup fields
Don't ask me why, apperently it's the way access works in this case... I think it is some unintentional behaviour for which there is not reason at all it should work this way.

As I said, I see it in your database and can replicate it in your database. If I build a database or use one of my existing databases then I cannot replicate that behavior. So I do not think this is a default behavior but only under certain conditions. Maybe there is some obscure setting in Access that I do not know that can cause this.

 

[Pat Hartman]

No, because my problem is already solved just by using a parameterquery (based on the SQL view) as the mainform recordsource; with that the subform does not requery anymore when I setv any of the combo's

That is exactly what I suggested. The query for RowSource 2 references the combo1, the query for RowSource 3 references the combo2, etc.