Solved

Access 2010 select multiple recordset value and store as variables

Posted on 2013-01-26
6
765 Views
Last Modified: 2013-01-26
Hi,

I have a menu screen that allows certain users from the "tblHAUsers" table access to certain functionality within the database via hidden buttons. The buttons are only shown when certain "SG_ID" values are found in the "tblSecurityUserGroup". The tblSecurityUserGroup is linked to tblHAUsers by "User_ID" (stored in each table).

The function (below) searches the tblSecurityUserGroup for the value "SG_ID" of the currently logged in user. This works fine for selecting 1 "SG_ID" value only (usually the first record it comes across).
The problem is that some users will have more than 1 SG_ID stored against their userID in the tblSecurityUserGroup.
How can I set each "SG_ID" as a variable so that in the event a User has more than 1 SG_ID in the tblSecurityUserGroup I can validate this to show certain buttons?

'========= FUNCTION to search tblSecurityUserGroup =================
Function DispSGID() As String

    Dim MyDB As DAO.Database, MyRec As DAO.Recordset, strUserName As String, strSGID As String
    Set MyDB = CurrentDb
    Set MyRec = MyDB.OpenRecordset("SELECT tblSecurityUserGroup.SG_ID " & _
    "FROM tblHAUsers INNER JOIN tblSecurityUserGroup ON tblHAUsers.User_ID = tblSecurityUserGroup.User_ID " & _
    "WHERE (((tblHAUsers.WinLogon)= '" & Forms![frmSwitchBoard]![txtUserName] & "') AND ((tblSecurityUserGroup.User_ID)=[tblHAUsers].[User_ID]));")

    If Not (MyRec.BOF Or MyRec.EOF) Then
    strSGID = (MyRec![SG_ID])
    Else
    strSGID = "1000"
    End If
   
    MyRec.Close
    MyDB.Close
   
    DispSGID = strSGID
   
End Function

'======SUB to show/hide buttons =================
intDispSGID = Me![txtDispSGID] 'this value is stored in a hidden control on the menu screen

If intDispSGID = "1" Or intDispSGID = "2" Then
        Me.cmdAmendInvoiceNumber.Visible = True
        End If
        If intDispSGID = "2" Then
        Me.cmdScheduleUpload.Visible = True
        End If
        If intDispSGID = "2" Or intDispSGID = "4" Then
        Me.cmdAmendCompanyDetails.Visible = True
        End If

Hope someine can help. Many thanks
0
Comment
Question by:andrewpiconnect
6 Comments
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 250 total points
ID: 38822303
'change this

If Not (MyRec.BOF Or MyRec.EOF) Then
    strSGID = (MyRec![SG_ID])
    Else
    strSGID = "1000"
    End If
   

with



If not myrec.eof then
     myrec.movefirst
     do until myrec.eof
              'here you can store the values in an array or in a string
             
          strSGID =strSGID & "," & MyRec![SG_ID]

          myrec.movenext
     loop
    Else
    strSGID = "1000"
End If
0
 
LVL 84
ID: 38822305
I'm curious as to why a single  user would have multiple entries in the security table?

You can store multiple values in a variable, as long as your routines to read that variable understands that and provides some mechanism to grab those values. For example, if you store the values in a comma delimited string (like 1,2,3 ) you could then use Split function to determine if the necessary value exists in that string.

However,  it would seem to be better to develop routines to determine if a user has the necessary permissions. For example:

Function CanUserSeeButton(UserID As Long, SGID As Long) As Boolean
  Dim rst As DAO.Recordset
  Set rst = currentdb.OpenRecordset("SELECT * FROM tblSecurityUserGroup WHERE UserID=" & UserID & " AND SG_ID=" & SGID)
  CanUserSeebutton = Not (rst.eof and rst.bof)
End Function

The function above returns True if the User and SGID is found in the table.

I'm curious as to exactly how you have the "security" setup on this  ... can you elaborate?
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 250 total points
ID: 38822429
Like LSM, I am wondering why you would do this :)

