On-Format problem

Carnafex

Registered User.
Local time
Today, 23:39
Joined
Jan 19, 2004
Messages
38
A little problem has arising when trying to display particular values in a report. The setup is like this: the report displays a part number, and then any orders for that part. (Important part is underlined.) For example:

SS1010: Control Box
Issued: 1/2/04 Some info Quanity
Issued: 2/2/03 Some Info Quanity

SS1040: R/H Side Control
...etc

I get the quantity from a List Box I created (it has 2 columns, PartNo and Quantity) and then place it in the code. Strangely though only the last quantity in the list will be displayed.
So for example, part A is ordered 20 times, part B 30, but when I run the code below, it says the quantity for both is 30. Heres the code I use.

Code:
[b]private sub Report_OnFormat()[/b]

If (QtyChk < QtyCRC) then
 TextQty = Forms!IndReportSetup!ListQty.Column(1, QtyChk)
else
 exit sub
end if
QtyChk = QtyChk +1

[b]end sub[/b]


[b]private sub Report_Open()[/b]

QtyCRC = Forms!IndReportSetup!ListQty.ListCount

[b]end sub[/b]

Where IndReportSetup is the form with the listbox, ListQty is the listbox itself, and TextQty is the text field on the report that I want to display my quantity.

If you need any more info, just ask and Ill post it. Any help with my problem would be greatly appreciated.
Thanks
Jason
 
Sounds like the code changes the relationship between the two ("A" & "B") in your query for that report. Ss the relationship between them correct in the design view of the query? Sorry i cant say much without actually seeing what data duplicates.
________
Chevrolet Chevette
 
Last edited:
Heres some more info, it might help you see what I mean.
So heres an example of a test report: (in design view)

Code:
[b]Header[/b]
[i]Part No         Description  Qty   ...[/i]
[b]detail[/b]
[Part No Field] [Desc]       [Qty] ...

Now the Qty field isnt bound, its actually a calculated field, with the code I did 2 posts up.
So for example, with three parts and their quantites (this is what appears in the listbox I was describing 2 posts up as well):
  • SS0011, 10
  • SS0022, 20
  • SS0033, 30
but when I run the report, it looks like this (I didn't include the description field for size):

  • .Part. .Qty.
    SS0011, 30
    SS0022, 30
    SS0033, 30

As you can see, the SS0033 quantity has replaced all the others.
Hopefully that should explain the problem nicely.

Thanks
Jason
 
Jason,

OnFormat "may" fire multiple times for one printed line. For
example, if it is the last item on the page, it will fire, see that
it won't fit on the page, and fire again when it does print at
the top of the next page.

Use Detail Print event.

Wayne
 
The problem is that your code is in the wrong event. It needs to be in the Format event of the DETAIL section. Where it is now, it is running only once for the entire report. You want it to run once for each detail record.
 
Thanks for the replies guys.
Ill give it shot when I get into work, and tell you how it goes.
Ta
Jason
 
Last edited:
I gave your ideas a go and they half worked. By this I mean that it will now show the quanities properly in Print Preview mode. However, the moment I actually print the report, the same problem that I mentioned arises.
This confuses me to no end, as I thought print preview only showed what was going to print...
BTW, I put the code in the on_print of the detail section (might make some difference).
As always, any thought or help will be appreciated.

Ta
Jason
 
Jason,

I feel your pain ... not too long ago I had a similar problem.

In A2000, I think that you are all right if you base your calculation
of the "unbound" field of your record source.

Just for grins, you could set a break-point in your code.

Have you used the debugger before?

Let me know and you'll be able to walk through your code and
see what's being calculated. If it looks OK in preview mode,
the DetailPrint triggers haven't fired yet. God, I don't want to
go down that road again.

If all else fails, post a sample db.

Tools --> Database Utilities --> Compact/Repair
ZIP
Then attach to post.

Wayne
 
Thanks for the info Wayne. I ran the debugger as you said and
found some interesting info about it. Apparently the On_Print
event fires twice, once when you open to preview, and
another one when you press print.

Ill paste my code below and you can
check it out. (Note: the textfilter string at the end I used to filter
the report at runtime, and ListQty refers to the listbox I gather some info from)

Code:
Option Compare Database
Option Explicit

Dim QtyCRC As Integer
Dim QtyChk As Integer
Dim LoopChk As Integer

[b]Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)[/b]

    Debug.Print "QtyChk/CRC: " & QtyChk & "/" & QtyCRC & 
