UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Form Not Displaying Available Query Results., Access 2016    
 
   
Zaddicus
post May 24 2019, 03:02 AM
Post#1



Posts: 122
Joined: 3-April 19
From: Cardiff


Morning UtterAccess,

This is a problem that one of my database users have encountered and submitted to me - I've had a look into it and honestly can't establish the source of the problem.

Context:
I have a sub form "usf_AttendeeOverview" - which within it has a tab control and 3 tabs with sub-forms on each tab (sub_delegates, sub_sponsors, sub_speakers) - these subs pull their data from their respective queries based on the 'event' a user would select and then display the results in a continuous form.
This works perfectly fine for delegates and sponsors however when it comes to the third sub (speakers) it sometimes displays the results and others it displays nothing (even though there are results available)

Step 1: I checked the SQL of the query - it basically mimics the working sponsor query with a change where clause on attendeetypeID from =1 to =2
SQL
SELECT tbl_Contacts.FirstName, tbl_Contacts.LastName, tbl_Contacts.Company, tbl_Contacts.Email, tbl_Attendees.AttendeeTypeID, tbl_Attendees.PackageID, tbl_Attendees.TicketTypeID, tbl_Attendees.TicketAmount, tbl_Attendees.ConfirmationID, tbl_Attendees.EventID, tbl_Attendees.AttendeeID, tbl_Attendees.ContactID
FROM tbl_Contacts INNER JOIN tbl_Attendees ON tbl_Contacts.ContactID = tbl_Attendees.ContactID
WHERE (((tbl_Attendees.AttendeeTypeID)=2) AND ((tbl_Attendees.EventID)=[TempVars]![SFAM]));

As all 3 queries do return results outside of the form I don't believe there is an issue with the syntax

Step 2: I checked the main form (usf_home) to ensure that had no filters applied, also checked the sub-form (usf_AttendeeOverview) for filters, again there were none and the same with all 3 sub-sub-forms.

Step 3: I followed this up with a quick check to see if the issue could be with the tempvars (Shouldn't be as all 3 queries use the tempvars and 2 work fine) anyway as you can see in the screenshot below when changing between the 'Finance Transformation Q2-2019' event (Id = 2) and the 'CFO & CPO Q4-2019' event (ID = 7) the tempvars is updating as expected.
Attached File  snip043.PNG ( 5.36K )Number of downloads: 4


After all this I am stumped - Is there a known issue with access 365 that could cause this? (Google or reddit have no answers)

This is the Finance Transformation Q2-2019 event selected. Nothing displayed on sub-form but the query when run stand-alone does populate results
Attached File  snip039.PNG ( 10.57K )Number of downloads: 3

Attached File  snip040.PNG ( 14.15K )Number of downloads: 3


This is the CFO & CPO Q4-2019 event - both the form and running the query stand-alone populate results
Attached File  snip041.PNG ( 20.05K )Number of downloads: 4

Attached File  snip042.PNG ( 7.3K )Number of downloads: 3


--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
Phil_cattivocara...
post May 24 2019, 03:15 AM
Post#2



Posts: 284
Joined: 2-April 18



QUOTE (Zaddicus)
This is a problem that one of my database users have encountered and submitted to me

Does this problem happen to that user only?

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
Zaddicus
post May 24 2019, 03:22 AM
Post#3



Posts: 122
Joined: 3-April 19
From: Cardiff


I've just had a quick check with 2 other users and they are experiencing the same issue - and the problem also replicates on my build

worth noting all users have the ACCDE file only and I use the main database file.

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
Phil_cattivocara...
post May 24 2019, 04:38 AM
Post#4



Posts: 284
Joined: 2-April 18



QUOTE (Zaddicus)
and the problem also replicates on my build
OK, we can say it is not related to a particular computer - operating system or Access version.
Did you check your accdb or accde? Could you disable error handling and keep error rising?
Perhaps we should see code behind form-subform-subsubform.

--------------------
Please forgive in advance my horrible English.
Go to the top of the page
 
Zaddicus
post May 24 2019, 05:03 AM
Post#5



Posts: 122
Joined: 3-April 19
From: Cardiff


QUOTE
Did you check your accdb or accde? Could you disable error handling and keep error rising?

I checked both versions - same issue. I disabled the error handling and there are no errors.

I'll post all the potentially relevant code below:

1) Enable the 'attendeeoverview' sub-form
CODE
Private Sub Go_Sub_Attendees_Click()
'On Error GoTo Errorhandler

If TempVars!AUSL = 1 Then
    GoTo AccessGranted
ElseIf TempVars!AUSL = 2 Then
    GoTo AccessGranted
ElseIf TempVars!AUSL = 6 Then
    GoTo AccessGranted
ElseIf TempVars!AUSL = 7 Then
    GoTo AccessGranted
ElseIf TempVars!AUSL = 8 Then
    GoTo AccessGranted
ElseIf TempVars!AUSL = 9 Then
    GoTo AccessGranted
ElseIf TempVars!AUSL = 10 Then
    GoTo AccessGranted

Else:
    MsgBox prompt:="You do not have the sufficient security level to access this, if you believe this to be an error please contact your system administrator.", buttons:=vbInformation, Title:="Insufficient Permissions"
    GoTo Exit_Go_Sub_Attendees

End If

AccessGranted:
sub_AttendeeOverview.Visible = True
sub_AttendeeOverview.Enabled = True
Me.sub_AttendeeOverview.SetFocus

Exit_Go_Sub_Attendees:
Exit Sub

