Reseting to "nothing" chained commands in which LimitedToList=True and AllowZeroLength=False

[Amir]

Hello!



I would be grateful if you help me with the following questions.



I have a form named frmReceiptEvents, in which there are the following
controls:

(There are more, but I will describe only the relevant ones.)



1. ReceiptID (This is the primary key)

2. ReceiptSupplier (This is a textbox command that cannot be changed, and is
dependant on the ReceiptID command.)

3. DeliveryPageID (Combo Box dependant on the receipt supplier (not the
control), according the value in ReceiptID control)

4. OrderID (Combo Box dependant on the receipt supplier, according the value
in ReceiptID control)

5. MoreControls (Some of them dependant on the values in the ReceiptID
command,

and others are "third-level" linked, and are dependant on the values in
commands that are

dependant on the values in the ReceiptID command.)



Both the DeliveryPageID and the OrderID fields are dependant on the
ReceiptSupplier data (existing in the ReceiptID command).

They are both linked in the same way. To emphasize, I will write down the
way the DeliveryPageID control is linked:

1. DeliveryPageID RowSource is:

SELECT tblDeliveryPagesData.DeliveryPageID,
tblDeliveryPagesData.DeliveryPageNumber,
tblDeliveryPagesData.DeliveryPageSupplier FROM tblDeliveryPagesData WHERE
(((tblDeliveryPagesData.DeliveryPageSupplier)=Forms!frmReceiptEvents!Receipt
Supplier)) ORDER BY tblDeliveryPagesData.DeliveryPageNumber;

2. The ReceiptID command has an AfterUpdate event which runs a requery
command on both the commands DeliveryPageID and OrderID (and more controls,

some of them "third-level" chained, as I wrote).



If I change the ReceiptID value to a value which carries a change on the
ReceiptSupplier command,

the choices in the DeliveryPageID and OrderID Combo Boxes do change, but the
problem is that if

there is already text or value in these controls, the text or value remains
as it was before the change of the supplier.

Access doesn't check whether the existing value in these controls suits the
new supplier, and this way

it is possible that if a user changes an existing record, he can mistakely
create or update an existing record

to a state where the supplier of DeliveryPageID is different from the
supplier of the ReceiptID.

A check is made only if the user edits the text in the command, and then
Access produces a "Not in list" event.



What I want to do after an update of the ReceiptID command is this:




If and only if the supplier did changed due to the change of the Receipt ID:

Delete the text in the DeliveryPageID and OrderID combo boxes, and requery
all the combo boxes and the chained combo boxes.

I want them to act like new "fresh" controls.



Please mind the following notes:

1.. Some of the commands I want to update are Texts, others are Numbers.
2.. There is more than two combo boxes depending on each other. As I
wrote, There are third level chained combo boxes that need to be changed due
to the change of the supplier. I want ALL these combo boxes to delete their
texts/values.
3.. Most of the controls I want to update have the following properties:
Required = Yes

AllowZeroLength = No.



I have tried to put the following code in the AfterUpdate event of the
ReceiptID control, but I received an error message saying I can't put the
value "" because of validation rules:



[Forms]![frmReceiptEvents]![ReceiptSupplier].Requery

[Forms]![frmReceiptEvents]![DeliveryPageID].SetFocus

[Forms]![frmReceiptEvents]![DeliveryPageID].Text = ""

[Forms]![frmReceiptEvents]![DeliveryPageID].Requery

[Forms]![frmReceiptEvents]![DeliveryPageID].Text =
[Forms]![frmReceiptEvents]![DeliveryPageID].ItemData(0)



I tried solving it by adding the the following line before all these lines:



[Forms]![frmReceiptEvents]![DeliveryPageID].LimitToList = False



and the following line after these lines:



[Forms]![frmReceiptEvents]![DeliveryPageID].LimitToList = True



but then I got an error message saying that The search key was not found in
any record.





Thank you very much for reading!