Clear redundant info

[Topher]

Hi there, I have a report which lists a lot of redundant information. In order to have all the information on the same line I cannot use any grouping with different headers, in the report, but I also don't want to show that redundant information. So I wrote some code in the detail section of the report which checks each record to a variable holding info from the previous record. if certain criteria match then certain information is not displayed but instead the textbox is set to a blank.

ie

part#1 nsn#1 description qty date
qty date
nsn#2 qty date
qty date
part#2 nsn#1 description qty date
qty date
qty date

* the qty and date are always different

this is the way i want the report to look. my problem arises when the report goes to a new page. when that happens the section above under part#1 will not show the appropriate information. instead of showing nsn#2 it still shows nsn#1 - then the records with nsn#2 will showup under that - as if its adding one more record. if i arrange the data so that this record is all within one page the code works flawlessly. since there is no telling when records like this occur i have to fix this from the beginnging, instead of tailoring the report to work each time the user wants to run it.

does anyone have any ideas as to what my problem maybe? or is there something funny access 97 does when changing the page?

here is my code in the detail secion.

'get info pertaining to current contract #
If varNewContract Then
strSQL = " SELECT ContactFName, ContactLName, NSNNumF, Comments, Contract,DeliveredQuantity, DateShipped, DateRequired,QtyRecieved FROM tblContractSchedule WHERE Contract = """ & Me.txtMainCont & """"

If Not IsNull(Me.txtMainNSN) And Me.txtMainNSN <> "" Then
strSQL = strSQL & " AND NSNNumF = """ & Me.txtMainNSN & """"
End If

'Debug.Print strSQL
Set rstContData = db.OpenRecordset(strSQL, dbOpenSnapshot)

Me.txtC = Me.txtMainCont
Me.txtNSN = Me.txtMainNSN

End If

'if its the 1st time the contract # is displayed - display it - otherwise dont
If Not rstContData.EOF Then

If ContractCounter = 0 Then 'PrevContract = rstContData!Contract Then '
'Me.txtC = Nz(Me.txtMainCont, " ") ' Nz(rstContData!Contract, "abc")
'PrevNSN = Nz(Me.txtMainNSN, " ") ' Nz(rstContData!NSNNumF, "abc")
PrevName = Left(Nz(rstContData!ContactFName, "abc"), 1) & " " & Nz(rstContData!ContactLName, "abc")
PrevContract = Nz(Me.txtMainCont, " ") 'Nz(rstContData!Contract, "abc")
'PrevPN = Nz(rstContData!PhoneNum, "abc")
Me.txtdqty = rstContData!DeliveredQuantity
Else
Me.txtC = ""
Me.txtNSN = ""
End If

'find out if the previous name is the same - if so dont display it - other wise display it - also ensure the contract # is the same
If PrevName = Left(rstContData!ContactFName, 1) & " " & rstContData!ContactLName And PrevContract = rstContData!Contract And ContractCounter <> 0 Then
Me.txtName = ""
Else
Me.txtName = Left(rstContData!ContactFName, 1) & " " & rstContData!ContactLName
PrevName = Left(rstContData!ContactFName, 1) & " " & rstContData!ContactLName
End If

Me.txtdeldate = rstContData!DateRequired
Me.txtdateshipp = rstContData!DateShipped
Me.txtqtyshipd = rstContData!QtyRecieved
Me.txtComments = rstContData!Comments

'move to the next record in the rst
rstContData.MoveNext
ContractCounter = ContractCounter + 1

'if there are no more records in the rst - start over - set the varnewcontract to true so that a new RST will be generated
If rstContData.EOF Then
varNewContract = True
ContractCounter = 0
Else
varNewContract = False
PrevContract = Nz(rstContData!Contract, "abc")
End If
'Else
' Me.txtC = ""
'Debug.Print strSQL
End If

 

[Chris RR]

Forgive me if this is a dumb question, but have you just tried setting "Hide Duplicates" to Yes?

Remember, too, that you can use sorting & grouping, but have no header line, just put everything in the detail.

 

[Topher]

Not a dumb question at all - but a dumb answer = no i didnt even think of that option! just tried it and i got the results i wanted! =)

but i still have a problem with the next page. when it goes to the next page it will show the duplicate information just at the top of the page. i can understand this b/c it tells you where you left off on that other page. but i'd like to try and stop it from that.

do you know of way also that will show the duplicate when another record is new?

for ie

i have contract # and a contact name. there can be say 5 contracts with N # of parts in them. but with that contract is only one contact name. i want to show the contact name at the beginning row of each contract, but not for all N parts?

is this possible?? if i turn off don't show duplicates then i get a contact name for each part which i dont want. maybe i can use a combination of my code and the duplictes on/off feature

thanks!
Topher