Thank you for ur response. Unfortunately I cannot create a table to hold the
description. The status description is deduced by the code from parameters in
two tables. If there are two records to be displayed (as per the underlying
query) Access goes through the Sub four times. Access (2k) exits the
Procedure each time and returns again. I think this is what is causing the
problem. The following is the code I have written.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo ErrorHandler
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim stSQL As String
If Me.Status = 5 Then
stSQL = "SELECT pkMaterialsID, RevisionNo, UnitPrice, Quantity,
DiscountPer, Description_Specification " _
& "FROM tblPOMaterials " _
& "WHERE tblPOMaterials.pkMaterialsID = " & Me.pkMaterialsID & "
AND tblPOMaterials.RevisionNo = " & (Me.POMtrlRevNo) & ""
Me.OldDiscPer.Visible = False
Me.OldSpec.Visible = False
Me.OldQuantity.Visible = False
Me.oldUnitPrice.Visible = False
Me.OldItemNo.Visible = False
Me.OldItemType.Visible = False
Me.OldMake.Visible = False
Me.OldUnit.Visible = False
Me.StatusView = "Please treat this Item as Cancelled"
Me.StatusView.Visible = True
ElseIf Me.Status = 3 Then
If Me.POMtrlRevNo = 0 And Me.pkPORevID > 0 Then 'New Line Item Added
to an Approved PO
Me.OldDiscPer.Visible = False
Me.OldSpec.Visible = False
Me.OldQuantity.Visible = False
Me.oldUnitPrice.Visible = False
Me.OldItemNo.Visible = False
Me.OldItemType.Visible = False
Me.OldMake.Visible = False
Me.OldUnit.Visible = False
Me.StatusView = "New Line Item Added"
Me.StatusView.Visible = True
Else
stSQL = "SELECT pkMaterialsID, RevisionNo, UnitPrice, Quantity,
DiscountPer, Description_Specification " _
& "FROM tblPOMaterials " _
& "WHERE tblPOMaterials.pkMaterialsID = " & Me.pkMaterialsID
& " AND tblPOMaterials.RevisionNo = " & (Me.POMtrlRevNo - 1) & ""
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(stSQL)
With rst
If .RecordCount > 0 Then
Me.OldDiscPer = !DiscountPer
Me.OldSpec = !Description_Specification
Me.OldQuantity = !Quantity
Me.oldUnitPrice = !UnitPrice
End If
End With
End If
End If
Exit_Sub:
Exit Sub
ErrorHandler:
MsgBox Err.Description & " :" & Err.Number
Resume Exit_Sub
End Sub
Thanks,
Anand
Ofer Cohen said:
First I would create a table to hold the status description, and then join
this table in the control source of the report, so if another status will be
added next time then you wont have to change the code.
It would help if you post the code you already have.
Also, instead of using code you can use the control source of the text box
where the decription is displayed
if there are only 3 and 5 status
=IIf([Status] = 3 , "New Line Item Added", "Please treat this item as
cancelled")
If there are more then 2 status
=IIf([Status] = 3 , "New Line Item Added", IIf([Status] = 5, "Please treat
this item as cancelled",""))
--
Good Luck
BS"D
Anand said:
Hello,
I have a report that displays amendments to purchase order line items. Each
amended line item has a status. There is code in the OnFormat event of the
Detail section that displays a 'Status Description' text field. The code uses
a combination of parameters from a background table to decide the contents of
the status description.
So if there are two records it should display as follows:
Status Description
3 New Line Item Added
5 Please treat this item as cancelled
My problem is when the two records are to be displayed on the report the
Status Description for both records is that of the last record in the report
and not its corresponding status description.
How do I get access to show the correct description for each record. The
background code works fine when I checked in Break mode.
Thanks in advance.
Anand
