Solved Hiding buttons when the update query runs

[Cheez]

ok I have Form with buttons I'd like to hide after an update query runs.
I want to use the same code on another form; I do not want to repeat the code; I know that I can use the code in Public function and call the function when the command button is clicked.
My problem is that the forms have different names and I am using the Me. in the code. How can I overcome these two issues? (I got this part solved)
how would I refence the button in the module?

form1
Private Sub cmdSQL_Click()

RunUpdate Me.CustomerID
Forms!CustomerF.Refresh

End Sub

Form2
Private Sub cmdSQL_Click()

RunUpdate Me.CustomerID
Forms!Customers.Refresh

End Sub

module
Public Sub RunUpdate(ByVal Cid)

    Dim A As String
   
DoCmd.SetWarnings False

A = "UPDATE CustomerT Set CustomerT.IsActive = true " _
    & "WHERE CustomerT.CustomerID=" & Cid & ""
   
DoCmd.RunSQL A
DoCmd.SetWarnings True

End Sub

 

[theDBguy]

You can either pass a reference to the calling form or pass its name to the function.

 

[The_Doc_Man]

The typical method for this is to pass in the form as a formal argument by reference and the name of the control as a string. I'm going to overspecify this for clarity, though the default for passing an object IS by reference.

Public Sub CtlAfterQry( ByRef frm AS Access.Form, strCtlName AS String, ynStatus as Boolean )

frm.Controls( strCtlName ).Transparent= NOT ynStatus
frm.Controls( strCtlName ).Enabled = ynStatus

End Sub

...

<<<run your update query>>>
   CtlAfterQry Me, "cmdButtonOfInterest", FALSE
...

Put the sub in a general module so you can use it on other forms. Note for this you must call it once per control to be "twiddled."

 

[Eugene-LS]

My problem is that the forms have different names and I am using the Me. in the code.

If you use the same code twice (or more) for multiple forms, you should write a public procedure in an external module.
For example:

Spoiler: Example:

Public Sub YourNameOfProcedure(frm As Form)
'The procedure is called from two forms : "Form01" and "Form02"
'----------------------------------------------------------------------------------------------
On Error GoTo YourNameOfProcedure_Err

    If frm!Conrol01.Value = True Then
        frm!Conrol02.Enabled = False
    Else
        frm!Conrol02.Enabled = True
    End If


YourNameOfProcedure_End:
'    On Error Resume Next
'    Err.Clear
    Exit Sub

YourNameOfProcedure_Err:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in Sub" & _
           "YourNameOfProcedure", vbCritical, "Error!"
    'Debug.Print "YourNameOfProcedure_Line: " & Erl & "."
    Err.Clear
    Resume YourNameOfProcedure_End
End Sub

 

[Eugene-LS]

ByRef frm AS Access.Form

ByRef is by default - isn't it?

 

[Gasman]

Crossposted? https://www.accessforums.net/showthread.php?t=78670

 

[Cheez]

Is there something wrong with that Gasman?

 

[moke123]

Is there something wrong with that Gasman?

Yes and No. You should at least include a link to the crosspost so that people dont waste their time helping you when you've already been given a solution at the other post.

 

[The_Doc_Man]

ByRef is by default - isn't it?

You are correct, but did you note that I stated I was going to overspecify? Yes, it is the default for objects, but I wanted to be unequivocal in which argument needed to be by reference.

 

[Cheez]

Ok I understand, Sorry was being impatient!

 

[The_Doc_Man]

Is there something wrong with that Gasman?

You are new here and not accustomed to our ways yet. It is a forgivable sin.

It is considered bad manners to have what we call a cross-post, meaning the same or similar post in multiple places on this or other forums. We are all volunteers here and don't get paid anything for answering questions. We are not supported by a membership fee. We help others to be friendly. We help others to "pay it forward" for the help we received. When you cross-post, you increase the odds that you will get an answer but you ALSO increase the odds that one of us, not realizing that you silently cross-posted, gives you an answer you already had. That wastes the time of the responder, who could have answered someone else.

