Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    David618 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2013
    Posts
    55

    Newbie needs to filter records in a form based on user log in status.


    I'm stuck. I have a form and when it opens it needs to filter the record source a certain way depending on the user status. Currently it hides a button and disables certain objects depending on whether the "super user" status is true or false (the "LogIn" form stays open, but is hidden in the background and a value from a combo box within that form indicates whether a "super user" has logged in or not [Forms!frmLogIn!cboIdentity.Column(4)]) - see code example below.

    What I need this code to do further is apply a particular SQL statement (filtered or not) based upon the status of the user currently logged in (see attachment). In the SQL, there are 2 tables joined by a unique identifier (Emp_ID) - the only distinction is the where clause (super user allows all records - a "non" super user only allows the record for that particular user based on their Emp_ID [from Forms!frmLogIn!cboIdentity.Column(4) which is open, but hidden). Please help!

    Respectfully submitted,
    Unworthy Newbie

    Code:
    Option Compare Database
    Private Sub Form_Open(Cancel As Integer)
    'Hide the Search button and disable certain objects if the user is not a super user
            
        If Forms!frmLogIn!cboIdentity.Column(4) = False Then
            Me.btnSearch.Visible = False
            Me.chkBilingual.SetFocus
            Me.txtEmp_ID.Enabled = False
            Me.TxtFirst_Name.Enabled = False
            Me.txtLast_Name.Enabled = False
            Me.txtPosition_Title.Enabled = False
            Me.cboFunctional_Area.Enabled = False
            
            Me.RecordSource =
        Else
            Me.btnSearch.Visible = True
            Me.txtEmp_ID.SetFocus
            
            Me.RecordSource =
            
        End If
    End Sub
    Attached Files Attached Files

  2. #2
    David618 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2013
    Posts
    55

    Applying a filter to a form.

    I tried something else (see code below). I tried applying a filter based on the bound column from the Log In form (hidden - the bound column is the employee ID - Emp_ID). Now it pulls up a separate dialog box, "Enter Parameter Value" for Emp_ID and you click "OK" or "Cancel." How do I get it to simply apply the filter to the form without the dialog box popping up? Anyone?

    Code:
    Option Compare Database
    Private Sub Form_Open(Cancel As Integer)
    'Hide the Search button and disable certain objects if the user is not a super user
            
        If Forms!frmLogIn!cboIdentity.Column(4) = False Then
            Me.btnSearch.Visible = False
            Me.chkBilingual.SetFocus
            Me.txtEmp_ID.Enabled = False
            Me.TxtFirst_Name.Enabled = False
            Me.txtLast_Name.Enabled = False
            Me.txtPosition_Title.Enabled = False
            Me.cboFunctional_Area.Enabled = False
            
    'Filter records to show only the record for the Employee who is currently logged in
            DoCmd.ApplyFilter , "Emp_ID = 'Forms!frmLogIn!cboIdentity.Column(1)'"
            
        Else
            Me.btnSearch.Visible = True
            Me.txtEmp_ID.SetFocus
        End If
    End Sub
    Hey - at least I'm trying!

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Do those queries actually show what you want?

    if so why don't you just substitute them into your code? what errors are you getting when you try to put the SQL code into the me.recordsource lines?

    save the first query as say 'qryRS1' and the second as 'qryRS2'

    then just use me.recordsource = qryrs1 or me.recordsource = qryrs2.

    If you're trying to create those queries on the fly with a SQL string you'll have to alter the super user false string to be something like


    Code:
    dim sSQL as string
    
    sSQL = "Your basic SQL String Here"
    
    if forms!frmlogin!cboidentity.column(4) = false then
         ssql = ssql & "WHERE (((tblEmployeeRoster.Emp_ID) = " & forms!frmlogin!cboidentity.column(1) & "))"
              me.recordsource = ssql
         else
              me.recordsource = ssql
         endif
    endif
    it's basically the same SQL statement you'd just tack on the WHERE statement depending on which loop you're in.

  4. #4
    David618 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2013
    Posts
    55
    Super User is false query.zipThank you so much for your reply! Actually, the first query doesn't work (if super user is False). I tried it as a test query where the criteria was the Emp_ID column for the combo box in the (hidden) open Log In form, but it didn't work. It gave me the message, "Undefined function 'Forms!frmLogIn!cboIdentity.Column' in expression." (see attached - shows screen shot of the query and the error message).

    I'm not sure why the query doesn't work. I opened the Log In form the combo box is in so it would be open when I ran the query...

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Then don't make it a query, make it a string in your events like I did.

    Where I have ssql = "your basic SQL string here"

    cut and paste your super user query SQL statement. I usually break them up a bit so it's easier to read like this:


    Code:
    ssql = "SELECT tblEmployeeRoster.Emp_ID AS tblEmployeeRoster_Emp_ID, "
    ssql = ssql & "tblEmployeeRoster.First_Name, "
    ssql = ssql & "tblEmployeeRoster.Last_Name, "
    ssql = ssql & "tblEmployeeRoster.Position_Title, "
    ...
    ssql = ssql & "FROM tblEmployeeRoster INNER JOIN tblEmployeeKSA ON tblEmployeeRoster.[Emp_ID] = tblEmployeeKSA.[Emp_ID] "
    the non super user is a subset of this so the code I gave you should work

    just put in a debug.print ssql before you try to change the recordsource and look at the statement, if it doesn't work you can cut and paste it into a query window and then go looking for why it's not working (it should be just the WHERE statement.

    MAKE SURE YOU HAVE AN EXTRA SPACE ON THE LAST LINE OF YOUR INITIAL SQL STATEMENT otherwise you'll end up with an error.

  6. #6
    David618 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2013
    Posts
    55
    That query worked with Forms!frmLogIn!cboIdentity instead of Forms!frmLogIn!cboIdentity.Column(1). Sorry - probably a silly question to you. I'll try your SQL code suggestion now and let you know how it goes...

  7. #7
    David618 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2013
    Posts
    55

    Added SQL - getting Run-time error 3141...

    Whew - that took a while. I made the changes (see below) but now get the following error: "Run-time error '3141': The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect." It stops at the Me.RecordSource = sSQL.

    I'll try to troubleshoot, but if you see something obvious, please advise -


    Code:
    Option Compare Database
    Private Sub Form_Open(Cancel As Integer)
    Dim sSQL As String
    sSQL = "SELECT tblEmployeeRoster.Emp_ID AS tblEmployeeRoster_Emp_ID, "
    sSQL = sSQL & "tblEmployeeRoster.First_Name, "
    sSQL = sSQL & "tblEmployeeRoster.Last_Name, "
    sSQL = sSQL & "tblEmployeeRoster.Position_Title, "
    sSQL = sSQL & "tblEmployeeRoster.Functional_Area, "
    sSQL = sSQL & "tblEmployeeRoster.Active, "
    sSQL = sSQL & "tblEmployeeKSA.Emp_ID AS tblEmployeeKSA_Emp_ID, "
    sSQL = sSQL & "tblEmployeeKSA.Bilingual, "
    sSQL = sSQL & "tblEmployeeKSA.Exp_Mgmt, "
    sSQL = sSQL & "tblEmployeeKSA.Exp_HCare, "
    sSQL = sSQL & "tblEmployeeKSA.Degree_Assoc, "
    sSQL = sSQL & "tblEmployeeKSA.Degree_Bchlr, "
    sSQL = sSQL & "tblEmployeeKSA.Degree_Mstr, "
    sSQL = sSQL & "tblEmployeeKSA.Degree_Dr, "
    sSQL = sSQL & "tblEmployeeKSA.Degree_Nsing, "
    sSQL = sSQL & "tblEmployeeKSA.Degree_Otr, "
    sSQL = sSQL & "tblEmployeeKSA.Cert_CPA_Cert, "
    sSQL = sSQL & "tblEmployeeKSA.Cert_CPA, "
    sSQL = sSQL & "tblEmployeeKSA.Cert_CFE, "
    sSQL = sSQL & "tblEmployeeKSA.Cert_AHFI, "
    sSQL = sSQL & "tblEmployeeKSA.Cert_CIA, "
    sSQL = sSQL & "tblEmployeeKSA.Cert_Coder, "
    sSQL = sSQL & "tblEmployeeKSA.Cert_PMP, "
    sSQL = sSQL & "tblEmployeeKSA.Cert_Otr, "
    sSQL = sSQL & "tblEmployeeKSA.HCExp_A, "
    sSQL = sSQL & "tblEmployeeKSA.HCExp_AofB, "
    sSQL = sSQL & "tblEmployeeKSA.HCExp_B, "
    sSQL = sSQL & "tblEmployeeKSA.HCExp_C, "
    sSQL = sSQL & "tblEmployeeKSA.HCExp_D, "
    sSQL = sSQL & "tblEmployeeKSA.HCExp_HH, "
    sSQL = sSQL & "tblEmployeeKSA.HCExp_DME, "
    sSQL = sSQL & "tblEmployeeKSA.HCExp_Psych, "
    sSQL = sSQL & "tblEmployeeKSA.HCExp_VA, "
    sSQL = sSQL & "tblEmployeeKSA.HCExp_Pvt, "
    sSQL = sSQL & "tblEmployeeKSA.HCExp_Caid, "
    sSQL = sSQL & "tblEmployeeKSA.HCExp_Rx, "
    sSQL = sSQL & "tblEmployeeKSA.HCExp_RRx, "
    sSQL = sSQL & "tblEmployeeKSA.HCExp_IRx, "
    sSQL = sSQL & "tblEmployeeKSA.HCExp_PRx, "
    sSQL = sSQL & "tblEmployeeKSA.HCExp_HInf, "
    sSQL = sSQL & "tblEmployeeKSA.HCExp_LTC, "
    sSQL = sSQL & "tblEmployeeKSA.HCExp_RxMkt, "
    sSQL = sSQL & "tblEmployeeKSA.HCExp_RxProd, "
    sSQL = sSQL & "tblEmployeeKSA.HCExp_RxAudit, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_MedRcdAud, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_ClmAud, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_CostRptAud, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_ComplAud, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_DeskAud, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_FinAud, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_RecvAud, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_CostRptInv, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_HCareInv, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_OtrInv, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_CourtTmny, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_TngPres, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_DataAnal, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_Nursing, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_ClmAppeal, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_CostRptApl, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_MedCod, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_ProjMgmt, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_PropDev, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_QualMgmt, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_EnrElig, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_COB, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_VA, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_Military, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_TPL, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_HIns, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_CRecr, "
    sSQL = sSQL & "tblEmployeeKSA.OtrExp_EDI, "
    sSQL = sSQL & "tblEmployeeKSA.Contr_ZPIC, "
    sSQL = sSQL & "tblEmployeeKSA.Contr_PSC, "
    sSQL = sSQL & "tblEmployeeKSA.Contr_AMIC, "
    sSQL = sSQL & "tblEmployeeKSA.Contr_MEDIC, "
    sSQL = sSQL & "tblEmployeeKSA.Contr_PDDC, "
    sSQL = sSQL & "tblEmployeeKSA.Contr_RAC, "
    sSQL = sSQL & "tblEmployeeKSA.Contr_EEV, "
    sSQL = sSQL & "tblEmployeeKSA.Contr_CareMC, "
    sSQL = sSQL & "tblEmployeeKSA.Contr_IPERA, "
    sSQL = sSQL & "tblEmployeeKSA.Contr_MEDIC_OE, "
    sSQL = sSQL & "tblEmployeeKSA.Contr_VA, "
    sSQL = sSQL & "tblEmployeeKSA.Contr_Other, "
    sSQL = sSQL & "tblEmployeeKSA.Date_Modified, "
    sSQL = sSQL & "tblEmployeeKSA.Time_Modified, "
    sSQL = sSQL & "FROM tblEmployeeRoster INNER JOIN tblEmployeeKSA ON tblEmployeeRoster.[Emp_ID] = tblEmployeeKSA.[Emp_ID] "
    
    'Hide the Search button and disable certain objects if the user is not a super user
            
        If Forms!frmlogin!cboIdentity.Column(4) = False Then
            Me.btnSearch.Visible = False
            Me.chkBilingual.SetFocus
            Me.txtEmp_ID.Enabled = False
            Me.TxtFirst_Name.Enabled = False
            Me.txtLast_Name.Enabled = False
            Me.txtPosition_Title.Enabled = False
            Me.cboFunctional_Area.Enabled = False
            
            sSQL = sSQL & "WHERE (((tblEmployeeRoster.Emp_ID) = " & Forms!frmlogin!cboIdentity & "))"
                Me.RecordSource = sSQL
            
        Else
            Me.btnSearch.Visible = True
            Me.txtEmp_ID.SetFocus
            
            Me.RecordSource = sSQL
            
        End If
    End Sub

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    use a

    debug.print ssql

    before you try to execute anything with it. Cut and paste the code into a SQL query window and see what happens, you will likely get a more detailed error than trying to run it in code. I suspect it may be your forms!frmlogin!cboidentity statement I thought you had that as a .column(x) before

    if so your where statement would be


    Code:
    sSQL = sSQL & "WHERE (((tblEmployeeRoster.Emp_ID) = " & Forms!frmlogin!cboIdentity.column(x) & "))"
    Where x would be the column of your original combo box. Just remember, combo boxes and list boxes start at column 0 and increment from there., so column 1 would actually be the 2nd column

  9. #9
    David618 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2013
    Posts
    55

    Good SQL vs what is showing in the Immediate Window...

    When I try it this way on the where (Forms!frmLogin!cboIdentity) it works since the bound column is the one I need for this (Emp_ID). I ran the debug.print.ssql and pasted it into a SQL query window. A few things: (1) for some reason it's inserting a couple of extra spaces on one of the fields: it reads "tb lEmployeeKSA.HCExp.RRx" instead of "tblEmployeeKSA.HCExp.RRx" - peculiar. (2) on the WHERE clause it doesn't put quotations around the value and is missing the semi-colon on the end: "WHERE (((tblEmployeeRoster.Emp_ID) = 2222))" instead of "WHERE (((tblEmployeeRoster.Emp_ID)="2222"));" which does work.

    However, even if I make those minor changes in the SQL from the immediate window and paste it back into a SQL query window, it still pops up a small dialog that reads "Enter Parameter Value "2222""

    Good SQL vs Immediate Window.zip

  10. #10
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    then your ID is not a number field and you'll have to change the syntax to


    sSQL = sSQL & "WHERE (((tblEmployeeRoster.Emp_ID) = '" & Forms!frmlogin!cboIdentity & "'))"

    try that first then see what happens

  11. #11
    David618 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2013
    Posts
    55
    Okay - I figured it out. I needed to put quotations around Forms!frmlogin!cboIdentity in the WHERE (since it's text):

    Code:
       sSQL = sSQL & "WHERE (((tblEmployeeRoster.Emp_ID) = " & "Forms!frmlogin!cboIdentity" & "))"
    -And that works like a charm. Now another question (assuming you still have the patience with me at this point! I have a search dialog (separate form - named frmSearch) and if it's a "super user" the command button to launch this is visible on this Editor form. I want the super user to be able to filter any records on the editor using a couple of combo boxes on the search form. By default, all the records appear for the super user, which that person can scroll through if they desire, but I wanted to add this feature that would enable them to filter the records showing up in the editor - if they so desire. Thoughts?

    Thanks again - you've been very helpful!

  12. #12
    David618 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2013
    Posts
    55
    Funny - we must have been typing a reply at the same time!

  13. #13
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    it's the same methodology, you'd just need additional WHERE statements for your additional criteria using the same type of syntax that you are using to determine super user or not.

    it'd be something like

    if not isnull(combo1) then
    <create your additional WHERE clause here>
    ssql = ssql & <additional WHERE CLAUSE>
    endif


    just keep in mind if your user is a super user you'll have to include a WHERE, but if your user is NOT a super user, they already have a WHERE statement so you'd just be adding the <additonal WHERE clause>.

  14. #14
    David618 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2013
    Posts
    55
    Okay, thanks - I'll take a crack at it. Something else odd happened, though. I tested as a super user and upon opening the form as such, it gave me the error: Run-time error '2110': Microsoft Access can't move the focus to the control txtEmp_ID." So, for some reason, it doesn't want to put the focus on that particular control in the form "txtEmp_ID" - any thoughts as to why?

    Code:
            
        Else
            
            Me.RecordSource = sSQL
            
            Me.btnSearch.Visible = True
            Me.txtEmp_ID.SetFocus
            
        End If

    Seems like a simple command to execute - it works on the "non" super user - and puts the focus on the "Bilingual" check box (chkBilingual):


    Code:
        If Forms!frmlogin!cboIdentity.Column(4) = False Then
            Me.btnSearch.Visible = False
            Me.chkBilingual.SetFocus
            Me.txtEmp_ID.Enabled = False
            Me.TxtFirst_Name.Enabled = False
            Me.txtLast_Name.Enabled = False
            Me.txtPosition_Title.Enabled = False
            Me.cboFunctional_Area.Enabled = False
            
            sSQL = sSQL & "WHERE (((tblEmployeeRoster.Emp_ID) = " & "Forms!frmlogin!cboIdentity" & "))"
                Debug.Print sSQL
                Me.RecordSource = sSQL

  15. #15
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Are you re-enabling your txtemp_id field? you're disabling it in the second set of code, if something's not enabled or not visible you'll get that error every time.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Filter by Records Assigned To User
    By skankingpigeon in forum Access
    Replies: 2
    Last Post: 07-23-2012, 03:35 PM
  2. Sharepoint List Query Filter based upon User Login
    By Steven.Allman in forum SharePoint
    Replies: 5
    Last Post: 03-22-2012, 11:30 AM
  3. Selecting records based on criteria from user input
    By shabbaranks in forum Programming
    Replies: 1
    Last Post: 01-04-2012, 09:06 AM
  4. Replies: 3
    Last Post: 12-14-2011, 01:24 PM
  5. Filter records based on multiple checkboxes
    By kbremner in forum Forms
    Replies: 2
    Last Post: 01-18-2011, 10:59 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums