Position subform based on recordset size

[Geezer via AccessMonster.com]

I have a main form that is unbound and on it are 15 subforms each based on
seperate queries. I currently have all of the subforms visibility set to
False if the recordset for the underlying query is zero. (These are
"maintenance" queries used to identify incomplete records - one query per
field looking for null values.) I can now print the single main form and see
the results of all 15 queries.

What I'd like to do is position the subforms on the main form according to
the recordset size of each; ultimately so those with a recordset of zero will
be positioned at the bottom of the main form so when it is printed I don't
have blank space at the top of the printed page and results appearing at the
bottom. Can this be done? Thanks

 

[Marshall Barton]

I have a main form that is unbound and on it are 15 subforms each based on
seperate queries. I currently have all of the subforms visibility set to
False if the recordset for the underlying query is zero. (These are
"maintenance" queries used to identify incomplete records - one query per
field looking for null values.) I can now print the single main form and see
the results of all 15 queries.

What I'd like to do is position the subforms on the main form according to
the recordset size of each; ultimately so those with a recordset of zero will
be positioned at the bottom of the main form so when it is printed I don't
have blank space at the top of the printed page and results appearing at the
bottom. Can this be done?

Although I've never heard of anyone needing that kind of
arrangement before, I think maybe it can be done.

Before I try to figure out how, please provide some details:

Are all the subform controls the same size?
Are all the subform controls using the same SourceObject?
What are the subform control's Link Master/Child Fields?
What is the difference between the 15 queries?

 

[Tom Ellison]

Dear Geez:

Just as a brain-storm kind of approach, what is the possibility of leaving
the subforms where they are and assigning the RecordSource of each form
according to the priority once you've established that. Can you make them
all the same size. We can adjust column headings and widths at run time.
Make sense as an alternative?

Tom Ellison

 

[Geezer via AccessMonster.com]

Although I've never heard of anyone needing that kind of

arrangement before, I think maybe it can be done.

Before I try to figure out how, please provide some details:

Are all the subform controls the same size?

They are the same width and set to Can Grow=Yes. The "height" will be
different between them. This is the reason I'd like to position them
according to recordset size. I don't want 14 blanks at the top of the page
and one with 10 records at the bottom causing the print job to go to two
pages for 10 lines of information.

Are all the subform controls using the same SourceObject?

I'm very new to Access and I'm not sure what you mean here. Each subform is
based on a unique querie. The subforms are datasheet view used to present
information only. No editing, adding, deleting, buttons etc are done on any
of the subs.

What are the subform control's Link Master/Child Fields?

There is no Master/Child relationship on any of the subforms. I put them on
a main form simply to allow clicking a single button, which brings up the
main form, and see the results of all 15 queries/subforms. (Originally I had
used an option group with 15 options, each displaying the results of a single
query. I thought it would be more simple to use subforms and display the
results of 15 queries on a single form.)

What is the difference between the 15 queries?

