Solved

Access 2010 select multiple recordset value and store as variables

Posted on 2013-01-26
6
763 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

743 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

10 Experts available now in Live!

Get 1:1 Help Now