Errorhandler:
        MsgBox prompt:="E-Link encountered an error when processing the last action. E-Link has cancelled the last action and the error has been logged with the system administrator", buttons:=vbInformation, Title:="Database Process Error"
        Call logAutoErrors(Err.Number, Err.Description, "Go_Sub_Attendees()")
        Resume Exit_Go_Sub_Attendees

End Sub

2) Enable the 'speakers' tab
CODE
Private Sub View_Speakers_Click()
'On Error GoTo Errorhandler

Me.tab_Speakers.SetFocus

Exit_View_Speakers:
Exit Sub

Errorhandler:
        MsgBox prompt:="E-Link encountered an error when processing the last action. E-Link has cancelled the last action and the error has been logged with the system administrator", buttons:=vbInformation, Title:="Database Process Error"
        Call logAutoErrors(Err.Number, Err.Description, "View_Speakers()")
        Resume Exit_View_Speakers

End Sub

3) Event Select
CODE
Private Sub txt_AttEventID_AfterUpdate()
'On Error GoTo Errorhandler

TempVars!SFAM = Me.txt_AttEventID.Value
Me.Refresh

Exit_txt_AttEventID:
Exit Sub

Errorhandler:
        MsgBox prompt:="E-Link encountered an error when processing the last action. E-Link has cancelled the last action and the error has been logged with the system administrator", buttons:=vbInformation, Title:="Database Process Error"
        Call logAutoErrors(Err.Number, Err.Description, "txt_AttEventID()")
        Resume Exit_txt_AttEventID

End Sub

For reference here is the data properties for the speakers sub
Attached File  snip045.PNG ( 10.01K )Number of downloads: 0

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
Minty
post May 24 2019, 06:02 AM
Post#6



Posts: 310
Joined: 5-July 16
From: UK - Wiltshire


Your grant access code could be simplified to just
CODE
    Select Case TempVars!AUSL
        Case 1, 2, 6, 7, 9, 10
            sub_AttendeeOverview.Visible = True
            'sub_AttendeeOverview.Enabled = True
            Me.sub_AttendeeOverview.SetFocus
        Case Else
            sub_AttendeeOverview.Visible = False
            'sub_AttendeeOverview.Enabled = False
            MsgBox prompt:="You do not have the sufficient security level to access this, if you believe this to be an error please contact your system administrator.", buttons:=vbInformation, Title:="Insufficient Permissions"
    End Select


Also setting the visible property to false will disable the form anyway, no need to disable it as well, in fact disabling a form can give you some weird side effects.

Go to the top of the page
 
Zaddicus
post May 24 2019, 06:06 AM
Post#7



Posts: 122
Joined: 3-April 19
From: Cardiff


Well that is indeed a much simpler version of my code.

I'll give that a go and see if it resolves the issue with the form not showing results (Maybe that's one of the weird side-effects)

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
missinglinq
post May 24 2019, 06:15 AM
Post#8



Posts: 4,617
Joined: 11-November 02



By 'nothing shows' you mean literally nothing...not even the Controls themselves, even if empty!

Controls don't appear in Form View when three conditions exist at the same time:

  1. The Form is Bound to a Table or Query
  2. There are no Records in the underlying Recordset
  3. The Form cannot have Records added to it

In a Split Database the Recordset may at least appear to be Empty, to the Front End, if the user on the Front End doesn't have access to the data in the Back End. This can be caused by network problems or the user simply not having the access privileges.

Several things can cause a Form to not be updateable:
  • AllowAdditions is set to No.
  • RecordSet Type Property is set to Snapshot
  • It is based on a Query, where data comes from two or more Tables, and the Query is Read-Only.

Read-Only Queries are explained clearly by Allen Browne:

http://allenbrowne.com/ser-61.html

Linq ;0)>

--------------------
Hope this helps!

The problem with making anything foolproof...is that fools are so darn ingenious!

All posts/responses based on Access 2003/2007
Go to the top of the page
 
Zaddicus
post May 24 2019, 06:41 AM
Post#9



Posts: 122
Joined: 3-April 19
From: Cardiff


QUOTE
By 'nothing shows' you mean literally nothing...not even the Controls themselves, even if empty!


That is correct - sometimes. If the 'event' is ID 2 - nothing not even the controls show, if the 'event' is ID 7 then the controls and the results do show.

Regarding the controls not even appearing:
- The Form is Bound to a Table or Query = Yes, qry_OverviewSpeak
- There are no Records in the underlying Recordset = Event ID 2 has 4 records/results in the query
- The Form cannot have Records added to it = Can not add records.

So 2 of the conditions are met for the controls not to appear however the 3rd (There are no Records in the underlying Recordset) is not met as there are records/results

QUOTE
Several things can cause a Form to not be updateable:
AllowAdditions is set to No.
RecordSet Type Property is set to Snapshot
It is based on a Query, where data comes from two or more Tables, and the Query is Read-Only.


Allow additions is set to 'no' as the additions is done through another form (This is just an overview form)
RecordSet Type Property is set to dynaset
It is based on a Query, where data comes from two or more Tables, and the Query is Read-Only. So the query does get data from 2 tables however it is not set to read-only

NOTE:
The sub-form which is producing this bug is an exact copy of sub-form sponsors (Which works fine) with the record source changed to the correct query
The query in which the record source is set to is an exact copy of the sponsors query (which works fine) with the where clause changed from 'attendeetype' ID = 1 to ID = 2

EDIT:
I have another sub-form on a different form (event/venue) which uses the EXACT same record source and that does display the results/records and the controls for eventId 2.
So to clarify the attendeeoverview may or may not display the data depending on how it feels however if I check the event/venue form for the same data it does show...
This post has been edited by Zaddicus: May 24 2019, 07:00 AM

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    25th June 2019 - 08:55 PM