Reset Page Numer & Total Page Count for each Report Group

R

RCR

Hi There

I am trying to reset the page number and page count for each group in my
database report. I downloaded Article ID 841779, and although I am not too
great at Visual Basic, I managed to use the example in the Northwind
database, which worked perfectly.
However, when I try and adapt the code to run on my database I get the
following error message 'The expression On Open you entered as event property
setting produced following error : User-defined type not defined'. The only
alterations I made to the code was replace [Country] with my group [ATA]. The
table that I created to store group names and pages is same as example. Also,
as I don't have much code in my database, I was unsure where to apend the
definition of variables for Database & Recordset (see copy of code)

Option Compare Database

Dim DB As Database
Dim GrpPages As Recordset
Function GetGrpPages()
'Find the group name.
GrpPages.Seek "=", Me![ATA]
If Not GrpPages.NoMatch Then
GetGrpPages = GrpPages![Page Number]
End If
End Function


Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
'Set page number to 1 when new group starts.
Page = 1
End Sub

Private Sub PageFooter3_Format(Cancel As Integer, FormatCount As Integer)
'Find the group.
GrpPages.Seek "=", Me![ATA]

If Not GrpPages.NoMatch Then
'The group is already there.
If GrpPages![Page Number] < Me.Page Then
GrpPages.Edit
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
Else
'This is the first page of the group. Therefore, add it.
GrpPages.AddNew
GrpPages![ATA] = Me![ATA]
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
End Sub

Private Sub Report_Open(Cancel As Integer)
Set DB = DBEngine.Workspaces(0).Databases(0)
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From [Category Group Pages];"
DoCmd.SetWarnings True
Set GrpPages = DB.OpenRecordset("Category Group Pages", DB_OPEN_TABLE)
GrpPages.Index = "PrimaryKey"

End Sub

If anyone could help in sorting this problem, I would be most grateful
 
S

steveh

In the VB Editor, go to Tools >> References and check the box by
Microsoft DAO 3.x Object Library.

Steve
 
R

RCR

Thanks for your reply. I have checked box by Microsoft DAO 3.6 Object Library.

