question about query as record source or field source

[Abbey Normal]

Hi. I have a report that prints detailed information for a selected item. The
record source for the report is a query, which joins 3 different files. I
added another file, which is an audit log, so it will print the last change
on the report.
I joined it to the main file using the appropriate id in the query, and it
worked fine, but if there are no changes, and therefore no matching records
in the audit log, i end up getting the 2427 error when trying to run the
report. I even changed the join relationship to "2" use all records from my
primary file and only those matching records in the audit log file, but that
didn't help either.
Is this query written wrong or should I be changing the field source on the
report?
Any help will be appreciated. Thank you,

 

[Abbey Normal]

Orrr.. would I be better off creating a sub-report in this report? that way I
could leave the original query alone, have another query as the record source
on the sub report (because I also need to only print the most recent change)
and then perhaps I could only print the sub-report when the query has
records? (but how would I do that)
Thanks for any help given.

 

[Tom Wickerath]

Dear Abbey,

You've used the word "files" several times, where I think you really mean
"tables". One does not join files in a query; you can join tables in a query.

When you encounter a situation that would produce the #Error on the report,
I take it that this corresponds to a query that returns no records. Is this
correct? If the answer is yes, then you simply need to add the following
event procedure to a VBA class module that is associated with the report
(create a new module in report design view, if one does not already exist--->
View | Code):

Private Sub Report_NoData(Cancel As Integer)
On Error GoTo ProcError

MsgBox "There is no data for the selected criteria.", _
vbInformation, "No Data Available..."
Cancel = True

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Report_NoData..."
Resume ExitProc
End Sub


If you are opening this report using a command button on a form, then you
will need to trap for error 2501, which would be raised when the report is
cancelled programmatically. Here is an example:

Private Sub cmdPreviewAllItemsbyGroup_Click()
On Error GoTo ProcError

DoCmd.OpenReport "rptAllItemsbyGroup", View:=acViewPreview

ExitProc:
Exit Sub
ProcError:
Select Case Err.Number
Case 2501 'Report canceled.
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, , _
"Error in cmdPreviewAllItemsbyGroup_Click event procedure..."
End Select
Resume ExitProc
End Sub



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Abbey Normal]

Nevermind, Tom. I am going to follow this post up in the query section. It
seems to be working fine, now I only need to know how to write a sub query
that selects only the TOP 1. (most current change). Thanks for your help.

 

[Tom Wickerath]

Hi Abbey,

My tack now is to (and tell me if this is inefficient) create a
sub-report, with a separate query, passing the same sku that was passed to
the main report.

Before doing that, did you try to implement the suggestion I provided,
without involving a sub-report?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Abbey Normal]

I wasn't sure it would work because I only got the error when I have no audit
log records, which is entirely possible. I couldn't show a message saying no
data available, because even if there is no audit log records, there should
be data available for the SKU. In addition, I found out my query was not
joining correctly. I have one SKU record with (possibly) multiple audit
records. It was printing multiple lines on the report, one for each of the
audit records; Also, I only need to display the last audit log record
entered, if any. So I created separate query to select the TOP 1 with a
descending sort on date, thinking I could do a filter on the same parameter
that the main report uses [SKU] but I can't get that to work either. I have
gone around and around on this. I am open to any suggestions you have on
this......

 

[Tom Wickerath]

Hi Abbey,

You might want to try out Access MVP A.D. Tejpal's sample, available here:

Report_ShowNoDataSubReports
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=50


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Abbey Normal]

Thanks, Tom. I downloaded this db and took a look, this really helped because
I see now I don't have my report set up as a sub-report report,I have it set
up as two reports in one.....

Hi Abbey,

You might want to try out Access MVP A.D. Tejpal's sample, available here:

Report_ShowNoDataSubReports
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=50


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

I wasn't sure it would work because I only got the error when I have no audit
log records, which is entirely possible. I couldn't show a message saying no
data available, because even if there is no audit log records, there should
be data available for the SKU. In addition, I found out my query was not
joining correctly. I have one SKU record with (possibly) multiple audit
records. It was printing multiple lines on the report, one for each of the
audit records; Also, I only need to display the last audit log record
entered, if any. So I created separate query to select the TOP 1 with a
descending sort on date, thinking I could do a filter on the same parameter
that the main report uses [SKU] but I can't get that to work either. I have
gone around and around on this. I am open to any suggestions you have on
this......