CanuckBuck
Registered User.
- Local time
- Yesterday, 17:10
- Joined
- Apr 27, 2019
- Messages
- 31
Hello;
I've encountered a bit of a conundrum regarding the order of the declaration of parameters to a procedure. I've fixed what was broken but I want to know why it was a problem in the first place.
This code works;
The code below does not. The only difference is the order of the variable declaration in the subroutine
The code below throws the error Run-time error '91': Object variable or With block variable not set. The debugger stops at the line frm.fraState = 1 in the outermost ELSE statement.
When the form is declared last (in the code below), in the Locals window it shows with a value of Nothing. When the form is declared first (in the code above), in the locals window it shows values for all of it's properties.
What's going on here?
I've encountered a bit of a conundrum regarding the order of the declaration of parameters to a procedure. I've fixed what was broken but I want to know why it was a problem in the first place.
This code works;
Code:
SetRecordSource frm, frm.cboUser
Sub SetRecordSource(frm As Form, cboUser As String)
Dim ViewSource As String
Dim sqlStr As String
Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("Browse_Query")
If gcfHandleErrors Then On Error GoTo Proc_ERR
'// Set the form's Record Source based on the values in
'// cboUser and fraState
'// 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 frm.fraState = 1 Then '// all records
sqlStr = "SELECT * FROM [AppTracker_Milestone_Dates_Browse] WHERE AppTracker_Form_Name = '" & frm.Name & "'"
ElseIf frm.fraState = 2 Then '// Undecided records
sqlStr = "SELECT * FROM [AppTracker_Milestone_Dates_Browse] WHERE [Decision_Date] IS NULL " _
& "AND AppTracker_Form_Name = '" & frm.Name & "'"
Else '// Decided records
sqlStr = "SELECT * FROM [AppTracker_Milestone_Dates_Browse] WHERE [Decision_Date] IS NOT NULL " _
& "AND AppTracker_Form_Name = '" & frm.Name & "'"
End If
ElseIf cboUser = "<No Name>" Then '// Records which have no value for; [Completeness_Reviewer_Name], [Technical_Reviewer_Name], or [Decision_Maker_Name]
If frm.fraState = 1 Then '// all records
sqlStr = "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 & "'"
ElseIf frm.fraState = 2 Then '// Undecided records
sqlStr = "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 & "'"
Else '// Decided records
sqlStr = "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
Else
If frm.fraState = 1 Then '// all records
sqlStr = "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 & "'"
ElseIf frm.fraState = 2 Then '// Undecided records
sqlStr = "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 & "'"
Else '// Decided records
sqlStr = "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
qdf.SQL = sqlStr
frm.RecordSource = "Browse_Query"
Proc_End:
Set qdf = Nothing
Exit Sub
Proc_ERR:
MsgBox "The SetRecordSource subroutine encountered an unexpected error." & vbCrLf & vbCrLf _
& "Error: (" & Err.Number & ") " & Err.Description, vbCritical
Resume Proc_End
End Sub
The code below does not. The only difference is the order of the variable declaration in the subroutine
The code below throws the error Run-time error '91': Object variable or With block variable not set. The debugger stops at the line frm.fraState = 1 in the outermost ELSE statement.
When the form is declared last (in the code below), in the Locals window it shows with a value of Nothing. When the form is declared first (in the code above), in the locals window it shows values for all of it's properties.
What's going on here?
Code:
SetRecordSource frm.cboUser, frm
Sub SetRecordSource(cboUser As String, frm As Form)
Dim ViewSource As String
Dim sqlStr As String
Dim qdf As QueryDef
Set qdf = CurrentDb.QueryDefs("Browse_Query")
If gcfHandleErrors Then On Error GoTo Proc_ERR
'// Set the form's Record Source based on the values in
'// cboUser and fraState
'// 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 frm.fraState = 1 Then '// all records
sqlStr = "SELECT * FROM [AppTracker_Milestone_Dates_Browse] WHERE AppTracker_Form_Name = '" & frm.Name & "'"
ElseIf frm.fraState = 2 Then '// Undecided records
sqlStr = "SELECT * FROM [AppTracker_Milestone_Dates_Browse] WHERE [Decision_Date] IS NULL " _
& "AND AppTracker_Form_Name = '" & frm.Name & "'"
Else '// Decided records
sqlStr = "SELECT * FROM [AppTracker_Milestone_Dates_Browse] WHERE [Decision_Date] IS NOT NULL " _
& "AND AppTracker_Form_Name = '" & frm.Name & "'"
End If
ElseIf cboUser = "<No Name>" Then '// Records which have no value for; [Completeness_Reviewer_Name], [Technical_Reviewer_Name], or [Decision_Maker_Name]
If frm.fraState = 1 Then '// all records
sqlStr = "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 & "'"
ElseIf frm.fraState = 2 Then '// Undecided records
sqlStr = "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 & "'"
Else '// Decided records
sqlStr = "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
Else
If frm.fraState = 1 Then '// all records
sqlStr = "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 & "'"
ElseIf frm.fraState = 2 Then '// Undecided records
sqlStr = "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 & "'"
Else '// Decided records
sqlStr = "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
qdf.SQL = sqlStr
frm.RecordSource = "Browse_Query"
Proc_End:
Set qdf = Nothing
Exit Sub
Proc_ERR:
MsgBox "The SetRecordSource subroutine encountered an unexpected error." & vbCrLf & vbCrLf _
& "Error: (" & Err.Number & ") " & Err.Description, vbCritical
Resume Proc_End
End Sub