User Security

[MarkH2]

Hi, I'm new here and have a question about implementing some basic security in Access 2000/2003. I've done a search on the existing threads, but can't find anything that looks like what I'm after.

I have a custom built switchboard that has 7 options on it.

In the background, I have a TblUserName table, which has Name, ID and several columns for access rights (current, option1, option2, option3..option7). These are simple Yes/No columns to indicate whether a user should have access or not.

Thanks to some of the threads on here, I can capture the current windows login name of a user and display it on a form (Thanks to ADzeii for his Insight - How to generate a user list).

What I want to be able to do, is retreive the user id on open, and then show/hide some of the options from the switchboard dependent on the value in the relevant field.

In terms of actually hiding fields, I can do that bit, it's the select based on information from a table that I'm struggling with.

Can anyone help with this please. I'm fairly experienced with Access and have some VB skills but not a lot.

Thanks

[ChipR]

You probably want to use the DLookup function, which you can read about in Access Help. Assuming you store the login name in a global variable (strUserName), something like:

option1 = DLookup("option1", "TblUserName", "Name = """ & strUserName & """")
option2 = DLookup("option2", "TblUserName", "Name = """ & strUserName & """")

Your other option is to open a RecordSet, move to the appropriate record based on the login name, and then access the fields of that record like:

    Dim records As Object
    Dim db As Object
 
    Set db = CurrentDb()
    Set records = db.openrecordset("TblUserName")
 
    While Not records!Name = strUserName
        records.movenext
        If (records.EOF) Then
            MsgBox strUserName & " info not found."
            records.Close
            Set records = Nothing
            Set db = Nothing
            Exit Sub
        End If
    Wend
 
    option1 = records!option1
    option2 = records!option2
    ...
 
    records.Close
    Set records = Nothing
    Set db = Nothing
Exit Sub

[ChipR]

I forgot another useful option. You place a hidden combo box on your form with the TblUserName info in columns, with the Name column bound. Then you do:

UserInfoCombo = strUserName
 
If UserInfoCombo.ListIndex < 0 Then
  MsgBox "User not found!"
  Exit Sub
End If
 
option1 = UserInfoCombo.Column(1)
option2 = UserInfoCombo.Column(2)
...

[MarkH2]

ChipR

Huge thanks for that, It's almost there. (Been in a meeting, hence delay in responding)

I went for the recordset option, as it seems like the most tidy and maintainable option. However, I'm having another problem.

here's how I implemented your code

Dim records As Object
    Dim db As Object
 
    Set db = CurrentDb()
    Set records = db.OpenRecordset("TblUserAccounts")
 
    While Not records!PayNumber = GetUserName()
        records.MoveNext
        If (records.EOF) Then
            MsgBox GetUserName() & " info not found."
            records.close
            Set records = Nothing
            Set db = Nothing
            Exit Sub
        End If
 
     Wend
 
'Hide/Show the Business Service Button,Combo & Label
  Me!CmboBusServ.Visible = records.BusinessService
  Me!Option1.Visible = records.BusinessService
  Me!OptionLabel1.Visible = records.BusinessService
 
'Show/Hide the ICT Service button, combo and label
  Me!comboICTServ.Visible = records.ICTService
  Me!Option2.Visible = records.ICTService
  Me!OptionLabel2.Visible = records.ICTService

If I comment out the second bit, show/hide ICT Service button, combo and Label, leaving just one option, it works fine.

When I add in the second option, the form won't open. If it's in design view it just loops back into design view (no error codes or messages). If it's closed, then it opens and closes straight away.

Is it because I'm trying to manipulate 3 controls? There are 9 records in the table currently, and the ID that I'm testing with is record No. 3 with PayNumber as the Primary Key on the table.

I've not worked with recordsets much, so maybe I'm missing something obvious?

[ChipR]

I haven't tried this, but there might be a problem with your syntax. Using records. would try to access properties of the generic RecordSet collection, while records! will access the data in the field that you specify. I'm surprised you didn't get any errors from Access giving you some information. I should have left the error handling code in my example.
At the very top of the function put:

On Error Goto ErrorHandler

And before the end, add:

ExitCode:
  Exit Sub
 
ErrorHandler:
  MsgBox "Error " & Err.Number & " - " & Err.Description
  Resume ExitCode
Exit Sub

That should give you a message box if an error occurs and a description of what happened. The Err object is built into Access. If no error occurs, the code just continues through the ExitCode and exits normally.

[ChipR]

The alternative syntax is the RecordSet.Fields collection.

records.Fields(columnNumber)
records.Fields("name")
records.Fields![name]

[MarkH2]

ChipR

Your a star, If only I'd put the error trapping in, I'd have found the error straight away.

I'd misspelt one of the controls, comboICTService instead of cmboICTService

The error trapping picked that up and now I've corrected it, everything is working exactly as required.

I changed the records. to records! on the first couple (for testing) and it didn't seem to make any difference.

Problem solved, Huge thanks again. Thats saved me a headache and a half. :-)

[NeoPa]

Mark,

From the problem you reported (mis-spelling) I thought I'd copy in a post I used elsewhere. The recommendations may help you in your future work.

It is always a good idea to ensure that variable name checking is enabled, AND your code compiles (at least compilation has been attempted), before submitting a question.

This avoids asking questions which are much more easily resolved on your own PC than on a forum.

To ensure variable name checking is enabled for all new modules, go to - Tools / Options / Editor (from the VBA Editor window) and set Require Variable Declaration to True (checked). For existing modules, ensure that the Option lines at the very top include :

Option Explicit

To compile your project, select (again from the VBA Editor window) Debug / Compile Project Name.

We ARE generally happy to help with compilation problems too (If you find an error reported and you can't resolve it, let us know), but we do expect members to have tried compiling before submitting a question. That way we have a better idea of the sort of problem we're looking at.

[MarkH2]

NeoPa

Thanks for the pointers, it's something that I generally already do, and indeed the module that this code is in, does indeed have the option explicit at the top, (as I used the switchboard wizard, and changed it to suit my purposes) and had compiled succesfully, which is what threw me.

I'll make sure that the rest of the modules have the option explicit in them.

[NeoPa]

@MarkH2
Going by your code in post #5 I too am confused.

Where code of the form Me.Controls("MyWrongControlName") is used that cannot be picked up by the compiler. I would expect it to find errors of the form Me.MyWrongControlName though :S