Careca
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
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
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.
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
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 TRIALMembers 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.
Open in new window
toOpen 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