Conceptually the queries are the same, I.E. they display the results of
incomplete records. The database is used to track grants. Each grant has a
bunch of information associated with it; Project Number, Primary Investigator
(manager), Program, DeptID (account), Start Date, End Date, etc. Each query
looks for records with ONE of these fields blank. (No Project Number, no
Start Date, no Program, etc.) I couldn't figure out a way to make one query
simply look for any blank/null field in any of the dozen tables. I can't set
the fields to "Required" in the tables because I download information from
another application in the form of an Excel spreadsheet and then import the
spreadsheet into the database. (Sure beats manual entry even though there is
missing information sometimes. This missing information is what the 15
queries is intended to find. I can lookup the grants individually to
complete the records once I've downloaded and imported what is available.)

I appreciate your trying to help and hope I've provided the information you
need. Thanks

 

[Geezer via AccessMonster.com]

Dear Geez:

Just as a brain-storm kind of approach, what is the possibility of leaving
the subforms where they are and assigning the RecordSource of each form
according to the priority once you've established that. Can you make them
all the same size. We can adjust column headings and widths at run time.
Make sense as an alternative?

This sounds like a potential solution. The whole "changing RecordSource" is
foreign to me so I'd need some direction on how to do it. The subforms are
the same width now and are set to "Can Grow" so the size of the container
adjusts as the recordset size increases. Any info you need to further
explore this possibility?

Tom Ellison

I have a main form that is unbound and on it are 15 subforms each based on
seperate queries. I currently have all of the subforms visibility set to

[quoted text clipped - 11 lines]

the
bottom. Can this be done? Thanks

 

[Tom Ellison]

Dear Geez:

There are really only two things to know. You assign the RecordSource
property of a form or subform at runtine and it refreshes automatically to
that. This would likely be a query, allowing you to choose only the desired
columns and place them in the order you like.

The other thing is to assign the columns of data to the columns in the form
using the array of columns in the RecordSource rather than by name. At this
point you'll have great flexibility.

For column headings, and perhaps widths too, you could have a database of
these things, one table keyed by table name and another keyed by table and
column names. The table column could provide the SQL for the RecordSource.
The column names could give you the widths of each column needed and the
font size used. All this could then be prorated to size the controls and
fonts using the same kind of scheme used for screen sizing, but this time
within a fixed size subform instead of a whole screen. The whole thing is
perhaps 3 days work to set up if you're accustomed to this kind of work.
I'd really have to see very complete details to say for sure. But it could
all be written once and then be re-usable. Sounds like a very enjoyable
project to me, but I've done much of it before. Creating facilities that
work incredibly well in creating great user interfaces is usually the most
rewarding to me.

I haven't really used "can grow" like this. If a subform can grow, would it
possibly come to overlap another control? Or are you "growing" within the
limits established for the subform, to show only a few lines of data when
that's all that are available? That's pretty much done automatically
anyway, isn't it? Or are the controls within each row growing vertically so
the data can wrap? I would usually try to avoid that, but it could be a
useful feature given how much you want to put on the screen at once, so the
font doesn't decrease too drastically. Yes, that could be a nice addition
to what I was considering.

Well, you've got me thinking on my feet. I like that. And I can see that
this would be very useful and quite successful. All the work I've done
along this line before is considerable coding, but it runs very fast, with
no noticable delay bringing forms on the screen. So, for feasibility, I
give this a thumbs up.

Sorry, but Geezer is all I know to call you. It's your option whether you
give us a real name, but I can be flexible and change to that if you so
choose.

Tom Ellison

Dear Geez:

Just as a brain-storm kind of approach, what is the possibility of leaving
the subforms where they are and assigning the RecordSource of each form
according to the priority once you've established that. Can you make them
all the same size. We can adjust column headings and widths at run time.
Make sense as an alternative?

This sounds like a potential solution. The whole "changing RecordSource"
is
foreign to me so I'd need some direction on how to do it. The subforms
are
the same width now and are set to "Can Grow" so the size of the container
adjusts as the recordset size increases. Any info you need to further
explore this possibility?

Tom Ellison

I have a main form that is unbound and on it are 15 subforms each based
on
seperate queries. I currently have all of the subforms visibility set
to

[quoted text clipped - 11 lines]

the
bottom. Can this be done? Thanks

 

[Geezer via AccessMonster.com]

Dear Geez:

There are really only two things to know. You assign the RecordSource
property of a form or subform at runtine and it refreshes automatically to
that. This would likely be a query, allowing you to choose only the desired
columns and place them in the order you like.

The other thing is to assign the columns of data to the columns in the form
using the array of columns in the RecordSource rather than by name. At this
point you'll have great flexibility.

For column headings, and perhaps widths too, you could have a database of
these things, one table keyed by table name and another keyed by table and
column names. The table column could provide the SQL for the RecordSource.
The column names could give you the widths of each column needed and the
font size used. All this could then be prorated to size the controls and
fonts using the same kind of scheme used for screen sizing, but this time
within a fixed size subform instead of a whole screen. The whole thing is
perhaps 3 days work to set up if you're accustomed to this kind of work.
I'd really have to see very complete details to say for sure. But it could
all be written once and then be re-usable. Sounds like a very enjoyable
project to me, but I've done much of it before. Creating facilities that
work incredibly well in creating great user interfaces is usually the most
rewarding to me.

This all sounds very neat, but to be honest, I didn't understand much. I
don't mind putting in the time to make a flexible and dynamic scheme, but
like I said, I know what the words you use mean but I'm too new to Access to
understand the full sentences.

I haven't really used "can grow" like this. If a subform can grow, would it
possibly come to overlap another control? Or are you "growing" within the
limits established for the subform, to show only a few lines of data when
that's all that are available? That's pretty much done automatically
anyway, isn't it? Or are the controls within each row growing vertically so
the data can wrap? I would usually try to avoid that, but it could be a
useful feature given how much you want to put on the screen at once, so the
font doesn't decrease too drastically. Yes, that could be a nice addition
to what I was considering.

Using the Can Grow simply allows the subform to comform to the height needed
to display all rows of information when printed. (At least I think that's
correct. When looking at the main form with the 15 subs, I have all of the
subs sized very small and stacked one right under the other. I can't see all
of the rows in that view, but when I print it, all of the subforms expand to
the height needed to print all rows. It seems to be working that way in my
application.) The Can Grow does not seem to affect the width at all and
there is no wrapping within the rows. I do need to set my column widths in
design view.

Well, you've got me thinking on my feet. I like that. And I can see that
this would be very useful and quite successful. All the work I've done
along this line before is considerable coding, but it runs very fast, with
no noticable delay bringing forms on the screen. So, for feasibility, I
give this a thumbs up.

Sorry, but Geezer is all I know to call you. It's your option whether you
give us a real name, but I can be flexible and change to that if you so
choose.

Well Tom, my real name is also Tom. Since I like Black Sabbath and I'm
getting older now, Geezer, after Geezer Butler the BS bass player seemed a
good screen name Thanks again for any assistance you can provide.

Tom Ellison

Dear Geez:
[quoted text clipped - 21 lines]
the
bottom. Can this be done? Thanks

 

[Tom Ellison]

Dear Tom:

I don't know how to help you with much more than the concepts here. I've
done similar things to what you want, but not exactly what you ask My
approach has been to avoid the can gorw on forms, but I use it occasionally
on reports, just like what you explain.

I can see how my outline form doesn't give a close feel for what is
required. If it were more than an outline, it would be a small book. I
couldn't write that without first creating a working version of the
software. That not what you'll get from a newsgroup. If you want custom,
professional help to do this, you could email me. I use my real email
address in here.

If you do decide to attempt this yourself, come back to this newsgruop with
specific questions. You could let me know you have done so by email so I'd
try to have time to look at it. You might have success with that, but the
basis would be your own efforts and ability.

Tom Ellison

Dear Geez:

There are really only two things to know. You assign the RecordSource
property of a form or subform at runtine and it refreshes automatically to
that. This would likely be a query, allowing you to choose only the
desired
columns and place them in the order you like.

The other thing is to assign the columns of data to the columns in the
form
using the array of columns in the RecordSource rather than by name. At
this
point you'll have great flexibility.

For column headings, and perhaps widths too, you could have a database of
these things, one table keyed by table name and another keyed by table and
column names. The table column could provide the SQL for the
RecordSource.
The column names could give you the widths of each column needed and the
font size used. All this could then be prorated to size the controls and
fonts using the same kind of scheme used for screen sizing, but this time
within a fixed size subform instead of a whole screen. The whole thing is
perhaps 3 days work to set up if you're accustomed to this kind of work.
I'd really have to see very complete details to say for sure. But it
could
all be written once and then be re-usable. Sounds like a very enjoyable
project to me, but I've done much of it before. Creating facilities that
work incredibly well in creating great user interfaces is usually the most
rewarding to me.

This all sounds very neat, but to be honest, I didn't understand much. I
don't mind putting in the time to make a flexible and dynamic scheme, but
like I said, I know what the words you use mean but I'm too new to Access
to
understand the full sentences.

I haven't really used "can grow" like this. If a subform can grow, would
it
possibly come to overlap another control? Or are you "growing" within the
limits established for the subform, to show only a few lines of data when
that's all that are available? That's pretty much done automatically
anyway, isn't it? Or are the controls within each row growing vertically
so
the data can wrap? I would usually try to avoid that, but it could be a
useful feature given how much you want to put on the screen at once, so
the
font doesn't decrease too drastically. Yes, that could be a nice addition
to what I was considering.

Using the Can Grow simply allows the subform to comform to the height
needed
to display all rows of information when printed. (At least I think that's
correct. When looking at the main form with the 15 subs, I have all of
the
subs sized very small and stacked one right under the other. I can't see
all
of the rows in that view, but when I print it, all of the subforms expand
to
the height needed to print all rows. It seems to be working that way in
my
application.) The Can Grow does not seem to affect the width at all and
there is no wrapping within the rows. I do need to set my column widths
in
design view.

Well, you've got me thinking on my feet. I like that. And I can see that
this would be very useful and quite successful. All the work I've done
along this line before is considerable coding, but it runs very fast, with
no noticable delay bringing forms on the screen. So, for feasibility, I
give this a thumbs up.

Sorry, but Geezer is all I know to call you. It's your option whether you
give us a real name, but I can be flexible and change to that if you so
choose.

Well Tom, my real name is also Tom. Since I like Black Sabbath and I'm
getting older now, Geezer, after Geezer Butler the BS bass player seemed a
good screen name Thanks again for any assistance you can provide.

Tom Ellison

Dear Geez:

[quoted text clipped - 21 lines]

the
bottom. Can this be done? Thanks

 

[Geezer via AccessMonster.com]

Dear Tom:

I don't know how to help you with much more than the concepts here. I've
done similar things to what you want, but not exactly what you ask My
approach has been to avoid the can gorw on forms, but I use it occasionally
on reports, just like what you explain.

I can see how my outline form doesn't give a close feel for what is
required. If it were more than an outline, it would be a small book. I
couldn't write that without first creating a working version of the
software. That not what you'll get from a newsgroup. If you want custom,
professional help to do this, you could email me. I use my real email
address in here.

If you do decide to attempt this yourself, come back to this newsgruop with
specific questions. You could let me know you have done so by email so I'd
try to have time to look at it. You might have success with that, but the
basis would be your own efforts and ability.

Tom Ellison

Thanks for the starting point Tom. I'll see if I can move from concept to
reality. Take care.

 

[Marshall Barton]

They are the same width and set to Can Grow=Yes. The "height" will be
different between them. This is the reason I'd like to position them
according to recordset size. I don't want 14 blanks at the top of the page
and one with 10 records at the bottom causing the print job to go to two
pages for 10 lines of information.


I'm very new to Access and I'm not sure what you mean here. Each subform is
based on a unique querie. The subforms are datasheet view used to present
information only. No editing, adding, deleting, buttons etc are done on any
of the subs.


There is no Master/Child relationship on any of the subforms. I put them on
a main form simply to allow clicking a single button, which brings up the
main form, and see the results of all 15 queries/subforms. (Originally I had
used an option group with 15 options, each displaying the results of a single
query. I thought it would be more simple to use subforms and display the
results of 15 queries on a single form.)


Conceptually the queries are the same, I.E. they display the results of
incomplete records. The database is used to track grants. Each grant has a
bunch of information associated with it; Project Number, Primary Investigator
(manager), Program, DeptID (account), Start Date, End Date, etc. Each query
looks for records with ONE of these fields blank. (No Project Number, no
Start Date, no Program, etc.) I couldn't figure out a way to make one query
simply look for any blank/null field in any of the dozen tables. I can't set
the fields to "Required" in the tables because I download information from
another application in the form of an Excel spreadsheet and then import the
spreadsheet into the database. (Sure beats manual entry even though there is
missing information sometimes. This missing information is what the 15
queries is intended to find. I can lookup the grants individually to
complete the records once I've downloaded and imported what is available.)

Yes, that's sufficient information.

First, we need to name your subform controls in an orderly
way. Let's name them Child1, Child2, . . ., Child15 so that
we can work with them by number instead of arbitrary names.

Next set each subform control's Height property to .7 inches
and set their CanGrow and CanShrink properties to Yes.

Move them up toward the top of the form's section and reduce
the section's height to near the bottom of the lowest
control.

With that done, you can use the code below in the main
form's Load event procedure to get the arrangement you're
looking for.

Could all this have been avoided if you had set each subform
control's CanShrink property to Yes?

If you were a little more advanced, I would have told you to
scrap the idea of printing the form and use a report
instead. But, since you have gotten so far down this road
and, at this point, you don't want to get into learning
another, different set of design issues, this will get you
going. However, in the future, you really need to get on
board with Access's extensive set of reporting features so
you could do all this with a single report based on a single
query.

Private Sub Form_Load()
Dim arySubs(1 To 15, 1 To 2) As Integer
Dim intSub As Integer
Dim intCnt As Integer
Dim i As Integer
Dim k As Integer

For i = 1 To 15
With Me("Child" & i).Form.RecordsetClone
.MoveLast
arySubs(i, 1) = .RecordCount
arySubs(i, 2) = i
End With
Next i

For i = 1 To 15
intCnt = arySubs(i, 1)
intSub = arySubs(i, 2)
For k = i + 1 To 15
If intCnt < arySubs(k, 1) Then
arySubs(i, 1) = arySubs(k, 1)
arySubs(i, 2) = arySubs(k, 2)
arySubs(k, 1) = intCnt
arySubs(k, 2) = intSub
intCnt = arySubs(i, 1)
intSub = arySubs(i, 2)
End If
Next k
Me("Child" & arySubs(i, 2)).Top = (i - 1) * 0.7 * 1440
Next i
End Sub

 

[Geezer via AccessMonster.com]

Thanks for the information, I'll test it when I get home from work.

Could all this have been avoided if you had set each subform
control's CanShrink property to Yes?

No, I tried setting CanShrink to Yes and nothing changes as far as what I'm
trying to achieve.

If you were a little more advanced, I would have told you to
scrap the idea of printing the form and use a report
instead. But, since you have gotten so far down this road
and, at this point, you don't want to get into learning
another, different set of design issues, this will get you
going. However, in the future, you really need to get on
board with Access's extensive set of reporting features so
you could do all this with a single report based on a single
query.

I'd thought of using a report, but wouldn't I end up with the same delema of
15 subreports? I have no problem scrapping the current form setup and
starting something new if it will be better; that's how I'm learning Access.
I've worked with reports a little and have a couple with subreports, if
that's necessary. If you're still interested, I'd very much like to hear the
single report/single query method, but I truely appreciate your help to this
point even if you prefer not to expand on this idea. Thanks!

 

[Marshall Barton]

Thanks for the information, I'll test it when I get home from work.


No, I tried setting CanShrink to Yes and nothing changes as far as what I'm
trying to achieve.


I'd thought of using a report, but wouldn't I end up with the same delema of
15 subreports? I have no problem scrapping the current form setup and
starting something new if it will be better; that's how I'm learning Access.
I've worked with reports a little and have a couple with subreports, if
that's necessary. If you're still interested, I'd very much like to hear the
single report/single query method, but I truely appreciate your help to this
point even if you prefer not to expand on this idea. Thanks!

The fundamental concept of any report is to create a query
that selects the needed data and only the needed data.
After that, you can use the report featues to format the
data in a useful way.

I think you said you want the report to include all records
that have a Null in at least one of 15 fields. This can be
done easily (if somewhat tediously) by selecting all(?) the
fields in the table and dragging them to the query's fields
row. Then type Is Null in the Criteria row under the first
of the 15 fields. For the next field, type the same thing
on the next row under the Criteria row. Keep doing that for
each field but make sure that the Is Null is on a separate
criteria row.

The key point here is that conditions (the Is Null) on the
same row must **all** be true, BUT **any** one on different
rows must be true. In logic, the Criteria in a row are
ANDed together, then the separate rows are ORed together.

Once you have the query selecting all the records of
interest, then create a simple report based on the query and
away you go ;-)

There's probably more to it than that, but give it a try and
post back with any additional requirements.

 

[Geezer via AccessMonster.com]

The key point here is that conditions (the Is Null) on the
same row must **all** be true, BUT **any** one on different
rows must be true. In logic, the Criteria in a row are
ANDed together, then the separate rows are ORed together.

Once you have the query selecting all the records of
interest, then create a simple report based on the query and
away you go ;-)

