Access 2003 Report & VB Problem

D

dannie

Overview: I have a report that pulls its info from a table and each time it
moves to a new record I have VB code which manipulates the information how I
want.

Problem: I can flip through the pages to the right but lets say I go to
record 20 and then start flipping backwards, it does not call my code under
Report_Activate() or Report_Page() for 9 records, these 9 records are
displayed correctly from memory but are not being updated. The 10th record
recieves the correct record information but uses the record 20 case, even
though it runs through the case 10 code when I debug it. Every record after
that is 1 case off from what it should be.

Possible Solutions: Is there any way to make my VB code called when I go
back records? Does anyone know why it waits 10 records before it starts
calling code again? Is there somewhere that you can set how many records it
keeps on memory or something?
 
M

Marshall Barton

dannie said:
Overview: I have a report that pulls its info from a table and each time it
moves to a new record I have VB code which manipulates the information how I
want.

Problem: I can flip through the pages to the right but lets say I go to
record 20 and then start flipping backwards, it does not call my code under
Report_Activate() or Report_Page() for 9 records, these 9 records are
displayed correctly from memory but are not being updated. The 10th record
recieves the correct record information but uses the record 20 case, even
though it runs through the case 10 code when I debug it. Every record after
that is 1 case off from what it should be.

Possible Solutions: Is there any way to make my VB code called when I go
back records? Does anyone know why it waits 10 records before it starts
calling code again? Is there somewhere that you can set how many records it
keeps on memory or something?
Click to expand...


No. Your're off on some wrong road with your code.

Without seeing the code, all I can do is guess that your
code is calculating some kind of total, which is a definite
No No. Totals should either be done in the report's record
source query, in a report text box using an aggregate
function or in a report text box using its RunningSum
property.
 
D

dannie

Marshall Barton said:
No. Your're off on some wrong road with your code.

Without seeing the code, all I can do is guess that your
code is calculating some kind of total, which is a definite
No No. Totals should either be done in the report's record
source query, in a report text box using an aggregate
function or in a report text box using its RunningSum
property.
Click to expand...

My report has its source as the table which stores the data. In that table
is a field which stores text (MH1, MH2, .... MH21) which is inputed using a
form. The report has a text box which has its control source as the field
where this variable is stored.
In the VB code I have ....
If Reports!PEC_StormDrainManholeReport!txtMHtype.Text = "MH1" Then
and I have 1 of these for each case of the 21 cases. I have set a watch to
see what this value is and as I click the right button moving into further
reports it updates it the value properly every time but when I go backwards
it does nothing for 9 records and then uses the wrong record. Report_Page()
is not being called as I go backwards as I set a breakpoint which was not
activated until the 10th record I went to the in backwards direction. I can
replicate the exact same results no matter how far into the data I go. Any
ideas?
 
M

Marshall Barton

dannie said:
My report has its source as the table which stores the data. In that table
is a field which stores text (MH1, MH2, .... MH21) which is inputed using a
form. The report has a text box which has its control source as the field
where this variable is stored.
In the VB code I have ....
If Reports!PEC_StormDrainManholeReport!txtMHtype.Text = "MH1" Then
and I have 1 of these for each case of the 21 cases. I have set a watch to
see what this value is and as I click the right button moving into further
reports it updates it the value properly every time but when I go backwards
it does nothing for 9 records and then uses the wrong record. Report_Page()
is not being called as I go backwards as I set a breakpoint which was not
activated until the 10th record I went to the in backwards direction. I can
replicate the exact same results no matter how far into the data I go. Any
ideas?
Click to expand...


Still guessing, but I suspect that there may be some total
type calculation in the code after the If

IMPORTANT: You should not be using the Text property, use
the Value property instead. I don't know why you are not
getting some kind of error and I don't think it's the root
cause of your problem, but since Value is the default
property, you do not even need to specify it. I think this
should be sufficient:

If Met!txtMHtype="MH1" Then

But, instead of using 21 If blocks, a Select Case structure
would be cleaner.
 
D

dannie

Marshall Barton said:
Still guessing, but I suspect that there may be some total
type calculation in the code after the If

IMPORTANT: You should not be using the Text property, use
the Value property instead. I don't know why you are not
getting some kind of error and I don't think it's the root
cause of your problem, but since Value is the default
property, you do not even need to specify it. I think this
should be sufficient:

If Met!txtMHtype="MH1" Then

But, instead of using 21 If blocks, a Select Case structure
would be cleaner.
Click to expand...

