Form--Subform

[Guest]

I have a form that allows people to enter award amounts on a contract. The
contracts will be bid in several fields, so each award will have several
winning contractors.

I want to have a subform under each contractor's name showing the alternates
accepted and the name and price of each one.

I had hoped to write one subform, and make multiple copies, and have each
one filtered by the trade name. It now looks like I have to save multiple
copies of the subform, one for each trade, in order to do this.

Am I missing something? Can you figure out what I am talking about? Thanks.

 

[Allen Browne]

I think you are saying that each tender consists of several subtender items,
so when you receive the bids you might award part A to contractor Jim, and
part B to contractor Bill, and part C to ... Is that the idea?

If so, you will need at least these tables:

Contractor: one record for each contractor.
Tender: one record for each thing put out to tender.
TenderDetail: one record for each subtender item in a Tender.
Bid: one record for each Tender bid submitted by a Contractor.
BidDetail: one record for each TenderDetail item in a Bid.
Contract: one record for each successful BidDetail.

Not sure I've understood correctly, but does that make sense to you?

 

[Guest]

That's pretty much what I've got (not entirely, and I thank you for helping
me through some of the details of another piece of this process).

Right now, I am trying to build a form which will let a person enter one
award (which goes to several contractors). Under the entry for each
contractor, I want them to be able to look at (and enter) the sub-items
selected and the amounts for the awards selected for them. Those need to be
sorted by trade, so that only the subitems for the correct trade show up
under each contractor.

If I make a copy of my subform, and apply a filter to it, that filter gets
applied to every copy of the subform on the form.

Right now, I am solving this problem by making 7 copies of my subform,
giving them different names, and filtering each one appropriately. This
works. But it feels awfully clumsy, and it's hard for me to believe that
there isn't a better way.

Thanks in advance.

 

[Allen Browne]

The crucial thing is the data structure.

One Award has many AwardDetails. Normally that would be represented by a
main form bound to the Award table, with a subform bound to the AwardDetail
table. It seems like you have some kind of AwardTypeID field in the
AwardDetail table, and you want to filter your subforms by this field. If
there can never be anything by 7 AwardTypeID entries, then yes, you could
use 7 different subforms - all bound to the same AwardDetail table - showing
one type in each.

Not sure I would choose to do that: the biggest flaw in the design would be
that some day someone will think up another AwardTypeID entry, and then you
have to redesign the interface to cope with the new type.

If you go that way anyway, you need not use filters. Just include the
AwardTypeID in the LinkMasterFields/LinkChildFields, so each subform
automatically gets the correct records. Then in each subform, hide the
AwardTypeID, and set its DefaultValue so that the correct value is assigned
when new records are added to each subform.

Hope I've understood what you are talking about.

 

[Guest]

I think that you have a good handle on what I am doing.

You would not choose to do it the way I am doing it. How would you do it?
One of my possible AwardTypeID's is "other" with an extra field to explain
that--If I get two "Others" in one Award, I'm in trouble. Can't happen in
most of our jobs, but I concede that life is always changing. I'm not eager
to rearrange the data structure, but better to do it with only 100 records to
fix than when we get further entrenched.

Thank you so much for the help you are giving me today (especially since
you're helping me in another area as well).

 

[Allen Browne]

Your data structure sounds fine, and the "one-subform-for-each-type"
approach is sometimes the best idea. You will probably know if this will be
stable enough to cope with most things in the future, esp. if you have your
"other" category.

Another approach would be to use one continuous subform (not datasheet) with
an unbound combo in its Form Header section. User can select an AwardTypeID
in the combo, and its AfterUpdate event filters the subform to that
category, or removes the filter if the combo is null (nothing selected).
Advantages: Simple interface, Faster loading (only one subform). Works if
more AwardTypeIDs added.
Disadvantages: Can't see multiple tabs at once. Access bugs if you filter
the main form as well as the subform.

 

[Guest]

Many thanks. I'll probably go with what I've got this time, as it is helpful
to the user to see it all at once, and we are fairly small, so I can take the
slower speed if I don't do too much of this kind of thing. But I very much
appreciate the counsel.