Access 2010 select multiple recordset value and store as variables
Posted on 2013-01-26
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])
strSGID = "1000"
DispSGID = strSGID
'======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
If intDispSGID = "2" Then
Me.cmdScheduleUpload.Visible = True
If intDispSGID = "2" Or intDispSGID = "4" Then
Me.cmdAmendCompanyDetails.Visible = True
Hope someine can help. Many thanks