I took away the .text and everything still worked so thats up to date but it
didnt fix the problem yet. Of the 21 If blocks I have here is an example of
a complete block:
ElseIf Reports!PEC_StormDrainManholeReport!txtMHtype = "MH8" Then
Reports!PEC_StormDrainManholeReport!imgMH8.Visible = True
Reports!PEC_StormDrainManholeReport!txtChannelType.Visible = False
'positioning cone depth
Reports!PEC_StormDrainManholeReport!txtConeDepth.Top = 9400
Reports!PEC_StormDrainManholeReport!txtConeDepth.Left = 4450
'positioning wall diameter
Reports!PEC_StormDrainManholeReport!txtWallDiameter.Top = 10000
Reports!PEC_StormDrainManholeReport!txtWallDiameter.Left = 2650
'positioning wall depth
Reports!PEC_StormDrainManholeReport!txtWallDepth.Top = 10100
Reports!PEC_StormDrainManholeReport!txtWallDepth.Left = 4450
'positioning chimney depth
Reports!PEC_StormDrainManholeReport!txtChimneyDepth.Top = 8850
Reports!PEC_StormDrainManholeReport!txtChimneyDepth.Left = 4450
'positioning depth to invert
Reports!PEC_StormDrainManholeReport!txtRimToInvert.Top = 10550
Reports!PEC_StormDrainManholeReport!txtRimToInvert.Left = 4950
'positioning cover size
Reports!PEC_StormDrainManholeReport!txtSize.Top = 8100
Reports!PEC_StormDrainManholeReport!txtSize.Left = 2600
Reports!PEC_StormDrainManholeReport!txtSizeOther.Top = 8100
Reports!PEC_StormDrainManholeReport!txtSizeOther.Left = 2600
'positioning pipe diameter
Reports!PEC_StormDrainManholeReport!txtMainPipeSize.Top = 12100
Reports!PEC_StormDrainManholeReport!txtMainPipeSize.Left = 2600
'positioning spring line
Reports!PEC_StormDrainManholeReport!txtSpringLine.Top = 11950
Reports!PEC_StormDrainManholeReport!txtSpringLine.Left = 4300
'positioning box dimensions
Reports!PEC_StormDrainManholeReport!txtBoxDimensions.Top = 11150
Reports!PEC_StormDrainManholeReport!txtBoxDimensions.Left = 2300
Reports!PEC_StormDrainManholeReport!txtBoxDepth.Top = 11600
Reports!PEC_StormDrainManholeReport!txtBoxDepth.Left = 4500
'positioning box length
Reports!PEC_StormDrainManholeReport!txtBoxLength.Top = 5500
Reports!PEC_StormDrainManholeReport!txtBoxLength.Left = 4300
'positioning box width
Reports!PEC_StormDrainManholeReport!txtBoxWidth.Top = 7000
Reports!PEC_StormDrainManholeReport!txtBoxWidth.Left = 2650

As you can see its all very simple code and I dont have any calculations,
maybe it is the setting of one of my tables or fields in my tables? I found
another way to show pretty much the same error, if I open up my report and
print from there it prints everything the way I want, if I move to another
record and print it prints everything correct except the first record. It
prints the correct data but prints in whatever case I stopped at before I
print. This would lead me to believe that when I print it does not run my
code first, but this also confuses me because if that was the case why
wouldnt all the records print out the same so it runs my code after the first
record I guess.
 
M

Marshall Barton

dannie said:
Of the 21 If blocks I have here is an example of a complete block:
ElseIf Reports!PEC_StormDrainManholeReport!txtMHtype = "MH8" Then
Reports!PEC_StormDrainManholeReport!imgMH8.Visible = True
Reports!PEC_StormDrainManholeReport!txtChannelType.Visible = False
'positioning cone depth
Reports!PEC_StormDrainManholeReport!txtConeDepth.Top = 9400
Reports!PEC_StormDrainManholeReport!txtConeDepth.Left = 4450
'positioning wall diameter
Reports!PEC_StormDrainManholeReport!txtWallDiameter.Top = 10000
Reports!PEC_StormDrainManholeReport!txtWallDiameter.Left = 2650
'positioning wall depth
Reports!PEC_StormDrainManholeReport!txtWallDepth.Top = 10100
Reports!PEC_StormDrainManholeReport!txtWallDepth.Left = 4450
'positioning chimney depth
Reports!PEC_StormDrainManholeReport!txtChimneyDepth.Top = 8850
Reports!PEC_StormDrainManholeReport!txtChimneyDepth.Left = 4450
'positioning depth to invert
Reports!PEC_StormDrainManholeReport!txtRimToInvert.Top = 10550
Reports!PEC_StormDrainManholeReport!txtRimToInvert.Left = 4950
'positioning cover size
Reports!PEC_StormDrainManholeReport!txtSize.Top = 8100
Reports!PEC_StormDrainManholeReport!txtSize.Left = 2600
Reports!PEC_StormDrainManholeReport!txtSizeOther.Top = 8100
Reports!PEC_StormDrainManholeReport!txtSizeOther.Left = 2600
'positioning pipe diameter
Reports!PEC_StormDrainManholeReport!txtMainPipeSize.Top = 12100
Reports!PEC_StormDrainManholeReport!txtMainPipeSize.Left = 2600
'positioning spring line
Reports!PEC_StormDrainManholeReport!txtSpringLine.Top = 11950
Reports!PEC_StormDrainManholeReport!txtSpringLine.Left = 4300
'positioning box dimensions
Reports!PEC_StormDrainManholeReport!txtBoxDimensions.Top = 11150
Reports!PEC_StormDrainManholeReport!txtBoxDimensions.Left = 2300
Reports!PEC_StormDrainManholeReport!txtBoxDepth.Top = 11600
Reports!PEC_StormDrainManholeReport!txtBoxDepth.Left = 4500
'positioning box length
Reports!PEC_StormDrainManholeReport!txtBoxLength.Top = 5500
Reports!PEC_StormDrainManholeReport!txtBoxLength.Left = 4300
'positioning box width
Reports!PEC_StormDrainManholeReport!txtBoxWidth.Top = 7000
Reports!PEC_StormDrainManholeReport!txtBoxWidth.Left = 2650

