Reports

[Guest]

I have been working on a database for my Wife’s side business. I have
developed most of the forms and tables and all is working OK. I don’t have
much experience with reports, but I wanted to create one that showed the
details of sales. The report is based on tblSales, and because I didn’t have
a lot of experience with reports, I based it on a form that showed sale
details for each sale. I saved the form as a report, then “cleaned it up†–
deleted command buttons, changed the background color to white, etc. The
report seemed to work fine at first, then I noticed some random
inconsistencies. There are two “controls†in the report, GiftCertNo &
GiftCertAmt, meant to show that a gift certificate was used in the sale.
GiftCertAmt is used in a calculated control to compute the total received.
GiftCertNo is a text field in tblSales that is Null if no Gift Certificate is
used for that sale. There is a table, tblGiftCertsSold, that contains fields
for GiftCertNo ( text, Primary Key) and GiftCertAmt (Currency). I only show
the Gift Cert controls if a Gift Certificate is used in the sale in both the
view forms and the report, and use the same code. The form uses the
OnCurrent event & in the report the OnPage event is used (it was the only one
that seemed to work). The code is as follows:

Me.lblGiftCertNo.Visible = False
Me.txtGiftCertNo.Visible = False
Me.lblGiftCertAmt.Visible = False
Me.txtGiftCertAmt.Visible = False
Me.txtGiftCertAmt = 0

If Not IsNull(Me.txtGiftCertNo) Then
Me.lblGiftCertNo.Visible = True
Me.txtGiftCertNo.Visible = True
Me.lblGiftCertAmt.Visible = True
Me.txtGiftCertAmt.Visible = True
' get gift certificate amount
Set db = CurrentDb
strSQL = "SELECT tblGiftCertificatesSold.Amount AS GCAmt" _
& " FROM tblGiftCertificatesSold" _
& " WHERE (((tblGiftCertificatesSold.GiftCertNo) = '" &
Me.txtGiftCertNo & "'));"
Set rec = db.OpenRecordset(strSQL)
Me.txtGiftCertAmt = rec("GCAmt")
rec.Close
End If

I should mention that only one sale used a gift certificate so far, the last
one.

The View form works fine. The report works “most†of the time. If I open
the report (preview mode) and cycle through all records, one at a time, using
the record navigation buttons at the bottom, all is fine. If I open the
report and go to the last record immediately, the gift cert info is not
shown, and if I step back to the previous record, the gift cert controls are
visible, GiftCertNo is Null and GiftCertAmt shows the amount from the gift
certificate used in the last sale, and is deducted from the calculated field
total received.. If I step back one more record, I see the same thing.
Stepping back one more, the gift cert controls are not shown. If I navigate
around the records, I will “randomly†see this in other sales. Normally, I
wouldn’t use a report to “View†the sale details, but I added a command
button to the View form to Print the current record using the report:

Private Sub cmdPrint_Click()
DoCmd.OpenReport "rptSales-Detail", _
WhereCondition:="Sale_No=" & Me.Sale_No
End Sub

and if I try to print the last sale, (the only one that uses a gift
certificate), the gift certificate information is not included, just as if I
opened the report & jumped to the last record immediately.
Any help or suggestions would be greatly appreciated.

Thanks Garry Gross

 

[Guest]

I think I discovered the answer, using the the OnFormat of the Detail section
of the report seems to work. I would still like to hear if this is the right
way.

Thanks Garry Gross