How can one subform be controlled by several combos?

  • Thread starter Thread starter Frank Martin
  • Start date Start date
F

Frank Martin

I have redesigned an old invoice form to make the address selection more
efficient.

There are four classes of addresses, Account, Delivery, Contractor and
Consultant.

All these addresses come from one query, and so a selection method is
required.

My idea is to have one address subform on the invoice screen with a bank of
combo boxes beneath it, each combo used for one class of address.

The subform data properties have the Master/Child fields that work,
unfortunately, for one combo only.

How can I have just one address subform controlled by several combos, each
used for a different class of address?

Please help, Frank
 
Hi Frank,

I could think of a few ways that you could handle this.

You could create separate subforms for each group of
addresses (on top of each other) and put an option group
on the main form, listing each of the address types, and
then use the afterupdate event of the option group to
display only the selected subform.

Or, you could create separate queries for each address
group that use aliases to give the same fieldnames to
each address part. For instance qShippingAddress would
select the join id and only the Shipping Address fields,
but would rename each of the fields generically such as
Street (instead of what is probably something like
ShipStreet). Then, at run time, you could set the
recordsource of the subform based on the selection in the
option group.

Perhaps the most simple though would be to just put a
tabbed control on the form, and label each tab with an
address category and put those address category fields on
the appropriate page of the control.

HTH

-Ted Allen
 
Thank you very much.
I like the tabbed-form method, and I understand this to mean that I will
have four tabs each with a relevant subform which will be exposed when the
appropriate combo is utilized.
Regards, Frank
 
Hi Frank,

You could do it that way. It's hard to say if that would
be the best way without knowing the relationship between
your main form and the query with your address fields.
If it is a 1:1 you could consider combining the address
fields with your main forms record source (as a single
query) and just have the appropriate fields for each type
of address placed directly on the appropriate tabbed
page, without using subforms at all. Or, as you
mentioned, you could use a combo box to show only the
appropriate tab and hide all others.

To be honest, I don't really understand what your combo
boxes are doing. I originally thought that you were
using the combo box to just determine what set of address
fields would be displayed. If that were the case, I was
thinking that they would be eliminated altogether and
instead the user would just click on the appropriate tab
to enter an address (choosing tab replaces choosing from
combo box).

But, now I am thinking that maybe there is more to it.
If so, post more detailed info on the forms main data
source, the query with the address fields, and their
relationship, and the purpose of the combo boxes and I
will see if I can give you some other ideas.

HTH

-Ted Allen
 
Thanks. The aim is to combine all addresses into one query for flexibility
and easy access. This query is made up of the following tables:

1: People.
2: People_1 (Alias to allow AddressNameID to use the same table.
3: PeopleType. (Customers, Contractors, Reps, Personal, Consultants etc.)
4: AddressType: (Delivery, Account, site, etc.)
5: AddressDetails: (containes the ID's of the above and the static address,
phone and other details)

The AddressDetails table is the 'many' side and all the rest are on the 'one
' side.

This query is updateable when the info is added in the correct sequence, and
the subform 'AddressSUB' (below) is based on this query only.

The advantage of this system is to incorporate all address and people into
the one system, which may be altered in future if necessary. For example a
new category for "Business Cards" might be added in future to record the
plethora of cards one acquires, and this would mean only the entry of a new
'PeopleType' category.

The query is the basis of a subform 'AddressSUB' which appears on the main
invoice form, and a particular address is selected by a combo linked to this
'AddressSUB' via the Master/Child fields in the properties of that subform.

I have followed your advice in duplicating the subform for each category to
give:

1: 'AddressSUB_Total'
2: 'AddressSUB_Cust'
3: 'AddressSUB_Contractor'
4: 'AddressSUB_Personal'
5: etc

Each of the above types of subform has its own combo based on the query,
filtered to give only the relevant category.

The subforms have been nested in tabs, and the desired subform at the time
can be shown automatically when accessing its combo via a macro of the 'goto
control' type.

This is as far as I have got, and now I have to investigate the method for
synchronizing combo boxes, because a selection for, say, Contractors will
need to specify postal or site address, and I suppose this will mean even
more subforms such as:

1: 'AddressSUB_Contractor_Site'
2: 'AddressSUB_Contractor_Postal'
3: 'AddressSUB_Contractor_Residence'
4: etc.

Any comments would be welcome; regards Frank
 
Hi Frank,

That info helped quite a bit. Now I can see that you
actually don't have a fixed list of addresses (which is
much more flexible for future growth), so a tabbed
subform would not be the way to go. I would think that
the best way to handle this may be to just have one
generic subform to display the address fields, and set
the subforms filter or recordsource at run time based on
user input. I would think that setting the filter would
be the most simple way to go.

I would recommend considering a list box rather than a
combo box for the user selection of the address to
display (sometimes it's nice to just see all of the
choices laid out all of the time). I would also
recommend seeing if you could set it up such that you
also display a column in the list box such that the user
is notified whether an entry exists in the database for
that type (could be a "Y" or a "+" or whatever you think
looks the best). You could do this by using a domain
lookup. I'm sure there are other ways as well. This
would be an extra of course, you could worry about it
once everything else is working.

You could also consider the possibility of letting the
user select "All" in which case the form would allow them
to scroll through the addresses one at a time (You could
show the navigation bar when they select "All").

The basic idea would be to add some code to the list or
combo box such that when the user selects a choice, the
code sets the subform's filter. In your case This should
be fairly easy because you would just filter the address
details table to only show the selected address type. Of
course you would also want to code the form's On Current
event to set the display when going to another record.

I'm not sure if you are familiar with VB coding, but it
would be pretty simple. Following is a basic example of
what it would generally look like (air code), although
you would have to substitute actual field names.

Private Sub LstAddrs_AfterUpdate()
Dim strFilter As String
strFilter = "AddressTypeID = " & Me.LstAddrs.Column(0)
Me.SubformContainer.Form.Filter = strFilter
Me.FilterOn = True
End Sub

You could use similar code for the form's On Current
event. Note that if the user selects an address that
doesn't exist, the subform would automatically display
the "New Record" entry line.

I'm sure there are many other ways to handle this, and
I'm sure some others would likely have better ways of
handling it (if so hopefully they will post), but I think
this would be pretty workable and simple. Post back if
you have follow-up questions.

-Ted Allen
 
Back
Top