Solved

Access 2010 select multiple recordset value and store as variables

Posted on 2013-01-26
6
773 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 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

860 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