Parameter declaration order conundrum (1 Viewer)

CanuckBuck · Jul 22, 2019

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;

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

theDBguy · Jul 22, 2019

Hi. Where exactly are you calling the routine? How are you setting the frm variable? I'm confused about the use of frm.cboUser if the sub definition is expecting a String.

Micron · Jul 22, 2019

you are saying that this line If frm.fraState = 1 Then

is OK if cbo is not 'all users' and is not 'no name' that it works in one procedure but not the other? I can see how if might fail if it skips to the outer ELSE block, but not in just one procedure and not the other. Makes me think that the code project might be corrupt. What you read is not always how it has been compiled. That's one reason why we open a db with the decompile switch. It completely re-compiles the code project.

The_Doc_Man · Jul 23, 2019

This is a puzzle. What is the bound column of the combo box (not only what is it, but what is its type?)

Also, from where are you calling this? Is it in the form's .Open routine? Because if so, you need to push that call back to the .Load routine. If it comes from somewhere else, I'm not so sure. In the .Open routine, the form object might be available but the controls on the forms are not guaranteed to be fully instantiated yet.

CanuckBuck · Jul 23, 2019

First; thank you for taking the time to read my post and respond. Site is a great resource!

Here's the backstory. I'll try to keep it clear and concise.

On the form (I've attached a screenshot) there is a pick-list (cboUser) of user names and a set of three radio-buttons (fraState) with the options; Decided, Undecided, and All. These two controls together, determine which records are displayed. When the form is initially opened cboUser is set to the name of the current user and the default value for fraState is set to the option Undecided (fraState = 2). This is done so that the number of rows returned is small - a couple of dozen at most.

Interactively, users can change cboUser to; someone else's name, <All Users> or <No Name> (records with no one's name has been assigned).

Interactively, users can change fraState to display "Decided" records (fraState = 1) or All records (fraState = 3).

Both of these have After Update events that look like this;

Code:

Private Sub cboUser_AfterUpdate()

If gcfHandleErrors Then On Error GoTo Proc_ERR

'//Set the form filter based on the user's choice from the drop-down
    
    SetRecordSource cboUser, Me

Proc_End:
    Exit Sub

Proc_ERR:
    MsgBox "The cboUser_AfterUpdate subroutine of the " & Me.Name & " form encountered an unexpected error." & vbCrLf & vbCrLf _
    & "Error: (" & Err.Number & ") " & Err.Description, vbCritical
    Resume Proc_End

End Sub

Private Sub fraState_AfterUpdate()
    
If gcfHandleErrors Then On Error GoTo Proc_ERR

'//Set the form filter based on the user's choice from the radio-button set
    
    SetRecordSource cboUser, Me
    
Proc_End:
    Exit Sub

Proc_ERR:
    MsgBox "The fraState_AfterUpdate subroutine of the " & Me.Name & " form encountered an unexpected error." & vbCrLf & vbCrLf _
    & "Error: (" & Err.Number & ") " & Err.Description, vbCritical
    Resume Proc_End
    
End Sub

The Form's On Load event calls a subroutine in a MainFormRoutines module called ViewFormLoadTasks, passing in Me (i.e the form object) as it's only parameter.

Code:

Private Sub Form_Load()

If gcfHandleErrors Then On Error GoTo Proc_ERR

'//Set the inital state & Visual cues
'//when the form is first opened
        
    ViewFormLoadTasks Me

Proc_End:
    Exit Sub

Proc_ERR:
    MsgBox "The Form_Load subroutine of the " & Me.Name & " form encountered an unexpected error." & vbCrLf & vbCrLf _
    & "Error: (" & Err.Number & ") " & Err.Description, vbCritical
    Resume Proc_End

End Sub

ViewFormLoadTasks sets some visual cues indicating what environment the Access database is connected to (Development, Test, or Production), and then calls SetRecordSource.

Here's ViewFormLoadTasks

Code:

Sub ViewFormLoadTasks(frm As Form)

If gcfHandleErrors Then On Error GoTo Proc_ERR

'// Set visual cues so the user knows what database environment
'// they're using

    SetEnvironment frm

