enable textboxes if three criterias met in table (1 Viewer)

Miff3436 · Dec 7, 2017

Hi,

I have a table called tbl_Admin, this has 2 fields in it that identify the users of my database, these are "Loginname" & "userpermissions". I am looking for a way to enable and disable certain textboxes on a form depending on the criteria in the userpermissions field.

The db opens on a form called Home, this has an unbound textbox that captures the environ("username"). Is there a way the username i capture in the textbox can look up the "loginname" in tbl_Admin and then look at the "userpermissions" associated to it ?

I did try and look at DLookups but just didn't get anywhere at all

thanks miff3436

Minty · Dec 7, 2017

What did you try? - there is a good syntax guide in the links in my signature.

Ranman256 · Dec 7, 2017

i have userpermissions too.
when the form loads, in the LOAD event, you turn on/off items based on permission:

btnAdmin = userpermissions ="A" (admin)
txtSocSec.visible = userpermissions <> "" (avg user)

etc...

Auntiejack56 · Dec 8, 2017

So, based on Ranman's reply, you could try, in the OnLoad event:
txtMyTextBox.enabled = (nz(Dlookup("userpermissions","tbl_Admin","username = '" & environ("username") & "'"),"") = "ReadWrite")

That looks complicated, but it's the same as:
dim strPermission as string
strPermission = nz(Dlookup("userpermissions","tbl_Admin","username = '" & environ("username") & "'"),"")
if strPermission = "ReadWrite" then
txtMyTextBox.enabled = true
else
txtMyTextBox.enabled = false
endif

Jack

Miff3436 · Dec 8, 2017

Hi Auntiejack56,

That is brilliant and works exactly how I need it. I have adapted the code so it will enable / disable textboxes / Command button on subforms as well.

Code:

Dim strPermission As String
 
strPermission = Nz(DLookup("PermissionType", "tbl_Admin", "OneLondonName = '" & Environ("username") & "'"), "")
If strPermission = "Read Only" Then

Forms![OpenNIDrillDown]![tbl_ESM subform].Form![ESMDescription].Locked = True
Forms![OpenNIDrillDown]![tbl_Assurance_subform].Form![AssuranceDescription].Locked = True
Forms![OpenNIDrillDown]![tbl_Assurance_subform].Form![CmdCloseNI].Enabled = False
 
Else
 
Forms![OpenNIDrillDown]![tbl_ESM subform].Form![ESMDescription].Locked = False
Forms![OpenNIDrillDown]![tbl_Assurance_subform].Form![AssuranceDescription].Locked = False
Forms![OpenNIDrillDown]![tbl_Assurance_subform].Form![CmdCloseNI].Enabled = True
End If
End Sub

For future reference what does the Nz represent in the code before the DLookup ? I have made a slight change to some of the textbox names to suit the form that I am building.

Minty · Dec 8, 2017

The Nz(Expression,ValueIfNull) function provides a value if the expression evaluates to null.

You could simplify you code quite a bit by setting a boolean value instead of the string;

Code:

    Dim bPermission      As Boolean

    bPermission = True

    If Nz(DLookup("PermissionType", "tbl_Admin", "OneLondonName = '" & Environ("username") & "'"), "") = "Read Only" Then bPermission = False

    Forms![OpenNIDrillDown]![tbl_ESM subform].Form![ESMDescription].Locked = bPermission
    Forms![OpenNIDrillDown]![tbl_Assurance_subform].Form![AssuranceDescription].Locked = bPermission
    Forms![OpenNIDrillDown]![tbl_Assurance_subform].Form![CmdCloseNI].Enabled = bPermission

Miff3436 · Dec 8, 2017

Hi Minty,

Thank you for clearing up the Nz question that makes a lot of sense now. Looking at your code that definitely simplifies things, much appreciated.

Thank you all for your time and input on this thread, I will mark as solved.

Thanks miff3436

enable textboxes if three criterias met in table (1 Viewer)

Miff3436

Registered User.

Minty

AWF VIP

Ranman256

Well-known member

Auntiejack56

Registered User.

Miff3436

Registered User.

Minty

AWF VIP

Miff3436

Registered User.

Similar threads

Users who are viewing this thread