By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,073 Members | 1,136 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,073 IT Pros & Developers. It's quick & easy.

User Security

P: 5
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.

May 5 '09 #1
Share this Question
Share on Google+
9 Replies

Expert 100+
P: 1,287
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:
Expand|Select|Wrap|Line Numbers
  1. option1 = DLookup("option1", "TblUserName", "Name = """ & strUserName & """")
  2. 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:
Expand|Select|Wrap|Line Numbers
  1.     Dim records As Object
  2.     Dim db As Object
  4.     Set db = CurrentDb()
  5.     Set records = db.openrecordset("TblUserName")
  7.     While Not records!Name = strUserName
  8.         records.movenext
  9.         If (records.EOF) Then
  10.             MsgBox strUserName & " info not found."
  11.             records.Close
  12.             Set records = Nothing
  13.             Set db = Nothing
  14.             Exit Sub
  15.         End If
  16.     Wend
  18.     option1 = records!option1
  19.     option2 = records!option2
  20.     ...
  22.     records.Close
  23.     Set records = Nothing
  24.     Set db = Nothing
  25. Exit Sub
May 5 '09 #2

Expert 100+
P: 1,287
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:
Expand|Select|Wrap|Line Numbers
  1. UserInfoCombo = strUserName
  3. If UserInfoCombo.ListIndex < 0 Then
  4.   MsgBox "User not found!"
  5.   Exit Sub
  6. End If
  8. option1 = UserInfoCombo.Column(1)
  9. option2 = UserInfoCombo.Column(2)
  10. ...
May 5 '09 #3

P: 5

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

Expand|Select|Wrap|Line Numbers
  1. Dim records As Object
  2.     Dim db As Object
  4.     Set db = CurrentDb()
  5.     Set records = db.OpenRecordset("TblUserAccounts")
  7.     While Not records!PayNumber = GetUserName()
  8.         records.MoveNext
  9.         If (records.EOF) Then
  10.             MsgBox GetUserName() & " info not found."
  11.             records.close
  12.             Set records = Nothing
  13.             Set db = Nothing
  14.             Exit Sub
  15.         End If
  17.      Wend
  19. 'Hide/Show the Business Service Button,Combo & Label
  20.   Me!CmboBusServ.Visible = records.BusinessService
  21.   Me!Option1.Visible = records.BusinessService
  22.   Me!OptionLabel1.Visible = records.BusinessService
  24. 'Show/Hide the ICT Service button, combo and label
  25.   Me!comboICTServ.Visible = records.ICTService
  26.   Me!Option2.Visible = records.ICTService
  27.   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?
May 5 '09 #4

Expert 100+
P: 1,287
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:
Expand|Select|Wrap|Line Numbers
  1. On Error Goto ErrorHandler
And before the end, add:
Expand|Select|Wrap|Line Numbers
  1. ExitCode:
  2.   Exit Sub
  4. ErrorHandler:
  5.   MsgBox "Error " & Err.Number & " - " & Err.Description
  6.   Resume ExitCode
  7. 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.
May 5 '09 #5

Expert 100+
P: 1,287
The alternative syntax is the RecordSet.Fields collection.
Expand|Select|Wrap|Line Numbers
  1. records.Fields(columnNumber)
  2. records.Fields("name")
  3. records.Fields![name]
May 5 '09 #6

P: 5

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. :-)
May 5 '09 #7

Expert Mod 15k+
P: 31,709

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 :
Expand|Select|Wrap|Line Numbers
  1. 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.
May 12 '09 #8

P: 5

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.
Jun 3 '09 #9

Expert Mod 15k+
P: 31,709
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
Jun 3 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.