Subforms - is this possible?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi (again - sorry!) everybody!

Is it possible to have a dynamic subform that changes depending on a button
that has been pressed?

I'm putting together a contacts database for our business. On the left of
the screen I would like to display the name and address details for that
company.

On the right I would like to display one of a number of choices, such as
details of the contacts we speak to from a the particular company displayed
on the left, or what items of equipment we service at their place of
business, or general notes our engineers may have made on the company or
details of their current invoices and contract.

So if I click on a button that says "contacts" then the list of contacts
subform would be displayed etc etc.

Can this be done? If so, will it be simple enough for a relative newbie to
Access to achieve??

Many thanks!

Ali
 
Hi

I myself use multisheet form for such tasks.
I create a main form, add some tabs to form, and move all main form controls
to 1st page of form.
Then I create separate forms which I want to use as subforms for main form.
I set up relationships between main form source, and sources of would-be
subforms. Usually they ave common key fields
Now I open main form, activate 2nd tab of it, and drag first subform from
project window onto it. On this subform, only records linked to record on
main form are displayed. And when I add a new record, it's automatically
linked to record on main form too.

I repeat this operation with other subforms.
PS. I can insert one or several subforms into 1st page too, but mostly it
isn't resonable. And when subforms are small enough, then I han have several
of them on single tab.

Much less coding with such approach.


Arvi Laanemets
 
you can "swap" subforms in a subform control dynamically, using a combo box,
listbox, or option group to offer the various subform "choices", and running
code from that control's AfterUpdate event to set the subform control's
SourceObject, LinkChildFields, and LinkMasterFields properties to the
appropriate values for the chosen subform.

it's relatively simple to do if you have some basic experience in VBA coding
(i think you could probably do it with macros, though i've never tried it).

hth
 
Thanks Arvi!

You'll have to forgive me for sounding completely dim here but I'm quite new
to the visual side of things, having mainly dealt with Unix shell scripts in
the past!!

So...what I think you are saying is:

1) Start a new form in design view and add a tab control to it.

2) Create relationships between the tables I want to use based on a common
key ( a unique "company number" in our case.)

3) Create the separate forms that I want to use as subforms, and drag each
one on to the appropriate tab.

And then everything should just automatically link together because of the
relationships that I have created?

Ali
 
Hi

You are almost there, but better I'll explain it by some example.

Table Invoices: InvoiceID, InvoiceDate, CustomerID
Table Details: DetailID, InvoiceID, ArticleID, Amount
Additional tables are Customers (with CustomerID as primary key) and
Articles (with ArticleID as primary key). The Articles table contains p.e.
article unit, and unit price.

InvoiceID in Invoices table can be autonumeric, or not - the format of
indexed field InvoiceID in Details table depends on format of it in Invoices
table.
DetailID in Details table you better set as autonumeric.

You set 1:M relation between tables Invoices and Details through InvoiceID
(and realtions for other tables too, but I ignore them now)

You create a form Invoices, which contains all general data, you need to
enter into table Invoices, and you get from Customers table. Usually you
keep al ID fields (InvoiceID when it is autonumeric) hidden. Instead of
CustomerID textbox you can create a select-a-value combo with CustomerID as
source. You also can add calculated fields, to calculate summary data from
Details table (total value, VAT etc.).
You create a continous form Details, with controls for all needed invoice
detail info, and additional article info read from Article table. Index
fields DetailID and InvoiceID you hide, instead ArticleID textbox yo create
a combo.
You add a tab to form Invoices - now the form is multi-tab with 2 tabs. NB!
neither of tabs contains any controls at moment.
Edit Caption's for tabs - "Invoices" and "Details"
Activate form, and select all controls on it. Cut them.
Activate tab Invoices, and paste copied controls to it. Until they remain
selected, you can easily shift them all at once to right place on tab
without messing up their placement.

When all was done right, in Form view you see Invoice controls only, when
Invoices tab is selected. Otherwise you have to try again.

Now, in design view, activate Details tab, and drag the form Details on it.
When all was done rightly, all will work OK.


Arvi Laanemets
 
Back
Top