'// Set the record source to it's initial state based on
'// The name of the user and to show only records without a
'// Decision date.
    
    SetRecordSource frm.cboUser, frm

Proc_End:
    Exit Sub

Proc_ERR:
    MsgBox "The ViewFormLoadTasks subroutine encountered an unexpected error." & vbCrLf & vbCrLf _
    & "Error: (" & Err.Number & ") " & Err.Description, vbCritical
    Resume Proc_End

End Sub

Here's SetEnvironment. This code is in a separate module called GlobalRoutines.

Code:

Sub SetEnvironment(frm As Form)
Dim stServerName As String
  
If gcfHandleErrors Then On Error GoTo Proc_ERR
  
'// This subroutine sets the a text string - EnvivronmentMessage, on the form,
'// and the background color of a form to indicate which server/database the
'// AppTracker solution is connected to.

    stServerName = DLookup("[DB_Environment]", "[Version]")
    
    Select Case stServerName
        Case "Devdsdbv1" 'DEV
            frm.EnvironmentMessage.Caption = "You are working with data in the Development Database."
            frm.Detail.BackColor = RGB(242, 220, 219)
        Case "Tstdsdbv1" 'TEST
            frm.EnvironmentMessage.Caption = "You are working with data in the Test Database."
            frm.Detail.BackColor = RGB(253, 248, 219)
        Case "Tstdsdb , 51033" 'TEST
            frm.EnvironmentMessage.Caption = "You are working with data in the Test Database."
            frm.Detail.BackColor = RGB(253, 248, 219)
        Case "Prddsdbv1" 'PROD
            frm.EnvironmentMessage.Caption = "You are working with data in the Production Database."
            frm.Detail.BackColor = RGB(235, 241, 222)
        Case "PRDRMSDBV1\RMSPRDSQL" 'PROD
            frm.EnvironmentMessage.Caption = "You are working with data in the Production Database."
            frm.Detail.BackColor = RGB(235, 241, 222)
    End Select

Proc_End:
    Exit Sub

Proc_ERR:
    MsgBox "The SetEnvironment subroutine encountered an unexpected error." & vbCrLf & vbCrLf _
    & "Error: (" & Err.Number & ") " & Err.Description, vbCritical
    Resume Proc_End

End Sub

Here (again) is SetRecordSource. This Subroutine builds a query string based on the selections in cboUser and fraState and updates a query object called Browse_Query which is the Record Source for the form.

Code:

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

As I posted previously, when the order of the parameters is SetRecordSource(cboUser As String, frm As Form) I get the error.

When it's declared as SetRecordSource(frm As Form, cboUser As String) everything works fine.

Here is another detail which is a head-scratcher. While, when initially opening the form with SetRecordSource declared as SetRecordSource(cboUser As String, frm As Form) throws the error, once the error is dismissed (click the End button when the error dialog box is displayed) the form is displayed using the previous definition of the Browse_Query. If the user changes cboUser or fraState the SetRecordSource runs fine.

When I set a breakpoint on SetRecordSource and run the form initially (Form Load is called) I can see, in the Locals window that frm = "Nothing". When I change CboUser or fraState after the form is open frm shows with all of it's properties.

Is it possible that frm isn't being passed correctly from ViewFormLoadTasks to SetRecordSource?

I apologize that this wasn't really short but hopefully it's clear...

Thanks in advance for any insights you can provide.

MajP · Jul 23, 2019

A long shot, but out of curiosity can resolve cboUser.value first on the code that does not work?

dim User as string
user = frm.cboUser.value
SetRecordSource User, frm

There are some nuances how parameters are resolved prior to passing. I am thinking it attempts to resolve frm.cbouser and uses the default property (value) to get the value. This may be triggering it to try to resolve frm as well possibly causing it to drop a pointer. If it is truly only the order of parameters, I have never seen or heard about anything like that.

CanuckBuck · Jul 23, 2019

@MajP

I don't understand it, but doing as you described appears to have resolved the problem.

Thank you so much.

If anyone can explain this I'd be very appreciative.

