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

[David618]

You're right - that did it. Thanks!

[David618]

One question on the search form filter. The search from itself (frmSearch) pops up when a super user clicks a button while they're in the Editor form. Basically, it will "pop up" on top of the Editor, the user will then select values from 2 different combo boxes (Employee Name - cboSName and Functional Area - cboSArea - can be either or both) and hit a command button (cmdSFilter) to "execute" the filter and limit the number of records they can scroll through in the Editor to the criteria they selected.

My question is this, I want this to "fire" when the user clicks the command button (cmdSFilter) and refine or "filter" the results in the Editor according to the criteria they selected and close the search from (frmSearch). I assume that I would use the "On Click" for the command button (cmdSFilter) which would bring up a separate coding page. How do I add the additional WHERE statements to my criteria, that being the case? Can I still "call" the sSQL that's in the frmEmployeeData to look something like this:

sSQL = sSQL & "WHERE (((tblEmployeeRoster.Emp_ID) = " & "Forms!frmSearch!cboSName" & ")) OR (((tblEmployeeRoster.Functional_Area)= ” & “Forms!frmSearch!cboSArea” & “));"
            Forms!frmEmployeeData.RecordSource = sSQL

[David618]

I tried this:

Private Sub cmdSFilter_Click()
If Not IsNull(cboSName) Then
        sSQL = sSQL & "WHERE (((tblEmployeeRoster.Emp_ID) = " & "Forms!frmSearch!cboSName.column(0)" & "))"
            Debug.Print sSQL
            Forms!frmEmployeeData!.RecordSource = sSQL
End If

DoCmd.Close

End Sub

- for the On Click event for that command button on the search form and it gave me the following error: "Run-time error '3125': 'WHERE (((tblEmployeeRoster.Emp_ID) = " & "Forms!frmSearch!cboSName.column(0)' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long." I only tried it for the first combo box so far (cboSName). I haven't attempted to add the second one (cboSArea) yet.

I think I may start a new thread - this one seems to be getting too long - ?

[David618]

PS - in the immediate window it's only printing WHERE (((tblEmployeeRoster.Emp_ID) = Forms!frmSearch!cboSName.column(0))) - which doesn't seem to be picking up the rest of the SQL statement from sSQL (since I'm launching from within another form?). Is there a way to refer to that or should I cut and paste the sSQL syntax from the other form in this one?

[David618]

Adding the sSQL worked:

Option Compare Database
Private Sub cmdSFilter_Click()
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] "
If Not IsNull(cboSName) Then
        sSQL = sSQL & "WHERE (((tblEmployeeRoster.Emp_ID) = " & "Forms!frmSearch!cboSName" & "))"
            Debug.Print sSQL
            Forms!frmEmployeeData!.RecordSource = sSQL
End If

DoCmd.Close

End Sub

Now I need to make the WHERE work with both combo boxes = cboSName AND/OR cboSArea... If I have trouble with that, I think I'll post it as a new thread - thanks so much for all of your help - greatly appreciated!!!