Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-10-2019, 05:24 AM   #1
CanuckBuck
Newly Registered User
 
Join Date: Apr 2019
Posts: 22
Thanks: 8
Thanked 1 Time in 1 Post
CanuckBuck is on a distinguished road
Access 2019 works differently?

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.

CanuckBuck is offline   Reply With Quote
Old 05-10-2019, 05:28 AM   #2
missinglinq
AWF VIP
 
missinglinq's Avatar
 
Join Date: Jun 2003
Location: Richmond (Virginia that is!)
Posts: 6,323
Thanks: 11
Thanked 725 Times in 674 Posts
missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light missinglinq is a glorious beacon of light
Re: Access 2019 works differently?

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)>
__________________
The Devil's in the Details!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


(All code solutions tested in Access 2003/2007, before posting, unless otherwise noted.)
missinglinq is offline   Reply With Quote
Old 05-10-2019, 06:05 AM   #3
CanuckBuck
Newly Registered User
 
Join Date: Apr 2019
Posts: 22
Thanks: 8
Thanked 1 Time in 1 Post
CanuckBuck is on a distinguished road
Re: Access 2019 works differently?

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.

CanuckBuck is offline   Reply With Quote
Old 05-11-2019, 05:06 AM   #4
sonic8
AWF VIP
 
Join Date: Oct 2015
Posts: 292
Thanks: 52
Thanked 87 Times in 80 Posts
sonic8 will become famous soon enough
Re: Access 2019 works differently?

Quote:
Originally Posted by CanuckBuck View Post
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.
__________________
A professional Access developer tool:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
sonic8 is offline   Reply With Quote
Old 05-11-2019, 07:36 PM   #5
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,507
Thanks: 12
Thanked 292 Times in 275 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Access 2019 works differently?

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.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is online now   Reply With Quote
Old 05-13-2019, 07:12 AM   #6
CanuckBuck
Newly Registered User
 
Join Date: Apr 2019
Posts: 22
Thanks: 8
Thanked 1 Time in 1 Post
CanuckBuck is on a distinguished road
Re: Access 2019 works differently?

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.
Attached Images
File Type: png Frame Properties.PNG (16.8 KB, 12 views)
CanuckBuck is offline   Reply With Quote
Old 05-13-2019, 08:06 AM   #7
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,843
Thanks: 56
Thanked 1,046 Times in 1,008 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Access 2019 works differently?

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)

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.

Last edited by gemma-the-husky; 05-13-2019 at 08:31 AM.
gemma-the-husky is offline   Reply With Quote
Old 05-13-2019, 08:50 AM   #8
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,507
Thanks: 12
Thanked 292 Times in 275 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Access 2019 works differently?

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
Micron is online now   Reply With Quote
Old 05-13-2019, 09:36 AM   #9
Mark_
Longboard on the internet
 
Join Date: Sep 2017
Location: Not the middle of no where, but I can see the road to it from my house.
Posts: 2,112
Thanks: 20
Thanked 405 Times in 396 Posts
Mark_ will become famous soon enough Mark_ will become famous soon enough
Re: Access 2019 works differently?

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.
Mark_ is offline   Reply With Quote
Old 05-13-2019, 09:48 AM   #10
gemma-the-husky
Super Moderator
 
gemma-the-husky's Avatar
 
Join Date: Sep 2006
Location: UK
Posts: 13,843
Thanks: 56
Thanked 1,046 Times in 1,008 Posts
gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all gemma-the-husky is a name known to all
Re: Access 2019 works differently?

@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)

__________________
Dave (Male!)
Gemma was my dog

if a poster helps you, please click the scales at the bottom left of this posting, or use the thanks button alongside.
gemma-the-husky is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Compliance Database design in Access 2019 picasso87 General 11 04-19-2019 06:04 AM
onFormat event works differently in preview and report view petko Reports 2 12-02-2016 05:49 AM
Copy After works only for the first time and works again after reopening the Access D baba Modules & VBA 3 06-21-2015 05:28 PM
[SOLVED] Access 2010 v Access 2013 - does FORMAT work differently? William86 Reports 21 04-07-2015 01:45 AM
Access Exit behaves differently depending on how it is run pledbetter Modules & VBA 13 09-30-2013 09:34 AM




All times are GMT -8. The time now is 09:52 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World