VBA Validation

[Graham1894]

Hello,

In the form for my Customer Address table I have a toggle button to show if the address is Active or Closed.

In the form for my Equipment table I have the same for the items of plant, a toggle button showing if the item is Active or Closed.

The Equipment table is linked to the Customer Address table via the Customer Address ID.
I cannot just delete the items from the DB due to legislation, I therefore want to put in 2 validations;
1. If someone wants to close an address which still has Active plant, a warning would be displayed saying there is still Active plant at the address, do they want to continue.
2. If they choose to continue any plant at that address that is still Active would be made Closed.

I would prefer to do this via VBA when the Customer Address toggle is clicked and I would appreciate if someone could show me how the code would look, or if there is a better way of doing this then please let me know.

Many thanks

 

[Ranman256]

In the active update event,

Sub chkActive_activeUpdate
  If not chkActive.value then
     MsgBox "you have deactivated this address....."
  End if

 

[Graham1894]

Hi Ranman256

Thank you for the quick response.

Correct me if I’m wrong but this just checks if on update the address has been made Closed, and if so a message box confirms the address has been closed.

What I need it to do is check if any equipment at the address is still active and if so give the option to proceed or not. It may need to be stopped so active equipment can be transferred to another address before the address is closed. Or it could be that we have lost the contract and all the equipment should be closed as well. In this case I want the code to mark all active equipment as closed as the engineers workloads are based on active equipment. As it is at the moment, an address can be closed but if there is equipment at the address which is still marked as active it still shows up in the engineers workload.

So if they proceed the code should run through all the equipment which is shown in a subform of the address details from and change the status of any equipment that is active to closed, but leave closed equipment as closed.