Link to home
Start Free TrialLog in
Avatar of Careca
CarecaFlag for Brazil

asked on

Problem with editing cascading dependent Combo boxes in continuous subforms

I have a form with 2 (continuous) subforms.
Both subforms have 2 cascading (dependent) combo boxes which run independently very well by utilizing a parameter and union query for the dependent combo
SQL Below:
First Combo "CbReqZone"
SELECT tb_Zones.ZoneID, tb_Zones.ZoneCode, tb_Zones.Description
FROM tb_Zones
ORDER BY tb_Zones.[ZoneCode];

2nd Combo "CbReqLocation"
PARAMETERS CbReqZone Byte;
SELECT  LocationID, Location, ZoneID, CbReqZone=ZoneID As InZone  FROM tb_Locations  UNION SELECT Null, '————————————', CbReqZone, False  FROM tb_Locations
ORDER BY InZone, Location;

They are synchronized with code in theire After Update events as follows:

1st Combo:
Private Sub CbReqZone_AfterUpdate()
Me.CbReqLoc.Value = 0
Me.CbReqLoc.Requery
End Sub

2nd Combo:
Private Sub CbReqLoc_AfterUpdate()
If IsNull(Me.CbReqLoc) Or Me.CbReqLoc = "" Then 'No action required
Else
    Me.CbReqZone = Me.CbReqLoc.Column(2)
    Me.CbReqLoc.Requery
End If
End Sub

Everything works very well in the first subform but the 2nd subform won't allow an existing record to be edited by the combo boxes.
The error I get is Update or Cancel Update without addnew or edit.
Just acn't understand it happens in form and not the other, both are doing exectly the same but for different fields in different underlying tables.

Here is the 2nd set of combos and after update events for the 2nd subform

1st Combo "CbDestinZone"
SELECT tb_Zones.ZoneID, tb_Zones.ZoneCode, tb_Zones.Description
FROM tb_Zones
ORDER BY tb_Zones.[ZoneCode];

2nd Combo "CbDestinLoc"
PARAMETERS CbDestinZone Byte;
SELECT  LocationID, Location, ZoneID, CbDestinZone=ZoneID As InZone  FROM tb_Locations  UNION SELECT Null, '————————————', CbDestinZone, False  FROM tb_Locations
ORDER BY InZone, Location;

Synchronized by After Update events:

1st Combo:
Private Sub CbDestinZone_AfterUpdate()
Me.CbDestinLoc.Value = 0
Me.CbDestinLoc.Requery
End Sub

2nd Combo:
Private Sub CbDestinLoc_AfterUpdate()
If IsNull(Me.CbDestinLoc) Or Me.CbDestinLoc = "" Then ' No Action Required
Else
    Me.CbDestinZone = Me.CbDestinLoc.Column(2)
    Me.CbDestinLoc.Requery
End If
End Sub

I have gone over and over over with this I am at my wits end, please can anyone help?
Thanks,
Careca
Avatar of Dale Fye
Dale Fye
Flag of United States of America image

My guess is that the recordset associated with the 2nd subform is not updateable.  Open that query and determine whether you can make changes.  If not, then try changing:

Private Sub CbDestinZone_AfterUpdate()
Me.CbDestinLoc.Value = 0
Me.CbDestinLoc.Requery
End Sub

Open in new window

to
Private Sub CbDestinZone_AfterUpdate()
Me.CbDestinLoc.Requery
End Sub

Open in new window

Actually, I'm not sure why you would attempt to set the value of the combo box to 0 (in either of the combo1_AfterUpdate events.  I can understand the requery, but setting the value to zero (especially before the requery) makes no sense.

Dale
Avatar of Careca

ASKER

Dale thanks for the comment but that is not the issue. Explanation on setting value to 0.
If someone makes all their selections correctly then fine, but if someone goes back to the record and changes the parent combo, I need it to reset the child combo to blank. Otherwise someone could change the parent combo and orphan the child combo an data entry would be corrupted.
Understood, but I generally do that after the requery.

However, that does not address why the 2nd code doesn't work, and my guess is that the recordset in that subform is not updateable.

Dale
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.