There's probably more to it than that, but give it a try and
post back with any additional requirements.

Pretty slick! I think I can make this work. As I was creating the query I
discovered (remembered in a couple of cases I need more criteria than
simply Is Null. I'm playing around and it appears to be coming around nicely.
Thanks for this idea and explanation.

I tried the code you offered previously also, for a learning experience. It
bombs-out on the .MoveLast in:

For i=1 To 15
With Me("Child" & i).Form.RecordsetClone
.MoveLast
arySubs(i, 1) = .RecordCount
arySubs(i, 2) = i

The error message states No Current Record. Is this because some of the subs
have a zero recordset?

Thanks for all your time and effort.

 

[Marshall Barton]

Pretty slick! I think I can make this work. As I was creating the query I
discovered (remembered in a couple of cases I need more criteria than
simply Is Null. I'm playing around and it appears to be coming around nicely.
Thanks for this idea and explanation.

I tried the code you offered previously also, for a learning experience. It
bombs-out on the .MoveLast in:

For i=1 To 15
With Me("Child" & i).Form.RecordsetClone
.MoveLast
arySubs(i, 1) = .RecordCount
arySubs(i, 2) = i

The error message states No Current Record. Is this because some of the subs
have a zero recordset?

Arrgghhh, I forgot that some of the subforms won't have any
data. Sorry about that.

Anyway, it's easy to fix:

For i=1 To 15
With Me("Child" & i).Form.RecordsetClone
If .RecordCount > 0 Then .MoveLast
arySubs(i, 1) = .RecordCount
arySubs(i, 2) = i

Let us know when you get to the next issue with the report
;-)

 

[Geezer via AccessMonster.com]

Arrgghhh, I forgot that some of the subforms won't have any

data. Sorry about that.

Anyway, it's easy to fix:

For i=1 To 15
With Me("Child" & i).Form.RecordsetClone
If .RecordCount > 0 Then .MoveLast
arySubs(i, 1) = .RecordCount
arySubs(i, 2) = i

Let us know when you get to the next issue with the report
;-)