Now when I run report, I get 'Run-time error 13. Type Mismatch' Debug
highlights the following code - Set GrpPages = DB.OpenRecordset("Category
Group Pages", DB_OPEN_TABLE)

Any help would be much appreciated

RCR

steveh said:
In the VB Editor, go to Tools >> References and check the box by
Microsoft DAO 3.x Object Library.

Steve

Hi There

I am trying to reset the page number and page count for each group in my
database report. I downloaded Article ID 841779, and although I am not too
great at Visual Basic, I managed to use the example in the Northwind
database, which worked perfectly.
However, when I try and adapt the code to run on my database I get the
following error message 'The expression On Open you entered as event property
setting produced following error : User-defined type not defined'. The only
alterations I made to the code was replace [Country] with my group [ATA]. The
table that I created to store group names and pages is same as example. Also,
as I don't have much code in my database, I was unsure where to apend the
definition of variables for Database & Recordset (see copy of code)

Option Compare Database

Dim DB As Database
Dim GrpPages As Recordset
Function GetGrpPages()
'Find the group name.
GrpPages.Seek "=", Me![ATA]
If Not GrpPages.NoMatch Then
GetGrpPages = GrpPages![Page Number]
End If
End Function


Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
'Set page number to 1 when new group starts.
Page = 1
End Sub

Private Sub PageFooter3_Format(Cancel As Integer, FormatCount As Integer)
'Find the group.
GrpPages.Seek "=", Me![ATA]

If Not GrpPages.NoMatch Then
'The group is already there.
If GrpPages![Page Number] < Me.Page Then
GrpPages.Edit
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
Else
'This is the first page of the group. Therefore, add it.
GrpPages.AddNew
GrpPages![ATA] = Me![ATA]
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
End Sub

Private Sub Report_Open(Cancel As Integer)
Set DB = DBEngine.Workspaces(0).Databases(0)
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From [Category Group Pages];"
DoCmd.SetWarnings True
Set GrpPages = DB.OpenRecordset("Category Group Pages", DB_OPEN_TABLE)
GrpPages.Index = "PrimaryKey"

End Sub

If anyone could help in sorting this problem, I would be most grateful
Click to expand...
Click to expand...
 
S

steveh

Clear any references to Microsoft ActiveX Data Objects (if you are not
using them), or at least make sure they have a lower priority than the
DAO. Or, dimension your objects like this:

Dim DB As DAO.Database
Dim GrpPages As DAO.Recordset


Steve

Thanks for your reply. I have checked box by Microsoft DAO 3.6 Object Library.

Now when I run report, I get 'Run-time error 13. Type Mismatch' Debug
highlights the following code - Set GrpPages = DB.OpenRecordset("Category
Group Pages", DB_OPEN_TABLE)

Any help would be much appreciated

RCR

steveh said:
In the VB Editor, go to Tools >> References and check the box by
Microsoft DAO 3.x Object Library.

Steve

Hi There

I am trying to reset the page number and page count for each group in my
database report. I downloaded Article ID 841779, and although I am not too
great at Visual Basic, I managed to use the example in the Northwind
database, which worked perfectly.
However, when I try and adapt the code to run on my database I get the
following error message 'The expression On Open you entered as event property
setting produced following error : User-defined type not defined'. The only
alterations I made to the code was replace [Country] with my group [ATA]. The
table that I created to store group names and pages is same as example. Also,
as I don't have much code in my database, I was unsure where to apend the
definition of variables for Database & Recordset (see copy of code)

Option Compare Database

Dim DB As Database
Dim GrpPages As Recordset
Function GetGrpPages()
'Find the group name.
GrpPages.Seek "=", Me![ATA]
If Not GrpPages.NoMatch Then
GetGrpPages = GrpPages![Page Number]
End If
End Function


Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
'Set page number to 1 when new group starts.
Page = 1
End Sub

Private Sub PageFooter3_Format(Cancel As Integer, FormatCount As Integer)
'Find the group.
GrpPages.Seek "=", Me![ATA]

If Not GrpPages.NoMatch Then
'The group is already there.
If GrpPages![Page Number] < Me.Page Then
GrpPages.Edit
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
Else
'This is the first page of the group. Therefore, add it.
GrpPages.AddNew
GrpPages![ATA] = Me![ATA]
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
End Sub

Private Sub Report_Open(Cancel As Integer)
Set DB = DBEngine.Workspaces(0).Databases(0)
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From [Category Group Pages];"
DoCmd.SetWarnings True
Set GrpPages = DB.OpenRecordset("Category Group Pages", DB_OPEN_TABLE)
GrpPages.Index = "PrimaryKey"

End Sub

If anyone could help in sorting this problem, I would be most grateful
Click to expand...
Click to expand...
Click to expand...
 
R

RCR

Thanks again for your reply. I have tried all of your suggestions, but now
get error message "Run-time error 91. Object Variable or with block variable
not set". If you select debug then following line is highlighted -
GrpPages.Seek "=",Me![ATA]

I would be grateful for any further help.


RCR




steveh said:
Clear any references to Microsoft ActiveX Data Objects (if you are not
using them), or at least make sure they have a lower priority than the
DAO. Or, dimension your objects like this:

Dim DB As DAO.Database
Dim GrpPages As DAO.Recordset


Steve

Thanks for your reply. I have checked box by Microsoft DAO 3.6 Object Library.

Now when I run report, I get 'Run-time error 13. Type Mismatch' Debug
highlights the following code - Set GrpPages = DB.OpenRecordset("Category
Group Pages", DB_OPEN_TABLE)

Any help would be much appreciated

RCR

steveh said:
In the VB Editor, go to Tools >> References and check the box by
Microsoft DAO 3.x Object Library.

Steve


RCR wrote:
Hi There

I am trying to reset the page number and page count for each group in my
database report. I downloaded Article ID 841779, and although I am not too
great at Visual Basic, I managed to use the example in the Northwind
database, which worked perfectly.
However, when I try and adapt the code to run on my database I get the
following error message 'The expression On Open you entered as event property
setting produced following error : User-defined type not defined'. The only
alterations I made to the code was replace [Country] with my group [ATA]. The
table that I created to store group names and pages is same as example. Also,
as I don't have much code in my database, I was unsure where to apend the
definition of variables for Database & Recordset (see copy of code)

Option Compare Database

Dim DB As Database
Dim GrpPages As Recordset
Function GetGrpPages()
'Find the group name.
GrpPages.Seek "=", Me![ATA]
If Not GrpPages.NoMatch Then
GetGrpPages = GrpPages![Page Number]
End If
End Function


Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
'Set page number to 1 when new group starts.
Page = 1
End Sub

Private Sub PageFooter3_Format(Cancel As Integer, FormatCount As Integer)
'Find the group.
GrpPages.Seek "=", Me![ATA]

If Not GrpPages.NoMatch Then
'The group is already there.
If GrpPages![Page Number] < Me.Page Then
GrpPages.Edit
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
Else
'This is the first page of the group. Therefore, add it.
GrpPages.AddNew
GrpPages![ATA] = Me![ATA]
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
End Sub

Private Sub Report_Open(Cancel As Integer)
Set DB = DBEngine.Workspaces(0).Databases(0)
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From [Category Group Pages];"
DoCmd.SetWarnings True
Set GrpPages = DB.OpenRecordset("Category Group Pages", DB_OPEN_TABLE)
GrpPages.Index = "PrimaryKey"

End Sub

If anyone could help in sorting this problem, I would be most grateful
Click to expand...
Click to expand...
Click to expand...
 
S

steveh

Please list the references you have, in order of priority, and your code
for the dimensioning of DB and GrpPages. Are they the same as what you
have in the Northwind sample?

Thanks again for your reply. I have tried all of your suggestions, but now
get error message "Run-time error 91. Object Variable or with block variable
not set". If you select debug then following line is highlighted -
GrpPages.Seek "=",Me![ATA]

I would be grateful for any further help.


RCR




steveh said:
Clear any references to Microsoft ActiveX Data Objects (if you are not
using them), or at least make sure they have a lower priority than the
DAO. Or, dimension your objects like this:

Dim DB As DAO.Database
Dim GrpPages As DAO.Recordset


Steve

Thanks for your reply. I have checked box by Microsoft DAO 3.6 Object Library.

Now when I run report, I get 'Run-time error 13. Type Mismatch' Debug
highlights the following code - Set GrpPages = DB.OpenRecordset("Category
Group Pages", DB_OPEN_TABLE)

Any help would be much appreciated

RCR

:

In the VB Editor, go to Tools >> References and check the box by
Microsoft DAO 3.x Object Library.

Steve


RCR wrote:
Hi There

I am trying to reset the page number and page count for each group in my
database report. I downloaded Article ID 841779, and although I am not too
great at Visual Basic, I managed to use the example in the Northwind
database, which worked perfectly.
However, when I try and adapt the code to run on my database I get the
following error message 'The expression On Open you entered as event property
setting produced following error : User-defined type not defined'. The only
alterations I made to the code was replace [Country] with my group [ATA]. The
table that I created to store group names and pages is same as example. Also,
as I don't have much code in my database, I was unsure where to apend the
definition of variables for Database & Recordset (see copy of code)

Option Compare Database

Dim DB As Database
Dim GrpPages As Recordset
Function GetGrpPages()
'Find the group name.
GrpPages.Seek "=", Me![ATA]
If Not GrpPages.NoMatch Then
GetGrpPages = GrpPages![Page Number]
End If
End Function


Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
'Set page number to 1 when new group starts.
Page = 1
End Sub

Private Sub PageFooter3_Format(Cancel As Integer, FormatCount As Integer)
'Find the group.
GrpPages.Seek "=", Me![ATA]

If Not GrpPages.NoMatch Then
'The group is already there.
If GrpPages![Page Number] < Me.Page Then
GrpPages.Edit
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
Else
'This is the first page of the group. Therefore, add it.
GrpPages.AddNew
GrpPages![ATA] = Me![ATA]
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
End Sub

Private Sub Report_Open(Cancel As Integer)
Set DB = DBEngine.Workspaces(0).Databases(0)
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From [Category Group Pages];"
DoCmd.SetWarnings True
Set GrpPages = DB.OpenRecordset("Category Group Pages", DB_OPEN_TABLE)
GrpPages.Index = "PrimaryKey"

End Sub

If anyone could help in sorting this problem, I would be most grateful
Click to expand...
Click to expand...
Click to expand...
 
R

RCR

Thanks for your reply.
List of references in following order -
Visual Basic For Applications
Microsoft Access 11.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Objects 2.1 Library
OLE Automation

Dimensioning of DB & GrpPages as you recommended -

Dim DB As DAO.Database
Dim GrpPages As DAO.Recordset

steveh said:
Please list the references you have, in order of priority, and your code
for the dimensioning of DB and GrpPages. Are they the same as what you
have in the Northwind sample?

Thanks again for your reply. I have tried all of your suggestions, but now
get error message "Run-time error 91. Object Variable or with block variable
not set". If you select debug then following line is highlighted -
GrpPages.Seek "=",Me![ATA]

I would be grateful for any further help.


RCR




steveh said:
Clear any references to Microsoft ActiveX Data Objects (if you are not
using them), or at least make sure they have a lower priority than the
DAO. Or, dimension your objects like this:

Dim DB As DAO.Database
Dim GrpPages As DAO.Recordset


Steve


RCR wrote:
Thanks for your reply. I have checked box by Microsoft DAO 3.6 Object Library.

Now when I run report, I get 'Run-time error 13. Type Mismatch' Debug
highlights the following code - Set GrpPages = DB.OpenRecordset("Category
Group Pages", DB_OPEN_TABLE)

Any help would be much appreciated

RCR

:

In the VB Editor, go to Tools >> References and check the box by
Microsoft DAO 3.x Object Library.

Steve


RCR wrote:
Hi There

I am trying to reset the page number and page count for each group in my
database report. I downloaded Article ID 841779, and although I am not too
great at Visual Basic, I managed to use the example in the Northwind
database, which worked perfectly.
However, when I try and adapt the code to run on my database I get the
following error message 'The expression On Open you entered as event property
setting produced following error : User-defined type not defined'. The only
alterations I made to the code was replace [Country] with my group [ATA]. The
table that I created to store group names and pages is same as example. Also,
as I don't have much code in my database, I was unsure where to apend the
definition of variables for Database & Recordset (see copy of code)

Option Compare Database

Dim DB As Database
Dim GrpPages As Recordset
Function GetGrpPages()
'Find the group name.
GrpPages.Seek "=", Me![ATA]
If Not GrpPages.NoMatch Then
GetGrpPages = GrpPages![Page Number]
End If
End Function


Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
'Set page number to 1 when new group starts.
Page = 1
End Sub

Private Sub PageFooter3_Format(Cancel As Integer, FormatCount As Integer)
'Find the group.
GrpPages.Seek "=", Me![ATA]

If Not GrpPages.NoMatch Then
'The group is already there.
If GrpPages![Page Number] < Me.Page Then
GrpPages.Edit
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
Else
'This is the first page of the group. Therefore, add it.
GrpPages.AddNew
GrpPages![ATA] = Me![ATA]
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
End Sub

Private Sub Report_Open(Cancel As Integer)
Set DB = DBEngine.Workspaces(0).Databases(0)
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From [Category Group Pages];"
DoCmd.SetWarnings True
Set GrpPages = DB.OpenRecordset("Category Group Pages", DB_OPEN_TABLE)
GrpPages.Index = "PrimaryKey"

End Sub

If anyone could help in sorting this problem, I would be most grateful
Click to expand...
Click to expand...
Click to expand...
 
S

steveh

So far so good. Did something happen to your Report_Open subroutine? The
GrpPages object should have been set there. Wasn't all the code you had
listed earlier taken from the report object?

Thanks for your reply.
List of references in following order -
Visual Basic For Applications
Microsoft Access 11.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Objects 2.1 Library
OLE Automation

Dimensioning of DB & GrpPages as you recommended -

Dim DB As DAO.Database
Dim GrpPages As DAO.Recordset

steveh said:
Please list the references you have, in order of priority, and your code
for the dimensioning of DB and GrpPages. Are they the same as what you
have in the Northwind sample?

Thanks again for your reply. I have tried all of your suggestions, but now
get error message "Run-time error 91. Object Variable or with block variable
not set". If you select debug then following line is highlighted -
GrpPages.Seek "=",Me![ATA]

I would be grateful for any further help.


RCR




:

Clear any references to Microsoft ActiveX Data Objects (if you are not
using them), or at least make sure they have a lower priority than the
DAO. Or, dimension your objects like this:

Dim DB As DAO.Database
Dim GrpPages As DAO.Recordset


Steve


RCR wrote:
Thanks for your reply. I have checked box by Microsoft DAO 3.6 Object Library.

Now when I run report, I get 'Run-time error 13. Type Mismatch' Debug
highlights the following code - Set GrpPages = DB.OpenRecordset("Category
Group Pages", DB_OPEN_TABLE)

Any help would be much appreciated

RCR

:

In the VB Editor, go to Tools >> References and check the box by
Microsoft DAO 3.x Object Library.

Steve


RCR wrote:
Hi There

I am trying to reset the page number and page count for each group in my
database report. I downloaded Article ID 841779, and although I am not too
great at Visual Basic, I managed to use the example in the Northwind
database, which worked perfectly.
However, when I try and adapt the code to run on my database I get the
following error message 'The expression On Open you entered as event property
setting produced following error : User-defined type not defined'. The only
alterations I made to the code was replace [Country] with my group [ATA]. The
table that I created to store group names and pages is same as example. Also,
as I don't have much code in my database, I was unsure where to apend the
definition of variables for Database & Recordset (see copy of code)

Option Compare Database

Dim DB As Database
Dim GrpPages As Recordset
Function GetGrpPages()
'Find the group name.
GrpPages.Seek "=", Me![ATA]
If Not GrpPages.NoMatch Then
GetGrpPages = GrpPages![Page Number]
End If
End Function


Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
'Set page number to 1 when new group starts.
Page = 1
End Sub

Private Sub PageFooter3_Format(Cancel As Integer, FormatCount As Integer)
'Find the group.
GrpPages.Seek "=", Me![ATA]

If Not GrpPages.NoMatch Then
'The group is already there.
If GrpPages![Page Number] < Me.Page Then
GrpPages.Edit
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
Else
'This is the first page of the group. Therefore, add it.
GrpPages.AddNew
GrpPages![ATA] = Me![ATA]
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
End Sub

Private Sub Report_Open(Cancel As Integer)
Set DB = DBEngine.Workspaces(0).Databases(0)
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From [Category Group Pages];"
DoCmd.SetWarnings True
Set GrpPages = DB.OpenRecordset("Category Group Pages", DB_OPEN_TABLE)
GrpPages.Index = "PrimaryKey"

End Sub

If anyone could help in sorting this problem, I would be most grateful
Click to expand...
Click to expand...
Click to expand...
 
R

RCR

Not too sure what you mean here. The code I am using is exactly the same as
used in Northwind example listed in my initial query, just the [country]
group is changed to my group [ATA]. My last reply was just to show changes
made to dimensioning of DB and GrpPages as per your recommendations. The
Private Sub Report open procecedure still contains the line

Set GrpPages = DB.OpenRecordset("Category Group Pages", DB_OPEN_TABLE)

My apologies if I have misunderstood your question.

steveh said:
So far so good. Did something happen to your Report_Open subroutine? The
GrpPages object should have been set there. Wasn't all the code you had
listed earlier taken from the report object?

Thanks for your reply.
List of references in following order -
Visual Basic For Applications
Microsoft Access 11.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Objects 2.1 Library
OLE Automation

Dimensioning of DB & GrpPages as you recommended -

Dim DB As DAO.Database
Dim GrpPages As DAO.Recordset

steveh said:
Please list the references you have, in order of priority, and your code
for the dimensioning of DB and GrpPages. Are they the same as what you
have in the Northwind sample?


RCR wrote:
Thanks again for your reply. I have tried all of your suggestions, but now
get error message "Run-time error 91. Object Variable or with block variable
not set". If you select debug then following line is highlighted -
GrpPages.Seek "=",Me![ATA]

I would be grateful for any further help.


RCR




:

Clear any references to Microsoft ActiveX Data Objects (if you are not
using them), or at least make sure they have a lower priority than the
DAO. Or, dimension your objects like this:

Dim DB As DAO.Database
Dim GrpPages As DAO.Recordset


Steve


RCR wrote:
Thanks for your reply. I have checked box by Microsoft DAO 3.6 Object Library.

Now when I run report, I get 'Run-time error 13. Type Mismatch' Debug
highlights the following code - Set GrpPages = DB.OpenRecordset("Category
Group Pages", DB_OPEN_TABLE)

Any help would be much appreciated

RCR

:

In the VB Editor, go to Tools >> References and check the box by
Microsoft DAO 3.x Object Library.

Steve


RCR wrote:
Hi There

I am trying to reset the page number and page count for each group in my
database report. I downloaded Article ID 841779, and although I am not too
great at Visual Basic, I managed to use the example in the Northwind
database, which worked perfectly.
However, when I try and adapt the code to run on my database I get the
following error message 'The expression On Open you entered as event property
setting produced following error : User-defined type not defined'. The only
alterations I made to the code was replace [Country] with my group [ATA]. The
table that I created to store group names and pages is same as example. Also,
as I don't have much code in my database, I was unsure where to apend the
definition of variables for Database & Recordset (see copy of code)

Option Compare Database

Dim DB As Database
Dim GrpPages As Recordset
Function GetGrpPages()
'Find the group name.
GrpPages.Seek "=", Me![ATA]
If Not GrpPages.NoMatch Then
GetGrpPages = GrpPages![Page Number]
End If
End Function


Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
'Set page number to 1 when new group starts.
Page = 1
End Sub

Private Sub PageFooter3_Format(Cancel As Integer, FormatCount As Integer)
'Find the group.
GrpPages.Seek "=", Me![ATA]

If Not GrpPages.NoMatch Then
'The group is already there.
If GrpPages![Page Number] < Me.Page Then
GrpPages.Edit
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
Else
'This is the first page of the group. Therefore, add it.
GrpPages.AddNew
GrpPages![ATA] = Me![ATA]
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
End Sub

Private Sub Report_Open(Cancel As Integer)
Set DB = DBEngine.Workspaces(0).Databases(0)
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From [Category Group Pages];"
DoCmd.SetWarnings True
Set GrpPages = DB.OpenRecordset("Category Group Pages", DB_OPEN_TABLE)
GrpPages.Index = "PrimaryKey"

End Sub

If anyone could help in sorting this problem, I would be most grateful
Click to expand...
Click to expand...
Click to expand...
 
S

steveh

This section of your code is not running (at least not the setting of
GrpPages):

Private Sub Report_Open(Cancel As Integer)
Set DB = DBEngine.Workspaces(0).Databases(0)
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From [Category Group Pages];"
DoCmd.SetWarnings True
Set GrpPages = DB.OpenRecordset("Category Group Pages", DB_OPEN_TABLE)
GrpPages.Index = "PrimaryKey"
End Sub

It should run when the report is opened (previewed, printed). Do you
have any idea why this would not be running? Was something accidentally
changed here (for instance, it no longer is Report_Open)? Is this code
in the report object and not in a separate module?

Not too sure what you mean here. The code I am using is exactly the same as
used in Northwind example listed in my initial query, just the [country]
group is changed to my group [ATA]. My last reply was just to show changes
made to dimensioning of DB and GrpPages as per your recommendations. The
Private Sub Report open procecedure still contains the line

Set GrpPages = DB.OpenRecordset("Category Group Pages", DB_OPEN_TABLE)

My apologies if I have misunderstood your question.

steveh said:
So far so good. Did something happen to your Report_Open subroutine? The
GrpPages object should have been set there. Wasn't all the code you had
listed earlier taken from the report object?

Thanks for your reply.
List of references in following order -
Visual Basic For Applications
Microsoft Access 11.0 Object Library
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Objects 2.1 Library
OLE Automation

Dimensioning of DB & GrpPages as you recommended -

Dim DB As DAO.Database
Dim GrpPages As DAO.Recordset

:

Please list the references you have, in order of priority, and your code
for the dimensioning of DB and GrpPages. Are they the same as what you
have in the Northwind sample?


RCR wrote:
Thanks again for your reply. I have tried all of your suggestions, but now
get error message "Run-time error 91. Object Variable or with block variable
not set". If you select debug then following line is highlighted -
GrpPages.Seek "=",Me![ATA]

I would be grateful for any further help.


RCR




:

Clear any references to Microsoft ActiveX Data Objects (if you are not
using them), or at least make sure they have a lower priority than the
DAO. Or, dimension your objects like this:

Dim DB As DAO.Database
Dim GrpPages As DAO.Recordset


Steve


RCR wrote:
Thanks for your reply. I have checked box by Microsoft DAO 3.6 Object Library.

Now when I run report, I get 'Run-time error 13. Type Mismatch' Debug
highlights the following code - Set GrpPages = DB.OpenRecordset("Category
Group Pages", DB_OPEN_TABLE)

Any help would be much appreciated

RCR

:

In the VB Editor, go to Tools >> References and check the box by
Microsoft DAO 3.x Object Library.

Steve


RCR wrote:
Hi There

I am trying to reset the page number and page count for each group in my
database report. I downloaded Article ID 841779, and although I am not too
great at Visual Basic, I managed to use the example in the Northwind
database, which worked perfectly.
However, when I try and adapt the code to run on my database I get the
following error message 'The expression On Open you entered as event property
setting produced following error : User-defined type not defined'. The only
alterations I made to the code was replace [Country] with my group [ATA]. The
table that I created to store group names and pages is same as example. Also,
as I don't have much code in my database, I was unsure where to apend the
definition of variables for Database & Recordset (see copy of code)

Option Compare Database

Dim DB As Database
Dim GrpPages As Recordset
Function GetGrpPages()
'Find the group name.
GrpPages.Seek "=", Me![ATA]
If Not GrpPages.NoMatch Then
GetGrpPages = GrpPages![Page Number]
End If
End Function


Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
'Set page number to 1 when new group starts.
Page = 1
End Sub

Private Sub PageFooter3_Format(Cancel As Integer, FormatCount As Integer)
'Find the group.
GrpPages.Seek "=", Me![ATA]

If Not GrpPages.NoMatch Then
'The group is already there.
If GrpPages![Page Number] < Me.Page Then
GrpPages.Edit
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
Else
'This is the first page of the group. Therefore, add it.
GrpPages.AddNew
GrpPages![ATA] = Me![ATA]
GrpPages![Page Number] = Me.Page
GrpPages.Update
End If
End Sub

Private Sub Report_Open(Cancel As Integer)
Set DB = DBEngine.Workspaces(0).Databases(0)
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From [Category Group Pages];"
DoCmd.SetWarnings True
Set GrpPages = DB.OpenRecordset("Category Group Pages", DB_OPEN_TABLE)
GrpPages.Index = "PrimaryKey"

End Sub

If anyone could help in sorting this problem, I would be most grateful
Click to expand...
Click to expand...
Click to expand...
 

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

VBA Code For Page Numbering Not Working 0
Nothing showing for page of pages 8
Page Numbers for Report Groups 0
Reset Page Numbers by group 0
SECOND REQUEST - Reset Page Numbers by Group 1
Reset Page Numbering 4
Article 841779 - Resetting Page Numbers 1
Make label visible on pages <> 1 using James H Brooks code 1

Top