Access: Modify VBA code from an On Open to On Click

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,732
Office Version
2016
Platform
Windows
I have code that I successfully run for one of my forms "On Open". I am trying to modify this for another form where the code is run when I click a button on the form

Code:
Private Sub Form_Open(Cancel As Integer)
    On Error GoTo Error_Handler
 
    If Nz(DLookup("UserName", "tbl_Users", _
                  "[Object_Name]='" & Me.Name & "' AND [UserName]='" & CurrentUser() & "'"), "") = "" Then
        Cancel = True
        MsgBox "You are not authorized to add a new supplier"
    End If
 
Error_Handler_Exit:
    On Error Resume Next
    Exit Sub
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Form_Open" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub
I want to run the same code (this checks a table to see if the user has permissions)
when I click Button command270. If it finds a match I want it to change a fields setting

Digit_1.Enabled = False
Digit_1.Locked = True
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,732
Office Version
2016
Platform
Windows
Got it to work

Code:
Private Sub Command270_Click()
'Check to see if user has permission, if so unlock the fields
On Error GoTo Command270_Click_Err

 
    If Nz(DLookup("UserName", "tbl_Users", _
                  "[Object_Name]='" & Me.Name & "' AND [UserName]='" & CurrentUser() & "'"), "") = "" Then
        MsgBox "You are not authorized to Edit/Modify or add commodities"
        
    Else
    
        Digit_1.Enabled = True
        Digit_1.Locked = False

        Digit_2.Enabled = True
        Digit_2.Locked = False

        Digit_3.Enabled = True
        Digit_3.Locked = False

        Digit_4.Enabled = True
        Digit_4.Locked = False

        IHS.Enabled = True
        IHS.Locked = True

        Definition.Enabled = True
        Definition.Locked = False

        Description.Enabled = True
        Description.Locked = False
        
        Examples.Enabled = True
        Examples.Locked = False
        
    End If


Command270_Click_Exit:
    Exit Sub

Command270_Click_Err:
    MsgBox Error$
    Resume Command270_Click_Exit
 

End Sub
Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,102,265
Messages
5,485,760
Members
407,512
Latest member
PearceK

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top