You could do something like:


Private Sub MarkCustomerInactive(TableName as string, ID as long)

     ' Usage: Button_Click() - MarkCustomerInactive "CustomerT", 15
     CurrentDB.Execute("UPDATE " & TableName & " SET isActive = FALSE WHERE CustomerID = " & ID)

End Sub


Or you could put all the relevant table names in an array and loop through them:


Private Sub MarkCustomerInactive()

     Dim MyTables as Variant, i as long

     MyTables = Array("CustomerT", "EstimateT", "ContactT", "InvoiceT")

     For i = 0 to ubound(MyTables) - 1

          CurrentDB.Execute("UPDATE " & MyTables(i) & " SET isActive = FALSE WHERE CustomerID = " & CustomerID)

     next

End Sub


Hi James, I hope you realize that deleting invoices may skew your data when analyzing, or doing taxes etc. John

Hi Donald. Nice usage for an array. Thanks for sharing.

Thanks guys for the Help. Donald, I will try this out and John you are right, so I will not mark the Invoices.

Hey Guys, I receiving a Error Message.

Have you hovered over "ClientID" in that same part to verify it exists?

Possibly add: if isnull(ClientID) then exit sub

at the top of the procedure in case it's a blank record ... Just in case to add an extra layer unless the button isn't available if there is no client id

Hi Darrin,

The reason for the "-1" in the For/Next loop is that Arrays are zero based. So the loop starts with i = 0 and gives us:
"UPDATE PropertyT SET"...
Then the next increments i to 1 and we get:
"UPDATE ContactT SET"...
The next then increments it to 2. Without the "-1" we would get and error because there is no index 2 in the Array.

For a better explanation: Arrays

Hi Donald

Didn't work that way for me, I tried the code in Blank Templet and removing the -1 to Updated ContactT and CustomerT isActive no error?.

I program in vba and java I know all about arrays.

Hey Harris, after removing the -1 for VBA Code. I still receiving the the same Error.

Hi James

Of course you have to fix the error first, I produced the exact same error with a spelling mistake.
I changed CustomerID to CustomeID 3061 error

Try to put a number where ClientID is?

And did you try what Donald suggested, hope you figure it out.

James
I would suggest moving the text into a string and showing it in a msgbox before trying to execute like:

    

Details

Dim S as String
     For i = 0 to ubound(MyTables) - 1
          S ="UPDATE " & MyTables(i) & " SET isDeleted= TRUE WHERE ClientID= " & ClientID
          msgbox S
          CurrentDB.Execute(S)
Copy

This would show exactly what is not giving enough parameters.

Hey Donald, I figured out the Problem was, that I had to Declare what ID was:

Dim MyTables As Variant, i As Long

    ID = Forms!ClientListF!ClientID
    
    MyTables = Array("PropertyT", "ContactT")
    
    For i = 0 To UBound(MyTables)
       CurrentDb.Execute "UPDATE " & MyTables(i) & " _
            SET Status = 'Archived', IsDeleted = True WHERE ClientID= " & ID
      
    Next

Glad you figured it out!

I'm guessing you've seen enough of Richard's videos to have heard, "Debug - Compile, Once in a While"

As long as you have "Option Explicit" at the top of your VBA, the compiler will catch undeclared variables like that and alert you.

I added the compile, comment, and uncomment buttons to my VBE toolbar just to save those few extra steps of clicking on the menu to get to them, lol. Makes it much easier to prevent, or even you don't catch, find errors that creep in when you're typing.

---Don