MajP · Jul 24, 2019

I cannot totally explain it but it seems related to the fact that before passing arguments vba tries to resolve them.

For example you could have the two functions that show the value of the combo

Code:

Public Sub PassCombo(SomeCombo as Access.combobox)
  msgbox SomeCombo.value
end sub

Code:

Public Sub PassCombo(SomeCombo as string)
  msgbox SomeCombo
end sub

You could call them the same way

Code:

PassCombo me.SomeCombo

Eventhough the code is exact, two different things happen. In the first case it will pass a pointer to the combobox object. In the second case it knows to resolve the value of the combobox prior to passing the value. Using the default property which is the value property that returns a string.

What I do not understand is why doing this messes up your second argument in your code. I will try to replicate this, because I have never heard of this.

There is a weird property in vba that about 1% of people understand. If you wrap your arguments in () it forces them to be resolved.

if you had this

Code:

Public Sub PassCombo(SomeCombo as Access.combobox)
  msgbox SomeCombo.value
end sub

and you called it like

Code:

passCombo me.someCombo

it would work you would pass a pointer to the combo

but if you called it

Code:

passCombo (me.someCombo)

It would fail. The syntax is fine, but it passes the value of the combo instead of the pointer to the combo object.

So I would assume that by forcing it to resolve the value first would also work for you

Code:

SetRecordSource cboUser.value, frm

Default properties and methods of objects can shorten code, but they can also confuse the shit out of people because they do not understand what is really taking place. I tend to write long code for clarity, to avoid this. Instead of
X = TxtBoxOne
I will write
x = me.txtboxone.value

The_Doc_Man · Jul 24, 2019

I have edited this response to remove some comments that in review were not constructive. After doing some research, I have to ask a question. You showed us a sequence that works and one that fails.

Code:

    SetRecordSource frm, frm.cboUser


Sub SetRecordSource(frm As Form, cboUser As String)

I won't repeat the other order of declaration because we know that fails. But if you are showing us literal code, I have to ask you where and how variable frm is declared. I'll be more specific. You showed us a call using "frm" as an actual argument and "frm.cboUser" as an actual argument. That means that frm must be declared somewhere in the scope of that call.

You ALSO use frm as a formal argument, which means that in the subroutine, frm is available as a variable. Is frm in the caller context a public variable? Because if so, you might be running into a "scope conflict" situation where "frm" is potentially multi-valued. If not, then that ain't the problem. But in truth I've never seen what you described before and have to sort of grasp at straws.

As a debugging approach (for BOTH the working case AND the non-working case)

Set a breakpoint at the first SELECT CASE statement in the subroutine. Now find the line of code that uses frm and hover the mouse over it. Also, look at the locals window to see what frm looks like.

Next, using the Show Calls menu option, step back one level in the call sequence to the caller's context. Now do the same type of examination to see what frm looks like in that context. Since this is a run-time error, setting the breakpoint before the error would be incurred will allow you to see what frm looks like without hitting the error yet.

Use the same techniques to see what your cboUser argument looks like for both cases. I fully suspect that in the failing sequence, frm in the subroutine will be "Nothing" (which is what the error 91 is trying to tell you). But it might be instructive to see what the other argument looks like in the two contexts.

One more simple test: Change the name of the formal argument inside the sub from frm to fromage or something else. Yeah, I know, it's a cheesy solution - but if it works?

CanuckBuck · Jul 24, 2019

@The_Doc_Man;

Thanks for taking the time to respond.

While I've been working with Access/VBA for a while now (databases, particularly SQL Server is where I primarily work. VBA, not so much) I have to admit that I still feel like a bit of a newby and so it's entirely possible that I could be doing something really silly that a real programmer just wouldn't do.

I have to ask you where and how variable frm is declared. I'll be more specific. You showed us a call using "frm" as an actual argument and "frm.cboUser" as an actual argument. That means that frm must be declared somewhere in the scope of that call.

The beginning of the chain where frm is called is in the Form Load event where ME (form object, right?) is passed to a subroutine called ViewFormLoadTasks

Code:

ViewFormLoadTasks Me

ViewFormLoadTasks then calls the problematic subroutine SetRecordSource.

Code:

SetRecordSource frm.cboUser, frm

It occurs to me now, that since I'm passing frm (form object) to SetRecordSource I don't really need to pass frm.cboUser since I should be able to access frm.cboUser directly in SetRecordSource, right?

Is frm in the caller context a public variable?

No (I don't think so... delaring newby status here), frm is being passed from the Form Load event. I haven't specifically declared it as a public variable anywhere.

As a debugging approach (for BOTH the working case AND the non-working case)

Set a breakpoint at the first SELECT CASE statement in the subroutine. Now find the line of code that uses frm and hover the mouse over it. Also, look at the locals window to see what frm looks like.

When I've set a breakpoint at the beginning of the SetRecordSource subroutine I can see, in the Locals window, that frm is set to Nothing, which is why I'm getting the error. What I don't know is WHY it's set to nothing. To my way of thinking - I'm passing the form object from the Form Load event to the subroutine ViewFormLoadTasks and from there to SetRecordSource but it seems to be getting lost in transit...

Doing what @MajP instructed;

A long shot, but out of curiosity can resolve cboUser.value first on the code that does not work?

dim User as string
user = frm.cboUser.value
SetRecordSource User, frm

There are some nuances how parameters are resolved prior to passing. I am thinking it attempts to resolve frm.cbouser and uses the default property (value) to get the value. This may be triggering it to try to resolve frm as well possibly causing it to drop a pointer. If it is truly only the order of parameters, I have never seen or heard about anything like that.

did indeed solve the problem. For clarity's sake, here's the new ViewFormLoadTasks and SetRecordSource subroutines. I've marked the changes as <<<thing I changed>>>

Code:

Sub ViewFormLoadTasks(frm As Form)
Dim userName As String  <<<New variable declaration>>>

If gcfHandleErrors Then On Error GoTo Proc_ERR

'// Set visual cues so the user knows what database environment
'// they're using

    SetEnvironment frm

'// Set the record source to it's initial state based on
'// The name of the user and to show only records without a
'// Decision date.
    
    userName = frm.cboUser <<< new line of code >>>
    SetRecordSource userName, frm

Proc_End:
    Exit Sub

Proc_ERR:
    MsgBox "The ViewFormLoadTasks subroutine encountered an unexpected error." & vbCrLf & vbCrLf _
    & "Error: (" & Err.Number & ") " & Err.Description, vbCritical
    Resume Proc_End

End Sub

Sub SetRecordSource(userName 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 userName = "<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 userName = "<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 '" _
            & userName & "' 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 '" _
            & userName & "' 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 '" _
            & userName & "' 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

It's also worth noting; and I probably should have said this at the beginning; The broken code works just fine in Access 2010 but is broken in Access 2019.

Finally; thanks to all who read this post and especially to those who took the time to respond!

The_Doc_Man · Jul 24, 2019

First and foremost, glad you got it working.

Second, like MajP, I've not seen this issue before.

The fact that it works for Ac2010 but not for a much newer version means that Microsoft "fixed" something that might not really have been broken, perhaps because of other changes that found the prior behavior limiting in some odd way for some new feature they wanted to implement. Got to admit, this is bizarre.

MajP's fix essentially defers the binding of the variable that you use to pick up the user. Therefore, whatever is going on is going on during argument passage evaluation, and I have yet to find any detailed articles on that subject.

For what it is worth, you were confused by what you saw. So were we, so there is no deficiency on your part.

Parameter declaration order conundrum (1 Viewer)

CanuckBuck

Registered User.

theDBguy

I’m here to help

Micron

AWF VIP

The_Doc_Man

Immoderate Moderator

CanuckBuck

Registered User.

Attachments

MajP

You've got your good things, and you've got mine.

CanuckBuck

Registered User.

MajP

You've got your good things, and you've got mine.

The_Doc_Man

Immoderate Moderator

CanuckBuck

Registered User.

The_Doc_Man

Immoderate Moderator

Similar threads

Users who are viewing this thread