I made the code adujstment and now get the error The Control or Subform
Control is Too Large For This Location at:
Me("Child" & arySubs(i, 2)).Top = (i - 1) * 0.7 * 1440

I imagine the 1440 is a width setting for the subforms. I also presume this
is a problem because I have the subs arranged in a two column arrangement,
each column the width of one-half a page width. I'm sure this has already
gone on longer than you care to continue with and I believe the report
solution you proposed is going to work so I'll pursue it further. I
appreciate your offer to help with report issues, but what's with the WHEN I
get to the next issue? (_8-(l) doh! Thanks again!

 

[Marshall Barton]

I made the code adujstment and now get the error The Control or Subform
Control is Too Large For This Location at:
Me("Child" & arySubs(i, 2)).Top = (i - 1) * 0.7 * 1440

I imagine the 1440 is a width setting for the subforms. I also presume this
is a problem because I have the subs arranged in a two column arrangement,
each column the width of one-half a page width. I'm sure this has already
gone on longer than you care to continue with and I believe the report
solution you proposed is going to work so I'll pursue it further. I
appreciate your offer to help with report issues, but what's with the WHEN I
get to the next issue? (_8-(l) doh! Thanks again!

Two columns? You never said anything about two columns ;-)

Seriously, My suggested approach will only work with one
column. The error occured because the code is stacking the
subforms, one above the other and the detail section is not
tall enough to hold them all. If the data accomodates it,
two columns is a good idea, but that's another good reason
to use a report.

The 1440 is the number of TWIPS per inch. TWOPS is the
internal Access unit of measure for size and position
values. The design UI alows you to specify inches or
centimeters, but those are converted to TWIPS in the real
property values.

There's always a next issue, even when a program works
perfectly and the users love it. They love it so much they
want it to walk the dog too ;-)

 

[Geezer via AccessMonster.com]

Two columns? You never said anything about two columns ;-)

Seriously, My suggested approach will only work with one
column. The error occured because the code is stacking the
subforms, one above the other and the detail section is not
tall enough to hold them all. If the data accomodates it,
two columns is a good idea, but that's another good reason
to use a report.

The 1440 is the number of TWIPS per inch. TWOPS is the
internal Access unit of measure for size and position
values. The design UI alows you to specify inches or
centimeters, but those are converted to TWIPS in the real
property values.

There's always a next issue, even when a program works
perfectly and the users love it. They love it so much they
want it to walk the dog too ;-)

I hear that! I've rearranged the subforms to just one column and it works
like a charm. Thanks so much. Gotta go, I need to walk the dog now