Mulitple Me!'s

[Roland]

I have the following Event Procedures in a report that prints invoices for 14
households, up until now only Household1 has required their departments
split. Household7 has now asked for their invoices to be split by department.
Thanks to help elsewhere (see Nesting IIF's), I think I have the report side
sorted, but need to update the following procedures as well

**Procedure1**
Private Sub DeptFooter_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo Error_DeptFooter_Format
If Me![txtHHNo] = 1 Then
Reports![rptInvoice].DeptFooter.ForceNewPage = 2
Reports![rptInvoice].DeptFooter.Visible = True
Else
Reports![rptInvoice].DeptFooter.ForceNewPage = 0
Reports![rptInvoice].DeptFooter.Visible = False
End If
Exit_DeptFooter_Format:
Exit Sub
Error_DeptFooter_Format:
MsgBox Error(Err)
Resume Exit_DeptFooter_Format
End Sub

**Procedure2**
Private Sub DeptHeader_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo Error_DeptHeader_Format
If Me![txtHHNo] = 1 Then
Reports![rptInvoice].DeptHeader.Visible = True
Else
Reports![rptInvoice].DeptHeader.Visible = False
End If
Exit_DeptHeader_Format:
Exit Sub
Error_DeptHeader_Format:
MsgBox Error(Err)
Resume Exit_DeptHeader_Format
End Sub

I know I need to adjust the lines
If Me![txtHHNo] = 1 Then
to show 1 & 7, but how?
NB I am not the originator of the db, but am now the only one who has
limited Access knowledge and this is a bit beyond what I know/can work out

 

[Paolo]

Hi Roland,
it's enough to change
If Me![txtHHNo] = 1 Then with
If (Me![txtHHNo] = 1 or If Me![txtHHNo] = 7) Then
in both procedures

HTH Paolo

 

[John Spencer]

You could modify the code as follows

If Me![txtHHNo] = 1 OR Me![txtHHNo] = 7 Then

A better idea would be to have a field in your table that identifies which
households should split the invoices by department. Then you can use the the
fields value to split or not split the invoices by department. And when
Household 8 decides it wants to split the invoices, all you need to do is to
set the SplitByDepartment field to True.

Your test line becomes something like

If Me.SplitByDepartment = True Then
...
ELSE
...
End if


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Roland]

Thanks Paolo and John, the OR works of course, but John, your suggestion
about adding the extra field in the main table looks interesting and have
tried to add it and failed (wish I knew more about Access).
The related setup for the report/invoices from what I can work out relies on
3 tables - [tblVehicles], [tblHouseholdDetails] and [tblDepartment]. I added
a Yes/No field to [tblHouseholdDetails] called SplitDepartment, and then
added this new field to the [qryVehiclesToInvoice] which the [rptInvoice]
gets most of its data from.
I tried as you suggested to add “If Me.SplitDepartment = True Then†and all
I got was “Compile error: Method or data member not foundâ€.
I then tried “If Me!SplitDepartment = True Then†which gave me an error
message saying it couldn’t find the field SplitDepartment which would
indicate I need to put it somewhere else – but where?

Roland

You could modify the code as follows

If Me![txtHHNo] = 1 OR Me![txtHHNo] = 7 Then

A better idea would be to have a field in your table that identifies which
households should split the invoices by department. Then you can use the the
fields value to split or not split the invoices by department. And when
Household 8 decides it wants to split the invoices, all you need to do is to
set the SplitByDepartment field to True.

Your test line becomes something like