Anyway, if you use cap1's approach, I recommend a tweak to ensure that the concatenated string includes both a leading and a trailing delimiter.  His tweak then becomes:

If not myrec.eof then
     myrec.movefirst
     do until myrec.eof
              'here you can store the values in an array or in a string
             
          strSGID =strSGID & "," & MyRec![SG_ID]

          myrec.movenext
     loop
         strSGID = strSGID & ","
    Else
    strSGID = "1000" 
End If

Open in new window


When you test for a security ID later on, instead of using:

If intDispSGID = "1" Or intDispSGID = "2" Then

Open in new window


you would instead use:

If InStr(1, intDispSGID, ",1,") > 0 Or InStr(1, intDispSGID, ",2,") > 0 Then

Open in new window


You need leading and trailing delimiters because otherwise just looking to see if the string contains "1" would return a yes to "1", "21", "1000", etc.  By looking for ",1,", you can be more explicit.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38822543
another option is to store all the records returned in a single field temp table, which you can use in a query to get all records with the users SG_ID

If not myrec.eof then
     myrec.movefirst
     do until myrec.eof
              'here you can store the values in an array or in a string or in a table
             
          ' strSGID =strSGID & "," & MyRec![SG_ID]
          '
          ' storing the values in a tempTable
         currentdb.execute "insert into tmpTable(SG_ID) values ('" & MyRec![SG_ID] & "')"

          myrec.movenext
     loop
    Else
    strSGID = "1000"
End If
0
 

Author Comment

by:andrewpiconnect
ID: 38822704
To elaborate further.

Security setup is threefold.

1) Firstly:

I have a function that uses the Win32API function GetUserName to return the name of the user currently logged on to this machine.
I call this function from a hidden control on the main menu - Me.txtUserName

========================================
Option Compare Database
Option Explicit
Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function FindUserName() As String

    Dim strBuffer As String
    Dim lngSize As Long
     
    strBuffer = String(100, " ")
    lngSize = Len(strBuffer)
   
    If GetUserName(strBuffer, lngSize) = 1 Then
        FindUserName = Left(strBuffer, lngSize - 1)
    Else
        FindUserName = "Name not available"
    End If
End Function
========================================

2) Secondly:

I check if the above username on the main menu "Me![txtUserName]" matches a username in the table tblHAUsers
If yes then allow system to open, otherwise exit system

========================================
If Me![txtUserName] = DLookup("[WinLogon]", "tblHAUsers", "WinLogon= '" & Me![txtUserName] & "' AND Active= -1") Then

'do 3) third option below

Else
       DoCmd.Quit
End If
========================================

Thirdly:

Each logged on user is a member of 1 or more Security Groups ie.
tblHAUsers
User_ID      WinLogon      UserName
1                  FredB             Fred Bloggs
2                  JoeB                Joe Bloggs
3                  JaneB              Jane Bloggs
4                  DaveB            Dave Bloggs

tblSecurityGroups
SG_ID      Description
1              Admin
2              Invoicing
3              Company Edit
4              Bookings

tblSecurityUserGroup
ID      User_ID      SG_ID
1        1                  1
2        2                  2
3        2                  4
4        3                  3
5        4                  2

So, Fred Bloggs belongs to Security Group "Admin"
Joe Bloggs is a momber of Security Groups "Invoicing" and "Bookings"
Jane Bloggs is a member of "Company Edit" only
Dave Bloggs is also a member of "Invoicing"

I have buttons on the main menu that certain Security Groups can see whilst other can't ie
"btnInvoicing"
"btnBookings"
"btnCompany Edit"

It goes without saying that "Admin" can see ALL buttons

==============================
I need some code that will verify which Security Groups a user belongs to in order to show them the correct buttons.
==============================

Hope this explains it better
0
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38822831
<I need some code that will verify which Security Groups a user belongs to in order to show them the correct buttons.>

how do you show the buttons after evaluating the security ids?

perhaps use
select case strSG_ID
      Case 1

      Case  2, 3


      case 4

end select
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now