Thanks again, Paul!
Pardon my ignorance, but what's the best approach on the Editor form? The order of operations I was thinking was after the user logs in (and it checks their password, if they're active and what user type they are) it would either open the Editor form or the Main Menu, depending on the user type (Super_User = true or false). If True, the Main Menu opens and the super user can choose to go to the same Editor or run some reports. If false, the Editor opens and it opens with the data for that particular user (based on their user ID - Emp_ID).
On the Editor form itself, I was thinking it would evaluate which user it is (super or not, based on the value in Super_User) and IF Super_User = True, then no filter would be applied unless they wanted to run a search, in which case a button to a sub form would be visible that would feed filter criteria to the record source for the form.
I have some older code for a different database somebody created for me which does something similar (see below). I thought I could create a modified version of it for the Record Source on the Editor form. Thoughts?
Code:
Option Compare Database
Option Explicit
Private Sub btnAll_Click()
Dim strSQL As String
With Me
'The SQL statement WHERE clause to be applied to this subform will include ALL records, ie contract_id > 0...
'strSQL = "SELECT Contract_ID, Contract_Number, Contract_Name, Entity, Agency, Service_Agreement " & _
"FROM tbl_Contract " & _
"WHERE Contract_ID > 0"
strSQL = "SELECT Contract_ID, Contract_Number, Contract_Name, Entity, Agency, Service_Agreement, LOB_Program, " & _
"IIf(End_Date>=Now(),""Current"",""Expired"") AS Contract_Status " & _
"FROM tbl_Contract "
.RecordSource = strSQL
.Filter = "contract_id > 0"
'Apply the filter
.Requery
'Erase the values of the search controls...
.cmbAgencySearch = ""
.cmbContractTypeSearch = ""
.cmbEntitySearch = ""
.cmbServiceAgreementSearch = ""
.cmbLOBSearch = ""
.lblNoRecordsFound.Visible = False
End With
Debug.Print strSQL
End Sub
Private Sub btnSelect_Click()
'After clicking on a particular contract's Edit button, the Contract form's record source will change to the SQL statement below...
'To the Contract form, apply a filter of contract_id = the value of the txtContractID control...
With Me.Parent
.RecordSource = "SELECT * " & _
"FROM tbl_Contract WHERE " & _
"Contract_ID = " & Me.txtContractID
.Requery
End With
End Sub
Private Sub btnSetFilter_Click()
Dim blnDoSearch As Boolean
Dim strSQL As String
Dim strSQLOrderBy As String
Dim strSQLWhere As String
blnDoSearch = False
'Build the SQL that will be used as the filter for this contract search form...
'SELECT subclause....
strSQL = "SELECT DISTINCT c.Contract_ID, c.Contract_Name, c.Contract_Number, c.Agency, c.Entity, c.LOB_Program, " & _
"IIf(c.End_Date>=Now(),""Current"",""Expired"") AS Contract_Status " & _
"FROM tbl_Contract AS c "
'ORDER BY subclause...
strSQLOrderBy = "GROUP BY c.Contract_ID, c.Contract_Name, c.Contract_Number, c.Agency, c.Entity, c.LOB_Program, c.End_Date " & _
"ORDER BY c.Contract_Name;"
'Set the WHERE clause based on values set in the search controls...
With Me
If .cmbContractTypeSearch <> "" Then
strSQLWhere = strSQLWhere & "AND c.contract_type = """ & Me.cmbContractTypeSearch & """ "
End If
If .cmbEntitySearch <> "" Then
strSQLWhere = strSQLWhere & "AND c.entity = """ & Me.cmbEntitySearch & """ "
End If
If .cmbLOBSearch <> "" Then
strSQLWhere = strSQLWhere & "AND c.lob_program = """ & .cmbLOBSearch & """ "
End If
If .cmbServiceAgreementSearch <> "" Then
strSQLWhere = strSQLWhere & "AND c.service_agreement = """ & Me.cmbServiceAgreementSearch & """ "
End If
If .cmbAgencySearch <> "" Then
strSQLWhere = strSQLWhere & "AND c.agency = """ & Me.cmbAgencySearch & """ "
End If
End With
Finish_SQL:
'If the WHERE clause has a value then apply it to the subform...
If strSQLWhere <> "" Then
strSQLWhere = "WHERE " & Right(strSQLWhere, Len(strSQLWhere) - 4)
Debug.Print strSQL & strSQLWhere & strSQLOrderBy
End If
With Me
.RecordSource = strSQL & strSQLWhere & strSQLOrderBy
.Requery
End With
'If no records are found, then display the "No records found" label...
If Me.RecordsetClone.RecordCount = 0 Then
Me.lblNoRecordsFound.Visible = True
Else
Me.lblNoRecordsFound.Visible = False
End If
End Sub
Syntax check: novice is missing something and can't figure it out...
Windows 7 64bit 
Access 2010 64bit
The code below is for a Log In form that works, with the exception of where it is supposed to open either the Main Menu or Editor based on the user status (Super_User - if True, open the Main Menu form, if False open the Editor form). Instead, it simply opens the Editor form regardless of the user status (Super_User). See where it says, "Close the Log In form and open either the Editor or Main Menu."
Reply With Quote
See the example at BaldyWeb: 
