Please help this code to work

[Guest]

Following an example on another post (I believe by Allen), I put a button on
my form which prints out a report of the current record.

frmOrderStatus contains fields from table tFits and also contains 3
subreports:

On Tab1:
1. sfmCommentLatest - linked on Style_pk & Style_fk
-displays latest comment from tComments table, according to timestamp

2. sfmOSOrders - linked on Style_pk & Style_fk, located on tab1
-displays many fields queried from table tOrders

On Tab2:

3. sfmCommentsHistory - linked on Style_pk & Style_fk, located on tab2
-displays all comments from tComments table for the current style on
Parent form



I made a report layout called "rptStyleOSSummary" to print out all the same
info as on the first tab.


I placed a button on my parent form, "frmOrderStatus", with following click
event.

Private Sub btnPrintRecMain_Click()
Dim strWhere As String

If Me.Dirty Then 'Save any edits.
Me.Dirty = False
End If

If Me.NewRecord Then 'Check there is a record to print
MsgBox "Select a record to print"
Else
strWhere = "[Style_pk] = """ & Me.[Style_pk] & """"
DoCmd.OpenReport "rptStyleOSSummary", acViewPreview, , strWhere
End If
End Sub




This works perfectly, bringing up the report, displaying all the info for
the current record. ...EXCEPT when there is no comment in the comments table
for that style. Then, the report is called up with no style in the linked
field, and errors all around in the other fields.

Help?

Thank you.

 

[Allen Browne]

Sounds like the RecordSource of this report is a query that contains more
than one table?

If so, you need to use an outer join. In the upper pane of query design,
double-click the line joining the 2 tables. Access offers a dialog with 3
choices. Choose the 2nd or 3rd one (depending what you want.)

Note that you will have to be clear about critiera you apply to the table on
the outer side of the join also.

For an explanation of both issues, see:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

 

[Steve Schapel]

Justme,

It sounds like the problem lies with the query that the report is based
on. In that query, the join between the main table and the Comments
table probably should be a Left join, whereas it sounds at the moment
you have an Inner Join. In design view of the query, double-click the
join line to the Comments table, and then select the option that
describes all main records and only matching comments.