[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access 2010 select multiple recordset value and store as variables

Posted on 2013-01-26
6
Medium Priority
?
799 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 1000 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 85
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 93

Accepted Solution

by:
Patrick Matthews earned 1000 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

872 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