OK, I don't know if you resolved this yet, but I have a couple of solutions
you might consider.
1. If you're major concern is the engineer's scrolling, it is easiest to
provide a simple sorting tool so the engineer's default subform view is
descending. I would try setting the default sort order for the engineers to
descending and provide nav buttons at least for ascend descend sort order...
These options are built into Access, but I don't typicall allow the default
tool bar or include record selectors and navigation buttons. That's just me,
that doesn't make those bad options for your solutions.
2. A bit more complex solution is to add a field to your main table and add
a text box to your main form. Then Populate the new text box with data
queried from the subform recordset's Highest Index value for the current
record in the main form.
If you find that a bit complex sounding, I really had a hard time comming up
with this several years ago, but when I got it, I was pretty stoked cause it
is pretty simple. The first way that I found to do this was to add the
textbox on the main form, right above the subform, and formatting it so to
look like part of the subform. Then I used a make table query triggered by
the form's Open Event or Load Event that grouped the top level unique key and
the Max of ID for the continuous form's dataset. This worked to retrieve the
data for one record for each main record 1-1. A second simple query appended
that record to the main forms table, but I had data now in two places (once
on the main form and once in the continuous subform in the last record... So
I added a filter to the subform so all records in the subform showed but that
one after the initializing macro ran the action queries.
Eventually I added a table to serve this end. That table was itentical t the
subform (but with no data). I added that table to the main form's underlying
query linking it to the main form's base table with a left outer join, so
that it wouldn't cause the main form to be filtered by it. (I couldn't figure
out why there was some missing records in the main form.) When I resolved an
issue in my issue tracking app, I clicked a button "Resolve Issue." This
populated the table by using Command Button Wizard generated
"docmd.OpenQuery" code. I use this often now for wierd situations because,
although it is down and dirty, it works to achieve a particular result
(sorting your subform ascending while pulling the last record up to the top.)
My case was very simmilar in nature to your's because I had multiple tasks
that I (and others) entered each day in the course of resolving issues. Each
entry required a status type (i.e Unassigned; Assigned; In-Proggress;
Resolved) My boss was only interested in reading the 'Resolved Status.' No
matter how much I documented, I still had to explain every step of the
issue's history, because she wouldn't scroll through the history, just read
the Resolution. It became important that I inclued more and more infromation
there because I didn't like explaining everything, over and over, especially
after typing it all in for her use...
That solution became the foundation for how I summarize data from sub-forms
to the main form. I worked with zip codes 'Client Census Zips and HMO
coverage area Zips.' It became an obsession to pull something meaningfull out
of all that meaningless data in a nice little summary on the main form. So I
would include things like Total Records for HMO A, Total Network HMOs for
this Client, Count of Null Value in Zip Code Field, Count of Invalidated Zips
by the USPS file filter... statistical stuff like that. You can use the
method described above to support this type of summary aggrigation on your
main form by Grouping and Counting the records in your subforms. Like count
how many tests(records) or how many re-tests or failed tests or whatever
groupings and countings seem potentially meaningful to engineers. Once you've
perfected the system of getting the data into the main form, you're going to
want to make it as meaningful as possible for your client users (engineers, I
guess); they love that sort of thing.
If you're interested in sample solutions, I have one that I just thru
together for this response... I also have the issues system that I built five
years ago which demonstrates this method in different application. I also
have prebuilt application solutions (Forms, Queries, Code, Reports(are my
week point) for just about any access application you will ever build, so
long as I have a couple day's lead time...
I just started building my consulting practice after working at the same job
for six years. For now I am relearning many dormant Access skills and getting
a clue about all the .Net stuff, I have some time to donate to these
newsgroups to that end.
Feel free to send questions or request Access sample solutions from:
EnterAccessTools @ cox.net (no spaces no spam please). All is free for
applications development.
I hope this information is useful, take care.
Rev.
