Converting Form code to a report!! 1

[GPM4663]

Hi Everyone,
I'm still struggline trying to convert this piece of code from a form to a report. I need the code below to be converted so that it will loop through the records on opening the report and insert the value in an unbound textbox (me.txtcardcount) for each record of the report. Any thoughts??? The original code was used on the "On_current" event of the form but since the user might just print all the records on the report how do i get it to work?

Public Sub CountSOCards()
Dim rst As DAO.Recordset
Dim lngBaseCount, lngRecord, lngTotalSO As Long

On Error Resume Next

Set rst = Me.RecordsetClone
rst.MoveFirst
rst.FindFirst "[SO]='" & Me.SO & "'"

lngBaseCount = rst.AbsolutePosition
lngTotalSO = DCount("UNIQID", "qryProduction", "[SO]='" & Me.SO & "'")
lngRecord = Me.CurrentRecord - lngBaseCount

'Show the result of the record count in the text box )
Me.txtCardCount = "Items on SO: " & lngRecord & " of " & lngTotalSO

Set rst = Nothing

End Sub

So far I've altered it like so but I can't get it to work.......

Public Sub CountSOCards()
Dim rst1 As Recordset
Dim rst2 As DAO.Recordset
Dim lngBaseCount, lngAbsolute, lngRecord, lngTotalSO As Integer

On Error Resume Next

Set rst1 = CurrentDb.OpenRecordset("qryJobCardsAll", dbOpenDynaset)
Set rst2 = CurrentDb.OpenRecordset("qryproduction", dbOpenDynaset)
rst1.MoveFirst
rst2.MoveFirst
rst1.FindFirst "UNIQID" = Me.UNIQID
rst2.FindFirst "[SO] like '*" & Me.SO & "*' and " & Me.Filter

lngAbsolute = rst1.AbsolutePosition
lngBaseCount = rst2.AbsolutePosition
lngTotalSO = DCount("UNIQID", "qryJobCardsAll", "[SO]='" & Me.SO & "'")
lngRecord = lngAbsolute - lngBaseCount

'Show the result of the record count in the text box )
Me.txtCardCount = "Items on SO: " & lngRecord & " of " & lngTotalSO

Set rst1 = Nothing
Set rst2 = Nothing

End Sub


I'd really appreciate any help anyone could give me.

thanks

Graham

 

[genomon]

Sorry didn't have time to look at all the code, but you don't need to "loop through" all the report records.
You can populate your text box each time the Detail_Print() event fires. Put the code that populates in that event, maybe with a DLookup() if the recordsource isn't really large....

Did you hear about the Buddhist who refused Novocain during a root
canal? He wanted to transcend dental medication.

 

[dhookom]

Can we ask what you are attempting to display in the report? Are the records in the report just being numbered or is the numbering not necessarily related to the report's record source.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[GPM4663]

Hi Everyone,
Thanks for all the help I've finally managed to crack it by placing the following code into the detail-print event as suggested. For some reason though i had to place it in the detail-format event as well or else the first record would display as zero! Don't know why?

thanks again,

GPM

Public Sub CountSOCards()
Dim rst1 As Recordset
Dim rst2 As DAO.Recordset
Dim lngBaseCount, lngAbsolute, lngRecord, lngTotalSO As Integer

On Error Resume Next


Set rst1 = CurrentDb.OpenRecordset("qryproduction", dbOpenDynaset)
Set rst2 = CurrentDb.OpenRecordset("qryproduction", dbOpenDynaset)
rst1.MoveFirst
rst2.MoveFirst
rst1.FindFirst "UNIQID=" & Me.UNIQID
rst2.FindFirst "[SO] like '*" & Trim(Me.SO) & "*' and " & Me.Filter

lngAbsolute = rst1.AbsolutePosition
lngBaseCount = rst2.AbsolutePosition
lngTotalSO = DCount("UNIQID", "qryJobCardsAll", "[SO]='" & Me.SO & "'")
lngRecord = lngAbsolute - lngBaseCount + 1

'Show the result of the record count in the text box )
Me.txtCardCount = "Items on SO: " & lngRecord & " of " & lngTotalSO

Set rst1 = Nothing
Set rst2 = Nothing

End Sub