Access 2019 works differently? (1 Viewer)

CanuckBuck · May 10, 2019

Hello;

I'm doing some testing in preparation for a transition to Windows 10 and MS Access 2019. I've run into an odd different behavior and wonder if someone knows anything about this.

In my Access (.accdb) 2010 database, which is connected to a SQL Server 2016 datatabase using linked tables, I have a form with a set of radio buttons in an option group. The Option Group control has a default value set. I have a Form_Load event handler which runs a query based on the value of the Option Group control. In Access 2010, at run time, the event handler shows that the value of the Option group control is set to the default and the Form_Load event handler runs the query as designed. In Access 2019 the event handler that the value of the Option group control is set to Nothing.

Does anyone know why this would be the case?

It's almost as though, in Access 2019 the default values of controls on the form are not set before the Form_Load handler is executed.

After the form is loaded, if the Option Group is changed, which reruns the query, the value of the Option group chosen by the user is passed in correctly and the query works as designed.

missinglinq · May 10, 2019

Does any code execute when the Form is opened by simply clicking on it, in the Nav Pane? In version 2007 and later, when no VBA code executes, the problem is usually that the folder holding the database hasn’t been designated as a "Trusted" location.

I assume that this is a new installation of Access...has the folder been declared as 'trusted' in this installation?

Linq ;0)>

CanuckBuck · May 10, 2019

missinglinq;

Thanks for your response.

Tracing the code using the dubugger I can see that the code defined in the various event handlers e.g. Form_Load runs when the form is opened from the Nav pane. The problem is that when the code gets to the point where the value of the Option_Group is needed it's value is Nothing in Access 2019 but in Access 2010 it has the control's default value.

Also, there is no warning that vba code has been disabled (as I have seen in Access 2010).

You are correct, this is a new installation of MS Access 2019 and indeed the folder where the Access database is stored was not defined as a trusted location. I corrected that but it did not make a difference.

I think that because the behavior is different once the form is opened points to something different than a trusted locations issue.

sonic8 · May 11, 2019

CanuckBuck said:
The problem is that when the code gets to the point where the value of the Option_Group is needed it's value is Nothing in Access 2019 but in Access 2010 it has the control's default value.

Show the code please.
A controls value being Nothing is rather unusual. I suspect there is an ambiguity in your code that is resolved differently in the different versions of Access.

Micron · May 12, 2019

Yes, odd that a frame is Nothing on Load. Nothing is the state of an uninitialized object variable. If you were using SET to assign the frame to an object variable, I can imagine how you might encounter that condition wrt the variable, but not between successive Access versions. If you want me/someone to test on 2016 for fun, post a db.

CanuckBuck · May 13, 2019

Here's the Form_Load routine. It calls the ViewFormLoadTasks subroutine, which is in a standalone module.

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

Here's the ViewFormLoadTasks subroutine. This subroutine, in turn, calls the SetRecordSource subroutine which is in the same module.

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 based on the defaults set on the
'// cboUser and fraSate controls
    
    SetRecordSource frm.cboUser, frm.fraState, 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 the SetRecordSource subroutine, which is where the error occurs. In the debugger the fraState parameter has a value of nothing in Access 2019 but a value of 2 in Access 2010. The code fails at the point noted by <<Fails Here>>

Code:

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

I've also included a screenshot showing that the default value for the frame is indeed set.

gemma-the-husky · May 13, 2019

if its got a value of nothing maybe there are no records to display?

I think all controls would be nothing in that circumstance - not null, - nothing or undefined. nz() will also fail. The only test that will not fail is "is nothing"

Could that be possible?

(* no records to display, and either a form that does not allow new records to be added, or a non-updateable form)

Micron · May 13, 2019

Again, unless I'm wrong, Nothing is the state of an uninitialized object variable and shouldn't have any bearing on records.
Going to take a lot of guessing without seeing the db, I'd say. Guesses like
- if the issue is version dependent, seems like a references problem
- I for one have questions about the code; e.g. how do we know the frame gets passed ot the function?
- along the lines of references, maybe pass frame as object rather than control, then SET afterwards.
- why does it fail where you indicate yet not at any of the many similar lines that come before it?
- did you ensure that the procedure is receiving the frame? Immediate window test :
?fraState.Name

Mark_ · May 13, 2019

For myself, I normally set default values in code rather than on the control itself. I have ONE SUB that handles all of my prep work, that way I can see exactly what I'm trying to do at the beginning.

I'd suggest trying this, just in case there is an internal change in ACCESS that causes the default values to be assigned later in 2019 than in previous version.

gemma-the-husky · May 13, 2019

@all

As the OP said he was testing, I thought it might well be that the form he is testing is "empty" - not null, empty, and any attempt to read a control will fail (I think the error code is a large negative number off hand)

Access 2019 works differently? (1 Viewer)

CanuckBuck

Registered User.

missinglinq

AWF VIP

CanuckBuck

Registered User.

sonic8

AWF VIP

Micron

AWF VIP

CanuckBuck

Registered User.

Attachments

gemma-the-husky

Super Moderator

Micron

AWF VIP

Mark_

Longboard on the internet

gemma-the-husky

Super Moderator

Similar threads

Users who are viewing this thread