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