sorting subform

[Guest]

My employer provides services to merchants in our region. Sometimes we
rent them any equipment needed, sometimes the merchant owns their own
equipment and we just provide support. I created a database to track the
equipment. I have one table that has the serial number as primary key, with
other fields being the type of equipment, who owns it, and where it is
located. I created a form with these fields present, and added a subform
that has fields for date and action taken so we can track when we drop
equipment off and when we pick it up.
When we add a new date and action to the subform, it rearranges the
records so they are no longer sorted by date. I have seen several posts
asking how to sort the subform, and noticed that all of them had replies
saying it was easier to use a query as the recordsource for the subform.
When I do that however, I have to include the serial number to link the
subform to the main form, and the serial number then shows in the subform for
every record. Is there a way to hide the serial number in the subform?
Also, even though I use the design view to arrange things a certain way, when
I enter form view the query subform displays in datasheet mode. How can I
change the design of the query subform?

 

[Al Camp]

Rich,
While using a table as the recordsource for a subform is perfectly
legit, I usually use a query, where I can change sorting, and perhaps add
calculated fields, etc... It doesn't have to be a "saved" query either...
it can just be resident within that particular form or report.

Is there a way to hide the serial number in the subform?

You can include the SerialNo on each subform record withgout showing it.
Your main form has a "window" that alows you so see the subform. If the
subform itself is 7' wide, and the subform window is 5", then you won't see
the 2" inches on the far right.
Just place the SerialNo field past the right edge of the subform window,
and set the TabStop = No, and Enabled = False, and Locked = Yes, and
ScrollBars = Vertical Only

I enter form view the query subform displays in datasheet mode.

In the properties for your subform Form, set the DefaultView to
Continuous... and ViewsAllowed to Form (or Both)

hth
Al Camp

 

[Guest]

You do not need to include the serial number field in the subform in order
for Access to link your two forms.

In the query for the subform, include the serial number field - but do not
include the serial number control in your subform. Remove it. Under your
main form, open the subform's properties and go to the 'Link Child' and 'Link
Master' Fields. For both selections you'll place the 'serial number' field.
This lets Access know to locate records in the subform that match the serial
number listed in the main form.

 

[Dirk Goldgar]

You do not need to include the serial number field in the subform in
order for Access to link your two forms.

In the query for the subform, include the serial number field - but
do not include the serial number control in your subform. Remove it.
Under your main form, open the subform's properties and go to the
'Link Child' and 'Link Master' Fields. For both selections you'll
place the 'serial number' field. This lets Access know to locate
records in the subform that match the serial number listed in the
main form.

This will generally work; however, I recommend including a control on
the subform bound to the serial number anyway. There are a few odd
quirks that sometimes occur if you don't. But you can make the bound
control invisible and give it a width of zero. so it doesn't show to the
user and doesn't get in the way while you are working on the subform in
design view.