If Me.SplitByDepartment = True Then
...
ELSE
...
End if


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have the following Event Procedures in a report that prints invoices for 14
households, up until now only Household1 has required their departments
split. Household7 has now asked for their invoices to be split by department.
Thanks to help elsewhere (see Nesting IIF's), I think I have the report side
sorted, but need to update the following procedures as well

**Procedure1**
Private Sub DeptFooter_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo Error_DeptFooter_Format
If Me![txtHHNo] = 1 Then
Reports![rptInvoice].DeptFooter.ForceNewPage = 2
Reports![rptInvoice].DeptFooter.Visible = True
Else
Reports![rptInvoice].DeptFooter.ForceNewPage = 0
Reports![rptInvoice].DeptFooter.Visible = False
End If
Exit_DeptFooter_Format:
Exit Sub
Error_DeptFooter_Format:
MsgBox Error(Err)
Resume Exit_DeptFooter_Format
End Sub

**Procedure2**
Private Sub DeptHeader_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo Error_DeptHeader_Format
If Me![txtHHNo] = 1 Then
Reports![rptInvoice].DeptHeader.Visible = True
Else
Reports![rptInvoice].DeptHeader.Visible = False
End If
Exit_DeptHeader_Format:
Exit Sub
Error_DeptHeader_Format:
MsgBox Error(Err)
Resume Exit_DeptHeader_Format
End Sub

I know I need to adjust the lines
If Me![txtHHNo] = 1 Then
to show 1 & 7, but how?
NB I am not the originator of the db, but am now the only one who has
limited Access knowledge and this is a bit beyond what I know/can work out

 

[John Spencer]

Did you add the field to the report? You can set the control's visible
property to false (you don't need to show it), but it must be used in the report.

If you don't add the field (a control bound to the field to be technically
accurate), then the report does not know the field exists. I believe if you
use the field in the Sorting and Grouping dialog that also counts.



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thanks Paolo and John, the OR works of course, but John, your suggestion
about adding the extra field in the main table looks interesting and have
tried to add it and failed (wish I knew more about Access).
The related setup for the report/invoices from what I can work out relies on
3 tables - [tblVehicles], [tblHouseholdDetails] and [tblDepartment]. I added
a Yes/No field to [tblHouseholdDetails] called SplitDepartment, and then
added this new field to the [qryVehiclesToInvoice] which the [rptInvoice]
gets most of its data from.
I tried as you suggested to add “If Me.SplitDepartment = True Then†and all
I got was “Compile error: Method or data member not foundâ€.
I then tried “If Me!SplitDepartment = True Then†which gave me an error
message saying it couldn’t find the field SplitDepartment which would
indicate I need to put it somewhere else – but where?

Roland

You could modify the code as follows

If Me![txtHHNo] = 1 OR Me![txtHHNo] = 7 Then

A better idea would be to have a field in your table that identifies which
households should split the invoices by department. Then you can use the the
fields value to split or not split the invoices by department. And when
Household 8 decides it wants to split the invoices, all you need to do is to
set the SplitByDepartment field to True.

Your test line becomes something like

If Me.SplitByDepartment = True Then
...
ELSE
...
End if


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have the following Event Procedures in a report that prints invoices for 14
households, up until now only Household1 has required their departments
split. Household7 has now asked for their invoices to be split by department.
Thanks to help elsewhere (see Nesting IIF's), I think I have the report side
sorted, but need to update the following procedures as well

**Procedure1**
Private Sub DeptFooter_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo Error_DeptFooter_Format
If Me![txtHHNo] = 1 Then
Reports![rptInvoice].DeptFooter.ForceNewPage = 2
Reports![rptInvoice].DeptFooter.Visible = True
Else
Reports![rptInvoice].DeptFooter.ForceNewPage = 0
Reports![rptInvoice].DeptFooter.Visible = False
End If
Exit_DeptFooter_Format:
Exit Sub
Error_DeptFooter_Format:
MsgBox Error(Err)
Resume Exit_DeptFooter_Format
End Sub

**Procedure2**
Private Sub DeptHeader_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo Error_DeptHeader_Format
If Me![txtHHNo] = 1 Then
Reports![rptInvoice].DeptHeader.Visible = True
Else
Reports![rptInvoice].DeptHeader.Visible = False
End If
Exit_DeptHeader_Format:
Exit Sub
Error_DeptHeader_Format:
MsgBox Error(Err)
Resume Exit_DeptHeader_Format
End Sub

I know I need to adjust the lines
If Me![txtHHNo] = 1 Then
to show 1 & 7, but how?
NB I am not the originator of the db, but am now the only one who has
limited Access knowledge and this is a bit beyond what I know/can work out

 

[Roland]

DOH! done and works as you suggested, also worked out how to adjust the other
IIF's on the report to simplify them.
eg
=IIf([SplitDepartment]-1,"Department: " & [Department],"")

many thanks for your help

Did you add the field to the report? You can set the control's visible
property to false (you don't need to show it), but it must be used in the report.

If you don't add the field (a control bound to the field to be technically
accurate), then the report does not know the field exists. I believe if you
use the field in the Sorting and Grouping dialog that also counts.



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Thanks Paolo and John, the OR works of course, but John, your suggestion
about adding the extra field in the main table looks interesting and have
tried to add it and failed (wish I knew more about Access).
The related setup for the report/invoices from what I can work out relies on
3 tables - [tblVehicles], [tblHouseholdDetails] and [tblDepartment]. I added
a Yes/No field to [tblHouseholdDetails] called SplitDepartment, and then
added this new field to the [qryVehiclesToInvoice] which the [rptInvoice]
gets most of its data from.
I tried as you suggested to add “If Me.SplitDepartment = True Then†and all
I got was “Compile error: Method or data member not foundâ€.
I then tried “If Me!SplitDepartment = True Then†which gave me an error
message saying it couldn’t find the field SplitDepartment which would
indicate I need to put it somewhere else – but where?

Roland

You could modify the code as follows

If Me![txtHHNo] = 1 OR Me![txtHHNo] = 7 Then

A better idea would be to have a field in your table that identifies which
households should split the invoices by department. Then you can use the the
fields value to split or not split the invoices by department. And when
Household 8 decides it wants to split the invoices, all you need to do is to
set the SplitByDepartment field to True.

Your test line becomes something like

If Me.SplitByDepartment = True Then
...
ELSE
...
End if


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Roland wrote:
I have the following Event Procedures in a report that prints invoices for 14
households, up until now only Household1 has required their departments
split. Household7 has now asked for their invoices to be split by department.
Thanks to help elsewhere (see Nesting IIF's), I think I have the report side
sorted, but need to update the following procedures as well

**Procedure1**
Private Sub DeptFooter_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo Error_DeptFooter_Format
If Me![txtHHNo] = 1 Then
Reports![rptInvoice].DeptFooter.ForceNewPage = 2
Reports![rptInvoice].DeptFooter.Visible = True
Else
Reports![rptInvoice].DeptFooter.ForceNewPage = 0
Reports![rptInvoice].DeptFooter.Visible = False
End If
Exit_DeptFooter_Format:
Exit Sub
Error_DeptFooter_Format:
MsgBox Error(Err)
Resume Exit_DeptFooter_Format
End Sub

**Procedure2**
Private Sub DeptHeader_Format(Cancel As Integer, FormatCount As Integer)
On Error GoTo Error_DeptHeader_Format
If Me![txtHHNo] = 1 Then
Reports![rptInvoice].DeptHeader.Visible = True
Else
Reports![rptInvoice].DeptHeader.Visible = False
End If
Exit_DeptHeader_Format:
Exit Sub
Error_DeptHeader_Format:
MsgBox Error(Err)
Resume Exit_DeptHeader_Format
End Sub

I know I need to adjust the lines
If Me![txtHHNo] = 1 Then
to show 1 & 7, but how?
NB I am not the originator of the db, but am now the only one who has
limited Access knowledge and this is a bit beyond what I know/can work out