As you can see its all very simple code and I dont have any calculations,
maybe it is the setting of one of my tables or fields in my tables? I found
another way to show pretty much the same error, if I open up my report and
print from there it prints everything the way I want, if I move to another
record and print it prints everything correct except the first record. It
prints the correct data but prints in whatever case I stopped at before I
print. This would lead me to believe that when I print it does not run my
code first, but this also confuses me because if that was the case why
wouldnt all the records print out the same so it runs my code after the first
record I guess.
Click to expand...


OK, that clarifies things a lot. I guess I shouldn't guess
so much ;-)

After rereading your other posts, I note that you said
"it does not call my code under
Report_Activate() or Report_Page()"
I should have picked up on that right away. The code needs
to be in the detail section's Format event procedure for it
to work for each record.

Side Note:
Using Me instead of
Reports!PEC_StormDrainManholeReport
would make all that code a lot easier to type and even
easier to read. In your situation, especially with so many
cases, I would go a step further use a With statement before
the first If (or Select Case):

With Reports!PEC_StormDrainManholeReport
If !txtMHtype = "MH8" Then
!imgMH8.Visible = True
!txtChannelType.Visible = False
. . .
 
D

dannie

Marshall Barton said:
OK, that clarifies things a lot. I guess I shouldn't guess
so much ;-)

After rereading your other posts, I note that you said
"it does not call my code under
Report_Activate() or Report_Page()"
I should have picked up on that right away. The code needs
to be in the detail section's Format event procedure for it
to work for each record.

Side Note:
Using Me instead of
Reports!PEC_StormDrainManholeReport
would make all that code a lot easier to type and even
easier to read. In your situation, especially with so many
cases, I would go a step further use a With statement before
the first If (or Select Case):

With Reports!PEC_StormDrainManholeReport
If !txtMHtype = "MH8" Then
!imgMH8.Visible = True
!txtChannelType.Visible = False
. . .
Click to expand...

OK, so I put in that "Me" code, which I didnt know existed, and it is much
easier to read so thank you for that tip. Now I put the call for my code
under On Format of the detail section and when I go backwards for 9 records
it still doesnt update my watch item until the 10th record BUT it did show
the 10th item correctly. So my problem is fixxed but I am still not sure
what the deal is with it not updating for 9 records. Thanks so much for the
help!!!
 
M

Marshall Barton

dannie said:
OK, so I put in that "Me" code, which I didnt know existed, and it is much
easier to read so thank you for that tip. Now I put the call for my code
under On Format of the detail section and when I go backwards for 9 records
it still doesnt update my watch item until the 10th record BUT it did show
the 10th item correctly. So my problem is fixxed but I am still not sure
what the deal is with it not updating for 9 records. Thanks so much for the
help!!!
Click to expand...


If the only thing that you don't like is a watch not showing
a change, it seems like it's that Watch only updates when
the thingie you are watching changes. But, with reports
jumping ahead causes a lot of formatting to take place so
Access can figure out what is on the page you are jumping
to. Access probably "remembers" what it has already done so
when you move backwards, it doesn't have to do it again.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access 2003 Report & VB Problem 1
Adding data to MS Access from csv file 0
Macro for manipulating data 0
Converting GPS coordinates to decimal in Report 0
Sub Report Problem 0
Follow-up to DAO.Recordset as Report record source 3
Result summary in a report 1
type mismatch error in macro in excel 0

Top