Sub SetRecordSource(cboUser As String, fraState As Control, frm As Form)
Dim ViewSource As String
If gcfHandleErrors Then On Error GoTo Proc_ERR
'// Set the form's Record Source based on the values in
'// cboUser and fraState
'// The ViewSource variable is used to determine if the local MS Access query
'// (which calls the SQL Server view) should be used or
'// if the server view should be used directly.
'// There doesn't seem to be a performance difference but we'll leave
'// this code is left in place in the case we find out later on that it
'// does make a difference.
'ViewSource = "Local"
ViewSource = "Server"
'// When cboUser is not "<All Users>" or "<No Name>" search for cboUser's name in;
'// - [Completeness_Reviewer_Name]
'// - [Technical_Reviewer_Name]
'// - [Decision_Maker_Name]
'// - [Created_By]
'// - [Last_Updated_By]
If cboUser = "<All Users>" Then
If fraState = 1 Then '// all records
If ViewSource = "Local" Then '// MS Access Query
frm.RecordSource = "SELECT * FROM [AppTracker_Milestone_Dates_Browse Query] WHERE AppTracker_Form_Name = '" & frm.Name & "'"
Else '// SQL Server View
frm.RecordSource = "SELECT * FROM [AppTracker_Milestone_Dates_Browse] WHERE AppTracker_Form_Name = '" & frm.Name & "'"
End If
ElseIf fraState = 2 Then '// Undecided records
If ViewSource = "Local" Then '// MS Access Query
frm.RecordSource = "SELECT * FROM [AppTracker_Milestone_Dates_Browse Query] WHERE [Decision_Date] IS NULL " _
& "AND AppTracker_Form_Name = '" & frm.Name & "'"
Else '// SQL Server View
frm.RecordSource = "SELECT * FROM [AppTracker_Milestone_Dates_Browse] WHERE [Decision_Date] IS NULL " _
& "AND AppTracker_Form_Name = '" & frm.Name & "'"
End If
Else '// Decided records
If ViewSource = "Local" Then '// MS Access Query
frm.RecordSource = "SELECT * FROM [AppTracker_Milestone_Dates_Browse Query] WHERE [Decision_Date] IS NOT NULL " _
& "AND AppTracker_Form_Name = " & frm.Name
Else '// SQL Server View
frm.RecordSource = "SELECT * FROM [AppTracker_Milestone_Dates_Browse] WHERE [Decision_Date] IS NOT NULL " _
& "AND AppTracker_Form_Name = '" & frm.Name & "'"
End If
End If
ElseIf cboUser = "<No Name>" Then '// Records which have no value for; [Completeness_Reviewer_Name], [Technical_Reviewer_Name], or [Decision_Maker_Name]
If fraState = 1 Then '// all records
If ViewSource = "Local" Then '// MS Access Query
frm.RecordSource = "SELECT * FROM [AppTracker_Milestone_Dates_Browse Query] WHERE " _
& "([Completeness_Reviewer_Name] IS NULL OR LTRIM(RTRIM([Completeness_Reviewer_Name])) = '') " _
& "AND ([Technical_Reviewer_Name] IS NULL OR LTRIM(RTRIM([Technical_Reviewer_Name])) = '') " _
& "AND ([Decision_Maker_Name] IS NULL OR LTRIM(RTRIM([Decision_Maker_Name])) = '') " _
& "AND AppTracker_Form_Name = '" & frm.Name & "'"
Else '// SQL Server View
frm.RecordSource = "SELECT * FROM [AppTracker_Milestone_Dates_Browse] WHERE " _
& "([Completeness_Reviewer_Name] IS NULL OR LTRIM(RTRIM([Completeness_Reviewer_Name])) = '') " _
& "AND ([Technical_Reviewer_Name] IS NULL OR LTRIM(RTRIM([Technical_Reviewer_Name])) = '') " _
& "AND ([Decision_Maker_Name] IS NULL OR LTRIM(RTRIM([Decision_Maker_Name])) = '') " _
& "AND AppTracker_Form_Name = '" & frm.Name & "'"
End If
ElseIf fraState = 2 Then '// Undecided records
If ViewSource = "Local" Then '// MS Access Query
frm.RecordSource = "SELECT * FROM [AppTracker_Milestone_Dates_Browse Query] WHERE " _
& "([Completeness_Reviewer_Name] IS NULL OR LTRIM(RTRIM([Completeness_Reviewer_Name])) = '') " _
& "AND ([Technical_Reviewer_Name] IS NULL OR LTRIM(RTRIM([Technical_Reviewer_Name])) = '') " _
& "AND ([Decision_Maker_Name] IS NULL OR LTRIM(RTRIM([Decision_Maker_Name])) = '') " _
& "AND [Decision_Date] IS NULL " _
& "AND AppTracker_Form_Name = '" & frm.Name & "'"
Else '// SQL Server View
frm.RecordSource = "SELECT * FROM [AppTracker_Milestone_Dates_Browse] WHERE " _
& "([Completeness_Reviewer_Name] IS NULL OR LTRIM(RTRIM([Completeness_Reviewer_Name])) = '') " _
& "AND ([Technical_Reviewer_Name] IS NULL OR LTRIM(RTRIM([Technical_Reviewer_Name])) = '') " _
& "AND ([Decision_Maker_Name] IS NULL OR LTRIM(RTRIM([Decision_Maker_Name])) = '') " _
& "AND [Decision_Date] IS NULL " _
& "AND AppTracker_Form_Name = '" & frm.Name & "'"
End If
Else '// Decided records
If ViewSource = "Local" Then '// MS Access Query
frm.RecordSource = "SELECT * FROM [AppTracker_Milestone_Dates_Browse Query] WHERE " _
& "([Completeness_Reviewer_Name] IS NULL OR LTRIM(RTRIM([Completeness_Reviewer_Name])) = '') " _
& "AND ([Technical_Reviewer_Name] IS NULL OR LTRIM(RTRIM([Technical_Reviewer_Name])) = '') " _
& "AND ([Decision_Maker_Name] IS NULL OR LTRIM(RTRIM([Decision_Maker_Name])) = '') " _
& "AND [Decision_Date] IS NOT NULL " _
& "AND AppTracker_Form_Name = '" & frm.Name & "'"
Else '// SQL Server View
frm.RecordSource = "SELECT * FROM [AppTracker_Milestone_Dates_Browse] WHERE " _
& "([Completeness_Reviewer_Name] IS NULL OR LTRIM(RTRIM([Completeness_Reviewer_Name])) = '') " _
& "AND ([Technical_Reviewer_Name] IS NULL OR LTRIM(RTRIM([Technical_Reviewer_Name])) = '') " _
& "AND ([Decision_Maker_Name] IS NULL OR LTRIM(RTRIM([Decision_Maker_Name])) = '') " _
& "AND [Decision_Date] IS NOT NULL " _
& "AND AppTracker_Form_Name = '" & frm.Name & "'"
End If
End If
Else
If fraState = 1 Then '// all records <<Fails Here>>
If ViewSource = "Local" Then '// MS Access Query
frm.RecordSource = "SELECT * FROM [AppTracker_Milestone_Dates_Browse Query] WHERE '" _
& cboUser & "' IN ([Completeness_Reviewer_Name], [Technical_Reviewer_Name], [Decision_Maker_Name], [Created_By], [Last_Updated_By]) " _
& "AND AppTracker_Form_Name = '" & frm.Name & "'"
Else '// SQL Server View
frm.RecordSource = "SELECT * FROM [AppTracker_Milestone_Dates_Browse] WHERE '" _
& cboUser & "' IN ([Completeness_Reviewer_Name], [Technical_Reviewer_Name], [Decision_Maker_Name], [Created_By], [Last_Updated_By]) " _
& "AND AppTracker_Form_Name = '" & frm.Name & "'"
End If
ElseIf fraState = 2 Then '// Undecided records
If ViewSource = "Local" Then '// MS Access Query
frm.RecordSource = "SELECT * FROM [AppTracker_Milestone_Dates_Browse Query] WHERE '" _
& cboUser & "' IN ([Completeness_Reviewer_Name], [Technical_Reviewer_Name], [Decision_Maker_Name], [Created_By], [Last_Updated_By]) " _
& "AND [Decision_Date] IS NULL " _
& "AND AppTracker_Form_Name = '" & frm.Name & "'"
Else '// SQL Server View
frm.RecordSource = "SELECT * FROM [AppTracker_Milestone_Dates_Browse] WHERE '" _
& cboUser & "' IN ([Completeness_Reviewer_Name], [Technical_Reviewer_Name], [Decision_Maker_Name], [Created_By], [Last_Updated_By]) " _
& "AND [Decision_Date] IS NULL " _
& "AND AppTracker_Form_Name = '" & frm.Name & "'"
End If
Else '// Decided records
If ViewSource = "Local" Then '// MS Access Query
frm.RecordSource = "SELECT * FROM [AppTracker_Milestone_Dates_Browse Query] WHERE '" _
& cboUser & "' IN ([Completeness_Reviewer_Name], [Technical_Reviewer_Name], [Decision_Maker_Name], [Created_By], [Last_Updated_By]) " _
& "AND [Decision_Date] IS NOT NULL " _
& "AND AppTracker_Form_Name = '" & frm.Name & "'"
Else '// SQL Server View
frm.RecordSource = "SELECT * FROM [AppTracker_Milestone_Dates_Browse] WHERE '" _
& cboUser & "' IN ([Completeness_Reviewer_Name], [Technical_Reviewer_Name], [Decision_Maker_Name], [Created_By], [Last_Updated_By]) " _
& "AND [Decision_Date] IS NOT NULL " _
& "AND AppTracker_Form_Name = '" & frm.Name & "'"
End If
End If
End If
Proc_End:
Exit Sub
Proc_ERR:
MsgBox "The SetRecordSource subroutine encountered an unexpected error." & vbCrLf & vbCrLf _
& "Error: (" & Err.Number & ") " & Err.Description, vbCritical
Resume Proc_End