How to keep subtotal together with detail

[Victoria]

Hello,

I created a report and a subreport. The issue is that my subtotal
which totals the main detail section in the report footer carries over
to the next page if subreport does not fit on the first page. Is
there a way to set it so that the subtotal stays on the same page as
the last item in the detail section?

Thanks,

Victoria

 

[Allen Browne]

Several possibilities, none of them ideal.

If you select the group header in the Sorting and Grouping box, the last
property is to keep the entire group together. This works, forcing the
entire section onto the next page if it can't fit the group header, all
detail records and the group footer on the page. However, this tends to
really slow down the formatting of the report, especially if the sections
Can Grow and CanShrink.

Another approach is to use a subreport in a section that has its Keep
Together property set to Yes.

Another alternative is to add a Page Break control to the top of your Detail
section. Set its Visible property to No. In the Format event of the Detail
section, examine the report's Top property. If this is the last record of
the group, and you are too far down the page to fit both this record and the
group footer, set the Visible property to Yes. This is impractical if you
have Can Grow or Can Shrink happening, and it can be messy to determine if
this is the last record for the group, because Access does not maintain the
report's FilterOn property correctly so you can't tell if the Filter is real
or an artifact.

If the group footer contains lots of white space (to space the group apart
from the next group), the chance of an orphaned record is higher. You can
reduce than chance by creating another group footer on the same field (i.e.
another row in the Sorting and Grouping box, based on the same row) and put
the white space into the 2nd group footer so the first one is more likely to
fit.

 

[Victoria]

Several possibilities, none of them ideal.

If you select the group header in the Sorting and Grouping box, the last
property is to keep the entire group together. This works, forcing the
entire section onto the next page if it can't fit the group header, all
detail records and the group footer on the page. However, this tends to
really slow down the formatting of the report, especially if the sections
Can Grow and CanShrink.

Another approach is to use a subreport in a section that has its Keep
Together property set to Yes.

Another alternative is to add a Page Break control to the top of your Detail
section. Set its Visible property to No. In the Format event of the Detail
section, examine the report's Top property. If this is the last record of
the group, and you are too far down the page to fit both this record and the
group footer, set the Visible property to Yes. This is impractical if you
have Can Grow or Can Shrink happening, and it can be messy to determine if
this is the last record for the group, because Access does not maintain the
report's FilterOn property correctly so you can't tell if the Filter is real
or an artifact.

If the group footer contains lots of white space (to space the group apart
from the next group), the chance of an orphaned record is higher. You can
reduce than chance by creating another group footer on the same field (i.e.
another row in the Sorting and Grouping box, based on the same row) and put
the white space into the 2nd group footer so the first one is more likely to
fit.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.







- Show quoted text -

Hi Allen,
Thank you for your reply. Unfotunetly I haven't had any luck. I do
have another question for you if I may. I stumbled on your site last
week and downloaded your Search2000 database. I didn't use any of the
vba code as I already created a simple search form to find products.
My questions is that I set my form to be continues and just like in
your form have header section with filters and my records are in the
detail section. However, unlike your form, when I choose to run a set
of criteria, I am taken to a datasheet view and all my fonts and
backgrounds settings get losts and the columns can be altered. Do you
have something in your code that keeps the search in the form view.
Could you help me?

Thank you,
Victoria

 

[Allen Browne]

Sorry: I have no idea what code is behind your button, so I cannot comment
on why it switches to Datasheet view.

A form would remain in its current view unless you explicitly changed it.

 

[Victoria]

Sorry: I have no idea what code is behind your button, so I cannot comment
on why it switches to Datasheet view.

A form would remain in its current view unless you explicitly changed it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

[snip] I do
have another question for you if I may. I stumbled on your site last
week and downloaded your Search2000 database. I didn't use any of the
vba code as I already created a simple search form to find products.
My questions is that I set my form to be continues and just like in
your form have header section with filters and my records are in the
detail section. However, unlike your form, when I choose to run a set
of criteria, I am taken to a datasheet view and all my fonts and
backgrounds settings get losts and the columns can be altered. Do you
have something in your code that keeps the search in the form view.
Could you help me?

Thank you,
Victoria- Hide quoted text -

- Show quoted text -

Allen,

This is the code i have on click even of the Run Command in my search
form.