Now there are mitigating circumstances sometimes - such as having posted at site X and gotten no responses after a couple of days. But if we have a link to the cross-post, we will be able to know that you have an answer. Which means we can go on about our business with someone else who doesn't have an answer yet. And that doesn't waste our time.

New members do this now and then. We see it, we know it can happen, and we can forgive an occasional lapse. We merely ask

(a) if you are going to do it, at least notify us of the cross-post or - better still - provide a link to it, and

(b) be patient with us - and the other site - because sometimes we need to research the question or think a while. Don't rush to cross-post just because you didn't get an immediate answer at site X.

Note that some of us help at other sites as well so will recognize the alternate posting, which is how you get caught.

Does this place a black mark on you? Yes and no. If you don't silently cross-post any more, we quickly forget you did it and respond as quickly to questions as we can. Silently cross-post often and we will start commenting on it, which will drive away other responders (from multiple forums) who don't want to bother with time-wasters.

 

[Cheez]

You are new here and not accustomed to our ways yet. It is a forgivable sin.

It is considered bad manners to have what we call a cross-post, meaning the same or similar post in multiple places on this or other forums. We are all volunteers here and don't get paid anything for answering questions. We are not supported by a membership fee. We help others to be friendly. We help others to "pay it forward" for the help we received. When you cross-post, you increase the odds that you will get an answer but you ALSO increase the odds that one of us, not realizing that you silently cross-posted, gives you an answer you already had. That wastes the time of the responder, who could have answered someone else.

Now there are mitigating circumstances sometimes - such as having posted at site X and gotten no responses after a couple of days. But if we have a link to the cross-post, we will be able to know that you have an answer. Which means we can go on about our business with someone else who doesn't have an answer yet. And that doesn't waste our time.

New members do this now and then. We see it, we know it can happen, and we can forgive an occasional lapse. We merely ask

(a) if you are going to do it, at least notify us of the cross-post or - better still - provide a link to it, and

(b) be patient with us - and the other site - because sometimes we need to research the question or think a while. Don't rush to cross-post just because you didn't get an immediate answer at site X.

Note that some of us help at other sites as well so will recognize the alternate posting, which is how you get caught.

Does this place a black mark on you? Yes and no. If you don't silently cross-post any more, we quickly forget you did it and respond as quickly to questions as we can. Silently cross-post often and we will start commenting on it, which will drive away other responders (from multiple forums) who don't want to bother with time-wasters.

ty for explaining this to me and again sorry I've wasted anybody's time here. That was not intended!

 

[Cheez]

The typical method for this is to pass in the form as a formal argument by reference and the name of the control as a string. I'm going to overspecify this for clarity, though the default for passing an object IS by reference.

Public Sub CtlAfterQry( ByRef frm AS Access.Form, strCtlName AS String, ynStatus as Boolean )

frm.Controls( strCtlName ).Transparent= NOT ynStatus
frm.Controls( strCtlName ).Enabled = ynStatus

End Sub

...

<<<run your update query>>>
   CtlAfterQry Me, "cmdButtonOfInterest", FALSE
...

Put the sub in a general module so you can use it on other forms. Note for this you must call it once per control to be "twiddled."

frm.Controls( strCtlName ).Transparent= NOT ynStatus
so this would be
Form1.cmdSQL.Visible=true ynStatus

 

[Eugene-LS]

Yes, it is the default for objects, but I wanted to be unequivocal in which argument needed to be by reference.

Understood. For the " Beauty of the Code "

 

[The_Doc_Man]

frm.Controls( strCtlName ).Transparent= NOT ynStatus
so this would be
Form1.cmdSQL.Visible=true ynStatus

Yes, unfortunately command buttons and other kinds of controls have a "reverse" visibility property from each other, so everything else is .Visible but command buttons are .Transparent = the opposite of .Visible. Go figure. (And if you DO figure it out... tell me.)

 

