Vba code for enable of button based on login id and selected yes/no

[Charles CL]

Dear All,



Table [user]
It has fields of UserID and UserName (same as laptop username)

Table
[ListofForm]
It has fields of FormID, FormType and UserName(Yes/No)

Form [AccessForm]
It has a text box display the UserName based on laptop username and
Buttons with FormType.

A form of
[ListofForm] created for admin to select or deselect form that can be accessed by the user from each field of UserName(Yes/No).

Main menu is the [AccessForm] which display the UserName at the textbox,
I want the button in the [AccessForm] enable based on the UserName
and at the same time it is also based on the selection of Yes/No which the field name is same as the UserName.

Please advice how to do this.
Thanks.

[Micron]

Consider putting the ListOfForm.FormID as a tag value on each applicable command button on the menu form. On form opening, loop through the controls looking for Type acCommandButton and get the tag value. Look up the userID and FormID in ListOfForm and get the yes/no value for that user-formID combination. If yes, enable the control; if no, disable. Maybe someone else has a more elegant idea...

[RuralGuy]

This can all be tested in the OnLoad event of the form or maybe you want to use the Current Event of the form.

[RuralGuy]

@Micron - I had some trouble understanding the problem. You may have decoded it properly.

[Charles CL]

Thank you for the rapid solution.

May you advice for the vba code based on the current situation?
I'm not familiar with vba code.

[Charles CL]

Thank you for the advice.

[RuralGuy]

@Micron - You take it and I'll just watch.

[RuralGuy]

It is now 3:14 AM in Kuala Lumpur, Malaysia and I believe the OP may have gone to bed.

[Micron]

Thank you for the rapid solution.

May you advice for the vba code based on the current situation?
I'm not familiar with vba code.

If you can go to design view of the form, ensure you can see the property sheet, and for each command button that opens a form, put the formId (from your table) on each of them in the tag property line on the property sheet. For example, if the cmdCustomer button opens the customer form, and that form's ID in your table is 6, assign 6 to the command button tag property. After that, you will either have to explain how I get the userID and find their yes/no value for a form or you will have to post a zipped copy of the db in 2007 format (do a Save As) after you do this setup work. You can remove/replace sensitive information if need be but be sure to give me something to work with.

[Charles CL]

If you can go to design view of the form, ensure you can see the property sheet, and for each command button that opens a form, put the formId (from your table) on each of them in the tag property line on the property sheet. For example, if the cmdCustomer button opens the customer form, and that form's ID in your table is 6, assign 6 to the command button tag property. After that, you will either have to explain how I get the userID and find their yes/no value for a form or you will have to post a zipped copy of the db in 2007 format (do a Save As) after you do this setup work. You can remove/replace sensitive information if need be but be sure to give me something to work with.

Please find the attachment as below.
TESTING.zip

thanks

[Micron]

I cannot open your file. If you saved it as a version 2007 file as instructed, do a compact & repair operation on it and upload it again with a different name. Your version may have a different method of doing this than what I'm used to

http://www.accessdatabaserepair.com/...ase-format.htm

[Charles CL]

I cannot open your file. If you saved it as a version 2007 file as instructed, do a compact & repair operation on it and upload it again with a different name. Your version may have a different method of doing this than what I'm used to

http://www.accessdatabaserepair.com/...ase-format.htm

I'm using MS Access 2010 and it can be used for version 2007 too. I had compact and repair operation on it.
Hopefully you can get it.

TESTING2.zip

Regards

[Micron]

Still can't open it. Based on your response, I don't think you understand that I need a 2007 version. The fact that you can open 2007 with your 2010 version does not help me. I also suspect you didn't follow the link that shows how to do this because the reason for having to do it was covered there. If it is a 2007 version, then there is something else wrong with the db, but I suspect not since you say you compacted/repaired it successfully.

[Charles CL]

To all,

As per earlier clarified, i had gone through all steps and yet it still came out of the same result.
Thank you for some of the experts here who had helped me to clarified it too. I'm truly appreciate it.

Anyway, i had successfully create a mdb file.

https://drive.google.com/file/d/0B5F...ew?usp=sharing

I'm sorry for my late to reply as i were not around last week and for the confusion to Micron.
I'm still looking forwards of help from Micron and any expert here.

[Micron]

Code I wrote is below (so that members don't have to download the db again - at end of post). You should be able to dump it into the On_Load event for form formSubMenu1 in your later version of this db. Create the event from the property sheet while in design view, then paste in the code. A more elegant way would be to pass the form to a function so you could use it against several forms, but I believe that's a bit too advanced for you to maintain at this time. Also, some notes (please read carefully):

turn on "Require Variable Declaration" (Option Explicit)!
there were more staff id's than user id's - not sure how that would affect my efforts
there was no connection between form user name textbox and table ID's so I had to add a form control and query for it
changed all tags on formSubMenu1 to jive with table fields - you must do this on your version too
you must alter the references for other forms as required (e.g. if txtUsrID is not called txtUsrID on other forms)
took off record selector and navigation buttons from FormSubMenu1 so you can see a cleaner look
If it all works, I leave it to you to replicate the work for your other form(s)

Dim lngUsrID As Long
Dim ctl As Control

Me.UserID = Environ("UserName")
lngUsrID = DLookup("UserID", "qryUserID")
Me.txtUsrID = lngUsrID 'could've been done directly against form control but a variable
'makes the following DLookup easier to write

On Error GoTo errHandler
For Each ctl In Me.Controls
    If ctl.ControlType = acCommandButton Then
        If ctl.Tag <> "" Then
            If DLookup(ctl.Tag, "FormList", "StaffID = " & lngUsrID) = True Then
                ctl.Enabled = True
            Else
            ctl.Enabled = False
            End If
        End If
    End If
Next

exitHere:
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

Database.zip