Private Sub RUN_Click()
DoCmd.OpenQuery "ProductSearchQuery", acViewNormal

End Sub

Victoria

 

[Allen Browne]

Well, that code will open a query alright.
Assuming it is a SELECT query, it will open in datasheet view.

It won't change the view of the form that contains your RUN button though.

 

[Victoria]

Well, that code will open a query alright.
Assuming it is a SELECT query, it will open in datasheet view.

It won't change the view of the form that contains your RUN button though.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.










- Show quoted text -

Allen,

Is there a way I can have my results show up in the form rather than a
datasheet window, just like in your search database?

Victoria

 

[Allen Browne]

You might try using code as in the sample database.

 

[Victoria]

You might try using code as in the sample database.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.








- Show quoted text -

Allen,
Pleaseeee help me on this, I have to send out the final database to
the end users tonight and I need to include a search form to look up
the products in the pricelist.

My form search is based on a query which has five fields that i'm
quering on with the following in the criteria of each
Like "*" & [forms]![productsearchform].[filtername] & "*"

In the form's search comman on click I have the following:

Option Compare Database
Option Explicit


Private Sub cmdFilter_Click()
Dim strWhere As String

If Not IsNull(Me.QDivision) Then
strWhere = strWhere & "([divison] Like ""*" & Me.QDivision &
"*"") AND "
End If
If Not IsNull(Me.QBrand) Then
strWhere = strWhere & "([IREF04] Like ""*" & Me.QBrand & "*"")
AND "
End If
If Not IsNull(Me.QItemDesc) Then
strWhere = strWhere & "([ProductName] Like ""*" & Me.QItemDesc
& "*"") AND "
End If
If Not IsNull(Me.QPC) Then
strWhere = strWhere & "([Pc] Like ""*" & Me.QPC & "*"") AND "
End If
If Not IsNull(Me.QPCDesc) Then
strWhere = strWhere & "([pcdesc] Like ""*" & Me.QPCDesc &
"*"") AND "
End If
Me.Filter = strWhere
Me.FilterOn = True

End Sub

I get a compile error 2448 on Me.filter = strWhere and then
me.filterOn = true.

Thank you

 

[Allen Browne]

Remove the criteria from the query.
You don't need that as well as the filter in code (as far as I can see.)

In your code, you omitted the code that chops off the trailing 5 characters,
i.e. the " AND ".

Is QDivision the name of the unbound control? It's the unbound controls in
the Form Header section you use to create the filter string.

Just above the line that assigns the filter, add the line:
Debug.Print strWhere
When if fails press Ctrl+G to open the Immediate window. Examine what's
there to see if you can identify the issue.

I'm about to go out for the day (teaching a class, and installing a new
database for a client), but I do hope that gives you the ability to debug
this and get it going. It sounds like you are so close now.

 

[Victoria]

You might try using code as in the sample database.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.








- Show quoted text -

Allen,
Pleaseeee help me on this, I have to send out the final database to
the end users tonight and I need to include a search form to look up
the products in the pricelist.

My form search is based on a query which has five fields that i'm
quering on with the following in the criteria of each
Like "*" & [forms]![productsearchform].[filtername] & "*"

In the form's search comman on click I have the following:

Option Compare Database
Option Explicit


Private Sub cmdFilter_Click()
Dim strWhere As String

If Not IsNull(Me.QDivision) Then
strWhere = strWhere & "([divison] Like ""*" & Me.QDivision &
"*"") AND "
End If
If Not IsNull(Me.QBrand) Then
strWhere = strWhere & "([IREF04] Like ""*" & Me.QBrand & "*"")
AND "
End If
If Not IsNull(Me.QItemDesc) Then
strWhere = strWhere & "([ProductName] Like ""*" & Me.QItemDesc
& "*"") AND "
End If
If Not IsNull(Me.QPC) Then
strWhere = strWhere & "([Pc] Like ""*" & Me.QPC & "*"") AND "
End If
If Not IsNull(Me.QPCDesc) Then
strWhere = strWhere & "([pcdesc] Like ""*" & Me.QPCDesc &
"*"") AND "
End If
Me.Filter = strWhere
Me.FilterOn = True

End Sub

I get a compile error 2448 on Me.filter = strWhere and then
me.filterOn = true.

Thank you