Solved Hiding buttons when the update query runs (1 Viewer)

Cheez

New member
Local time
Today, 07:00
Joined
Mar 6, 2022
Messages
17
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?

Code:
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

I’m here to help
Staff member
Local time
Today, 05:00
Joined
Oct 29, 2018
Messages
21,358
You can either pass a reference to the calling form or pass its name to the function.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:00
Joined
Feb 28, 2001
Messages
27,001
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.

Code:
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

Registered User.
Local time
Today, 15:00
Joined
Dec 7, 2018
Messages
481
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:
Code:
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
 
Last edited:

Cheez

New member
Local time
Today, 07:00
Joined
Mar 6, 2022
Messages
17
Is there something wrong with that Gasman?
 

moke123

AWF VIP
Local time
Today, 08:00
Joined
Jan 11, 2013
Messages
3,852
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

Immoderate Moderator
Staff member
Local time
Today, 07:00
Joined
Feb 28, 2001
Messages
27,001
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

New member
Local time
Today, 07:00
Joined
Mar 6, 2022
Messages
17
Ok I understand, Sorry was being impatient!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:00
Joined
Feb 28, 2001
Messages
27,001
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

New member
Local time
Today, 07:00
Joined
Mar 6, 2022
Messages
17
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

New member
Local time
Today, 07:00
Joined
Mar 6, 2022
Messages
17
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.

Code:
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
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:00
Joined
Feb 28, 2001
Messages
27,001
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

AWF VIP
Local time
Today, 08:00
Joined
Jan 11, 2013
Messages
3,852
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

Immoderate Moderator
Staff member
Local time
Today, 07:00
Joined
Feb 28, 2001
Messages
27,001
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.)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:00
Joined
Feb 19, 2002
Messages
42,981
Here are some samples that I use in all my databases. When I want to create common code that references controls, I use the same control name on every form.

To use them --

Call CommonClose(Me)

Code:
Public Sub CommonClose(frm As Form)
On Error GoTo ErrProc
    If bForceClose = True Or bSaveOK = False Then
        Exit Sub        'do not run close code
    End If
    If frm.Dirty Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    If IsNull(frm.OpenArgs) Then
        DoCmd.OpenForm "Switchboard"
    Else
        DoCmd.OpenForm frm.OpenArgs
    End If
    
    bForceClose = False     'reset variable
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 2102   'bad open args form name
            Resume ExitProc
        Case 2455   'happens for A2007 when unbound form references the Dirty property
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume ExitProc
    End Select
End Sub
Public Sub CommonReturn(frm As Form)
On Error GoTo ErrProc
    If frm.Dirty Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
    DoCmd.Close acForm, frm.Name
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 3709, 3021, 2501  'caused when save is cancelled
            Resume ExitProc
        Case 2455   'happens for A2007 when unbound form references the Dirty property
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume ExitProc
    End Select
End Sub

Public Sub CommonSave(frm As Form)
On Error GoTo ErrProc
    If frm.Dirty Then
        DoCmd.RunCommand acCmdSaveRecord
    End If
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 3709, 3021, 2501, 3071  'caused when save is cancelled
            Resume ExitProc
        Case 2455   'happens for A2007 when unbound form references the Dirty property
            Resume Next
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume ExitProc
            Resume Next
    End Select
End Sub


Here's another ---

Call LockControls(Me, True) --- True to lock, False to unlock
Code:
Public Sub LockControls(frm As Form, bLock As Boolean)
    Dim ctl As Control
    For Each ctl In frm.Controls
    Select Case ctl.ControlType
        Case acTextBox, acComboBox, acListBox, acCheckBox
            Select Case ctl.Tag
                Case "NoLock"
                    ctl.Locked = False
                Case "Lock"
                    ctl.Locked = True
                Case Else
                    ctl.Locked = bLock         'toggle locks
            End Select
        Case acOptionGroup, acOptionButton, acCheckBox      ''not working
             Select Case ctl.Tag
                Case "NoLock"
                    ctl.Locked = False
                Case "Lock"
                    ctl.Locked = True
                Case Else
                    ctl.Locked = bLock         'toggle locks
            End Select
        Case acCommandButton
            Select Case ctl.Tag
                Case "NoLock"
                    ctl.Enabled = True
                Case "Lock"
                    ctl.Enabled = False
                Case Else
                    ctl.Enabled = Not bLock         'toggle locks
            End Select
    End Select
Next ctl
Set ctl = Nothing
End Sub
 

LarryE

Active member
Local time
Today, 05:00
Joined
Aug 18, 2021
Messages
562
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":
Code:
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:00
Joined
Feb 19, 2002
Messages
42,981
Rather than affect all open forms, you might want to use a targeted approach and reference only ONE specific form.
 

Users who are viewing this thread

Top Bottom