"    with LoopChk: " & LoopChk & "    Qty: " & 
Forms!IndividualReportSetup!ListQty.Column(1, QtyChk)
    
    'leave this if run more times than parts exist
    If (QtyChk < QtyCRC) Then
        TextNoOff = Forms!IndividualReportSetup!ListQty.Column(1,
 QtyChk)
    Else
        Debug.Print "**************************"
        LoopChk = 0
        QtyChk = 0
        Exit Sub
    End If
    
    'increment only if new part
    If ((LoopChk + 1) >= DCount("[Part
 No]", "IndReportQuery", "[Part No] = '" & Forms!
IndividualReportSetup!ListQty.Column(0, QtyChk) & "'")) Then
        QtyChk = QtyChk + 1
        LoopChk = 0
    Else
        LoopChk = LoopChk + 1
    End If
    
    'calculated fields
    If ([TextNoOff] / [NoSheet] < 1) Then
        TextTotalSheets = Int(1.5 + ([TextNoOff] / [NoSheet]))
    Else
        TextTotalSheets = Int(0.5 + ([TextNoOff] / [NoSheet]))
    End If
    Remain = Abs((Int([TextTotalSheets] + 0.5) * [NoSheet]) - ([TextNoOff]))
    
[b]End Sub[/b]

[b]Private Sub Report_Open(Cancel As Integer)[/b]

    'setup stuff for formatting
    QtyCRC = Forms!IndividualReportSetup!ListQty.ListCount
    
    'finish it off
    Reports!IndProductionReport.Filter = _
 Forms!IndividualReportSetup!TextFilter
    Reports!IndProductionReport.FilterOn = True
    
[b]End[/b]

and the debug code looks like this:

  • QtyChk/CRC: 0/2 with LoopChk: 0 Qty: 50
    QtyChk/CRC: 0/2 with LoopChk: 1 Qty: 50
    QtyChk/CRC: 0/2 with LoopChk: 2 Qty: 50
    QtyChk/CRC: 0/2 with LoopChk: 3 Qty: 50
    QtyChk/CRC: 1/2 with LoopChk: 0 Qty: 57
    QtyChk/CRC: 1/2 with LoopChk: 1 Qty: 57
    QtyChk/CRC: 1/2 with LoopChk: 2 Qty: 57
    QtyChk/CRC: 2/2 with LoopChk: 0 Qty:
    **************************
    QtyChk/CRC: 0/2 with LoopChk: 0 Qty: 50
    QtyChk/CRC: 0/2 with LoopChk: 1 Qty: 50
    QtyChk/CRC: 0/2 with LoopChk: 2 Qty: 50
    QtyChk/CRC: 0/2 with LoopChk: 3 Qty: 50
    QtyChk/CRC: 1/2 with LoopChk: 0 Qty: 57
    QtyChk/CRC: 1/2 with LoopChk: 1 Qty: 57

As you can see, the second part of the debug print is when it
prints the report, and it cuts off after 6 goes,
compared to 8. So that is where the problem arises I believe.

My code looks fine to me, but its easy to miss something. As a
side note I just tried to run the report with
DoCmd.openreport, acViewNormal, so it will only run the code once,
but then the quantities are displaced down one spot. By
this I mean that (in the above example) 57 will appear in the
spot 50 currently has, and vice versa.
Man this is strange...

Hopefully my code makes it a bit clearer as to what Im doing.

Cyas
Jason

[P.s: Wanye,Im actually using V97, not 2k, my workplace hasnt
upgraded for ages]
 
Last edited:
whoops... sorry about that, Ill change it and give it another try.
 
That did the trick Pat, and thanks for the help from every one else.
Glad thats all fixed up, now I can get on with the rest of my database.

Cyas
Jason
 
It is very important to understand when a particular event is activated so you know where to put code to accomplish your tasks. Help generally tells you this. You can also create forms to test the event firing sequence. Just put message boxes in the events and notice the order in which the messages pop up.
 
Thanks for that info Pat. Ill definately use that MsgBox trick often.

Thanks,
Jason
 

Users who are viewing this thread

Back
Top Bottom