inconsistant results with Onformat

[UnderGround]

Hi,
I am using code in the Onformat event to assign values to a textbox
from a query. The problem i am having is that if i paste the same code
in the OnPrint method it works fine if you browse the pages in a
sequence but if you jump directly on the last page then the date will
be wrong. Only the first page is correct the rest have data duplicated
from the first page and showing wrong data for the records. If i use
the same code in the Onformat only the last page is correct (no matter
how i view it) and the last record is duplicated in all the records in
all the pages except the last page. I cant figure out whats going
wrong. Can anyone help Thanks.




Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If (Not rs.EOF) Then
If Me.FormatCount = 1 Then (***** I change this line to
Me.Printcount for the OnPrint method)

Me.txtName.Value = rs("[Name]")
Me.txtCompanyName.Value = rs("[Company Name]")
Me.txtABNNo.Value = rs("[ABN No]")
Me.txtACNNo.Value = rs("[ACN No]")

If (IsNull(rs("[Policy Expiry Date]"))) Then
Me.txtPolicyExpDate.Value = "NOT ENTERED"
Else
strPolicyExpDate = DateValue(rs("[Policy Expiry Date]"))
If (DateDiff("d", (DateAdd("d", 28, Date)), strPolicyExpDate) <
0) Then
Me.txtPolicyExpDate.Value = strPolicyExpDate
Else
Me.txtPolicyExpDate.Value = ""
End If
End If


If (IsNull(rs("[Public Liability Policy Expiry Date]"))) Then
Me.txtLiabilityExpDate.Value = "NOT ENTERED"
Else
strLaibilityExpDate = DateValue(rs("[Public Liability Policy
Expiry Date]"))
If (DateDiff("d", (DateAdd("d", 28, Date)),
strLaibilityExpDate) < 0) Then
Me.txtLiabilityExpDate.Value = strLaibilityExpDate
Else
Me.txtLiabilityExpDate.Value = ""
End If
End If



rs.MoveNext
End If
End If


End Sub

 

[Guest]

The problem is most likely a result of there being two iterations through the
report, probably due to the use of the Pages property in the page footer to
show the total number of pages in the report. You need the code to execute
only on the second iteration. The way to do this is to declare a module
level Boolean variable in the report's module and set its value to True in
the report footer's Format event procedure. Make the code execution in the
detail section's Format event procedure conditional on the value of the
variable being True.

Ken Sheridan
Stafford, England

 

[Allen Browne]

The nub of the issue is that Access fires the events only when it needs to.
As you found, it may not fire the events for the intervening pages when you
print only the later pages of a report. It follows that you cannot reliably
using this approach to cover anything that spans more than one page. We are
therefore looking for an alternative approach.

Your code seems to be looking up fields relating to the data in the report.
The simplest solution might be to get that data into the report's
RecordSource so you do not need the code.

Create a query that uses the same tables you currently have for your report,
along with the tables you used to OpenRecordset. If this gets is too messy
for some reason, you could create a query that uses the existing queries as
input "tables" (a query stacked on top of other queries.) You may need to
use outer joins: more info on that:
http://allenbrowne.com/casu-02.html

If there is some problem with supplying parameters to handle the specific
cases your report covers, other alternatives include building the SQL
statement in the report's Open event, and assigning the string to its
RecordSource. If that is still not enough, you can even build the SQL string
for one of the lower-level stacked queries and assign to
dbEngine(0)(0).QueryDefs("Query1").SQL before you open the report.

As a general guide, it is almost alwasys preferable to solve the problem in
SQL rather than VBA. In practice < 10% of my reports have a module at all,
whereas the stuff that happens in generating the SQL for the report can get
rather involved.

Please post a reply if it is not clear how to take that approach.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi,
I am using code in the Onformat event to assign values to a textbox
from a query. The problem i am having is that if i paste the same code
in the OnPrint method it works fine if you browse the pages in a
sequence but if you jump directly on the last page then the date will
be wrong. Only the first page is correct the rest have data duplicated
from the first page and showing wrong data for the records. If i use
the same code in the Onformat only the last page is correct (no matter
how i view it) and the last record is duplicated in all the records in
all the pages except the last page. I cant figure out whats going
wrong. Can anyone help Thanks.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If (Not rs.EOF) Then
If Me.FormatCount = 1 Then (***** I change this line to
Me.Printcount for the OnPrint method)

Me.txtName.Value = rs("[Name]")
Me.txtCompanyName.Value = rs("[Company Name]")
Me.txtABNNo.Value = rs("[ABN No]")
Me.txtACNNo.Value = rs("[ACN No]")

If (IsNull(rs("[Policy Expiry Date]"))) Then
Me.txtPolicyExpDate.Value = "NOT ENTERED"
Else
strPolicyExpDate = DateValue(rs("[Policy Expiry Date]"))
If (DateDiff("d", (DateAdd("d", 28, Date)), strPolicyExpDate) <
0) Then
Me.txtPolicyExpDate.Value = strPolicyExpDate
Else
Me.txtPolicyExpDate.Value = ""
End If
End If

If (IsNull(rs("[Public Liability Policy Expiry Date]"))) Then
Me.txtLiabilityExpDate.Value = "NOT ENTERED"
Else
strLaibilityExpDate = DateValue(rs("[Public Liability Policy
Expiry Date]"))
If (DateDiff("d", (DateAdd("d", 28, Date)),
strLaibilityExpDate) < 0) Then
Me.txtLiabilityExpDate.Value = strLaibilityExpDate
Else
Me.txtLiabilityExpDate.Value = ""
End If
End If

rs.MoveNext
End If
End If

End Sub

 

[UnderGround]

Thanks alot guys. I have used sql as you suggested.