Application defined or object defined error on report

[ToniS]

I have a report with three sub reports on it.
As soon as I add the following code I get the following error when I run the
report more then once...(first time the report runs) "Application defined or
object defined error"

If IsNull(Report_ExhReportAlpha_SubReportRepGroups.RepGroupName) Then
blnRepGroup = True
Else
blnRepGroup = False
End If

as soon as I comment out the above the report runs.


Below is all of the source code for the report.

Option Compare Database
Option Explicit

Private Sub Report_Open(Cancel As Integer)

Dim subRptPersons As Report
Dim subRptRepGroups As Report

'set recordsource for rep persons subReport
strSQL = "SELECT EPS.ShowID, EP.ExhibitorID, EP.FirstName, EP.LastName "
& _
"FROM ExhibitorPersonsShows EPS " & _
"INNER JOIN ExhibitorPersons EP ON EPS.ExhibitorPersonID =
EP.ExhibitorPersonID " & _
"Where EPS.ShowID = " & "'" & pubShowID & "'"

DoCmd.OpenReport "ExhReportAlpha_SubReportRepPersons", acViewDesign
Set subRptPersons = Reports!ExhReportAlpha_SubReportRepPersons
subRptPersons.RecordSource = strSQL
DoCmd.Close acReport, "ExhReportAlpha_SubReportRepPersons", acSaveYes

'set recordsource for rep Group subReport
strSQL = "SELECT ExhibitorShowID, RepGroupName, RepresentedID, ShowId "
& _
"FROM ViewAllRepGroups " & _
"Where ShowID = " & "'" & pubShowID & "'"

DoCmd.OpenReport "ExhReportAlpha_SubReportRepGroups", acViewDesign
Set subRptRepGroups = Reports!ExhReportAlpha_SubReportRepGroups
subRptRepGroups.RecordSource = strSQL
DoCmd.Close acReport, "ExhReportAlpha_SubReportRepGroups", acSaveYes

' set Main report Recordsource
strSQL = "SELECT ..... "WHERE (EA.AddressType = 1 and ES.ShowID = " &
"'" & pubShowID & "') ORDER BY E.ExhibitorName "

Report_ExhReportAlpha.RecordSource = strSQL

End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim blnIntegrityChecking As Boolean
Dim blnRepGroup As Boolean

' temp testing purposes
blnIntegrityChecking = True

If blnIntegrityChecking Then
If IsNull(Report_ExhReportAlpha_SubReportRepGroups.RepGroupName) Then
blnRepGroup = True
Else
blnRepGroup = False
End If

' if missing important info print detail section in grey
If (IsNull(Address1) Or IsNull(City) Or IsNull(State) Or
IsNull(Zipcode)) Or (IsNull(ProductDescription) And blnRepGroup = False) Then
Detail.BackColor = CONST_GREY
Else
Detail.BackColor = CONST_WHITE
End If
End If
End Sub


Any help will be greatly appreciated.
Tonis

 

[Duane Hookom]

I would either:
- base the subreports on saved queries and then change the SQL property of
these queries prior to opening the main report
- Create a public function that returns the value of pubShowID. Use this
function in the criteria of your subreport record sources.

IMHO opening objects in design view to change properties is overly complex
and messy. This is especially true when referencing a subreport from code in
the main report.

 

[ToniS]

Thanks for replying so quickly. I do have a public function that sets the
pubshowID, I guess I am not sure what you mean by using a function in the
criteria of your subreport record source? Originally I did try to set the
sub report record source in the open event of the subreport but got an error
something like can not change a report once it has already started
printing..... I agree the way I have it is messy, just was not sure on how
to accomplish what I am trying to

 

[Duane Hookom]

You can create a function like:

Public Function GetShowID() as String
GetShowID = pubShowID
End Function

Then save your record source SQL as:
SELECT EPS.ShowID, EP.ExhibitorID, EP.FirstName, EP.LastName
FROM ExhibitorPersonsShows EPS
INNER JOIN ExhibitorPersons EP ON EPS.ExhibitorPersonID =
EP.ExhibitorPersonID
Where EPS.ShowID = GetShowID()

 

[ToniS]

Thanks, this should work! I have added the function with in the modules
-Module1 area, also tried adding the function in the Main report open event,
then tried the sub report open event... I keep getting the error function
does not exist... where would you add the function? For testing purposes I
added this function with in the modules -Module1 area and with in the main
form I use the function and do a msgbox to display the results and that is
working.... I just can not get it to work for the report.

Thanks again for all of your help!!

 

[Duane Hookom]

Don't you only want to grab the ShowID value for use in the criteria of your
subreport record source?

The function code should only exist in one standard module. It can then be
used almost anywhere that any other function can be used.

 

[NOTICIAS]

OOOOOOOOOOOOOO

 

[ToniS]

That is correct, I now have it w/i the Module-Modules area where I have my
other functions.. when I run the report it does not work (No information
with in the sub reports print) When I double click on the sub report I get
the following error "GetShowID" is not a reconized function name.
My function looks as follows:

Public Function GetShowID() As String
MsgBox ("w/i getshowid") ' testing
GetShowID = pubShowID
End Function

For the sub report with in the properties section I have the property
RecordSource set as follows:

SELECT EPS.ShowID..... FROM....
WHERE (EPS.ShowID = GetShowID())

I do not have any source code anywhere to set the sub report source code...
I have no idea what I am doing wrong..
Thanks again for your help.

 

[Duane Hookom]

Get rid of the MsgBox() and possibly use Debug.Print if you want to
trouble-shoot.