[moke123]

Yes, unfortunately command buttons and other kinds of controls have a "reverse" visibility property from each other, so everything else is .Visible but command buttons are .Transparent = the opposite of .Visible. Go figure. (And if you DO figure it out... tell me.)

I never encountered using .transparent for command buttons so I did a little test. When setting a command button to transparent it will still fire when you click it (if you can find it) while setting it to .visible = false it will not fire.

 

[The_Doc_Man]

I never encountered using .transparent for command buttons so I did a little test. When setting a command button to transparent it will still fire when you click it (if you can find it) while setting it to .visible = false it will not fire.

Two comments:

1. You are correct - transparency doesn't disable any object. Which is why in the sample code earlier I included "enable" as something to be turned on or off as needed.

2. As to .Transparent vs. .Visible, some online references don't include .Visible as a command-button property. I just found one dated 2022 that says it IS a valid property, but when I originally answered the question, another linked article did NOT include .Visible as a command button property, it only used .Transparent. I'm wondering if this changed from Access 2010 to more recent versions? I remember having this issue in my Navy project in 2013 in which there were a LOT of command buttons (to launch various reports among other things) and I had to use .Transparent back then because I did not have .Visible available to me. (And I WANTED it - but it wasn't there.)

 

[LarryE]

You can use the following code to select open forms, loop through their controls, and set their visibility property (or any property). Let's say one of my open forms is named "Reports Form" and I want to hide a command button named "Close". I can call the following Public Function I named "HideButtons":

Public Function HideButtons()
On Error GoTo HideButtons_Error
Dim Frm As AccessObject
Dim Selected As Form
Dim ActiveCntrl As Control
For Each Frm In Application.CurrentProject.AllForms
    With Frm
       If .IsLoaded And Frm.Name = "Reports Form" Then
           DoCmd.SelectObject acForm, Frm.Name
            Set Selected = Application.Screen.ActiveForm
            'MsgBox Selected.Name
            For Each ActiveCntrl In Selected.Controls
                If ActiveCntrl.ControlType = acCommandButton And ActiveCntrl.Name = "Close" Then
                    'MsgBox ActiveCntrl.Name
                    ActiveCntrl.Properties("Visible") = False
                End If
            Next
        End If
    End With
Next
Exit Function
HideButtons_Error:
DoCmd.CancelEvent
MsgBox Err.Description
'Resume Next
Exit Function

Of course, you need to modify it to fit your requirements, but this is how to select any open form and access its controls and control properties.
I hope this can help you.

 

[Cheez]

Thank you all for you input on this. I'm was able to get it work.

Private Sub cmdSQL_Click()

RunUpdate Me.CustomerID
Buttons Me, "a1", False
Forms!CustomerF.Refresh

End Sub

Private Sub cmdSQL_Click()

RunUpdate Me.CustomerID
Buttons Me, "a1", False
Forms!CustomerT.Refresh

End Sub


 Option Compare Database
 
Public Sub RunUpdate(ByVal Cid)

    Dim A As String
    
DoCmd.SetWarnings False

A = "UPDATE CustomerT Set CustomerT.IsActive = true " _
    & "WHERE CustomerT.CustomerID=" & Cid & ""
    
DoCmd.RunSQL A
DoCmd.SetWarnings True

End Sub

Public Sub Buttons(ByRef frm As Access.Form, strCtlName As String, ynStatus As Boolean)

frm.Controls(strCtlName).Transparent = Not ynStatus
frm.Controls(strCtlName).Enabled = ynStatus

End Sub

Now if there's more than one button to be hidden, would I do this or is there another way?

Buttons Me, "a1", False
Buttons Me, "a2", False
Buttons Me, "a3", False

 

[moke123]

Now if there's more than one button to be hidden, would I do this or is there another way?

There's always another way.

That is simple and will work just fine.