Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 09-14-2018, 08:21 AM   #1
fishindude64
Newly Registered User
 
Join Date: Sep 2018
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
fishindude64 is on a distinguished road
VBA Error in Access Report

Hello all, I am receiving the error "You entered an expression that has no value" within an Access report. The report is grouped on a field and I have the below code in the Group Header On Format property:


If FormatCount = 1 Then

' If office hours are to be posted,
If GetReportType = "Posted" Then

Dim blConfidential As Boolean
blConfidential = Nz(Me.chkConfidential, False)

' If Address is confidential for current faculty/staff member,
If blConfidential Then
' Suppress home address and phone information on report.
Me![Address Line 1].Visible = False
Me![Address Line 2].Visible = False
Me![City/State/Zip].Visible = False
Me![Home Phone].Visible = False
Else
' Otherwise display home address and phone information on report.
Me![Address Line 1].Visible = True
Me![Address Line 2].Visible = True
Me![City/State/Zip].Visible = True
Me![Home Phone].Visible = True
End If

Else ' Office hours are for internal purposes

' Display Confidential checkbox.
Me![Confidential].Visible = True

End If

End If


The code errors on the blConfidential = Nz(Me.chkConfidential, False) statement. I have changed the control name to a different name from the associated field name (Confidential). I also tried me!chkConfidential but the same error occurs. This report has been used for years so dunno why it all of the sudden started giving this error. Any thoughts?

fishindude64 is offline   Reply With Quote
Old 09-14-2018, 08:29 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,070
Thanks: 10
Thanked 3,870 Times in 3,813 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: VBA Error in Access Report

Make sure that's the name of the checkbox and not it's associated label.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 09-14-2018, 08:30 AM   #3
fishindude64
Newly Registered User
 
Join Date: Sep 2018
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
fishindude64 is on a distinguished road
Re: VBA Error in Access Report

It is the correct spelling of the checkbox control. The associated label has the generic name of Label45.

fishindude64 is offline   Reply With Quote
Old 09-14-2018, 08:32 AM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,070
Thanks: 10
Thanked 3,870 Times in 3,813 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: VBA Error in Access Report

Can you attach the db here? It seems like that should work.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 09-14-2018, 09:27 AM   #5
fishindude64
Newly Registered User
 
Join Date: Sep 2018
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
fishindude64 is on a distinguished road
Re: VBA Error in Access Report

It is attached. The name of the report is Faculty/Staff Office Hours Report.
Attached Files
File Type: accdb English Students & Staff.accdb (784.0 KB, 6 views)
fishindude64 is offline   Reply With Quote
Old 09-14-2018, 09:50 AM   #6
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,070
Thanks: 10
Thanked 3,870 Times in 3,813 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: VBA Error in Access Report

It's because the report isn't returning any records. I changed the source to return records and the code works fine. Try adding

Cancel = True

to the report's No Data event.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 09-14-2018, 09:52 AM   #7
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,070
Thanks: 10
Thanked 3,870 Times in 3,813 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: VBA Error in Access Report

By the way, that will make you want to add error handling to the code that opens the report:

http://www.baldyweb.com/ErrorTrap.htm

__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 09-14-2018, 09:55 AM   #8
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,437
Thanks: 40
Thanked 3,368 Times in 3,263 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: VBA Error in Access Report

your report recordsource returns no records so the field does not have a value - null functions wont work because there is not a record. You need to check if any records have been returned. Usually best to do that by using say dcount of the recordsource query prior to opening the report.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 09-14-2018, 11:05 AM   #9
fishindude64
Newly Registered User
 
Join Date: Sep 2018
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
fishindude64 is on a distinguished road
Re: VBA Error in Access Report

Thanks for the heads up. I found out the reason there was no data in the report is because someone hard coded the criteria values into the reports query. I have it set up with a form that accepts user-supplied criteria and stores it into variables using functions. After modifying the query back to the function calls the report works. So thanks again!
fishindude64 is offline   Reply With Quote
Old 09-14-2018, 11:21 AM   #10
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,070
Thanks: 10
Thanked 3,870 Times in 3,813 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: VBA Error in Access Report

Happy to help!
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 09-15-2018, 02:40 AM   #11
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,437
Thanks: 40
Thanked 3,368 Times in 3,263 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: VBA Error in Access Report

Quote:
because someone hard coded the criteria values into the reports query
hope that's not because they are sharing a front end

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London 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
Access 2007 Save Report as PDF VBA error Rx_ Modules & VBA 10 03-13-2013 09:40 PM
Run-Time error '3075' Access Report WhatNThe General 7 02-01-2010 09:08 AM
Access 97 - Error when using report wizard Neil07979 General 8 07-16-2007 06:35 AM
Export access report to excel gives an error gbarje Reports 0 08-15-2006 02:09 PM
report Error in Access on 1 PC Lushman Reports 1 03-04-2003 07:41 AM




All times are GMT -8. The time now is 04:00 AM.


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

Sponsored Links

How to advertise

Media Kit


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