Form & Subform Problems!!

[tflett]

I have a data entry form consisting of one main form (Client List Table) with
three subforms in it (Primary Training Table, Secondary Training Table, and
Employment table). I have everything working but when I click on add new
client, the combo box I created in each subform stays the same. Its like its
not linked to the subforms. Could it be relationship problems?

The combo boxes in the 2 training subforms are linked to a trades tables.
This table has all the info on trades I need for my DB. The combo box in the
employment table is linked to an employer codes table that has all the info
on employers I need for my DB. All the info that is linked to the combo
boxes are already typed in the tables i linked them to.

Any suggestions??

 

[Ken Sheridan]

What you have here is one table, Client List which represents an entity type
which is related many-to-many to those entity types represented by the Trades
table and the Employers tables. The many-to-many relationships are modelled
by the Primary Training Table, Secondary Training Table and Employment Table.

The three tables modelling the many-to-many relationships should therefore
each have a foreign key column such as ClientID referencing the primary key
of the Client List table. They should also respectively have another foreign
key column referencing the primary keys of the Trades table (for each of the
two training tables) and the Employers table (for the Employment table).
They might also have other columns representing attributers of each
relationship such as the training or employment dates etc.

The main parent form, in single form view, will be based on the Client List
table (or better still a sorted query on that table). The subforms, in
continuous form or datasheet view, will be based on the Primary Training,
Secondary Training and Employment tables (or queries on those tables). In
each case the LinkMasterFields and LinkChildFields properties will be
ClientID (or whatever your keys are called).

The combo boxes in each subform will be bound to the foreign key columns
e.g. TradeID and EmployerID but set up to show the relevant text name of the
trade or employer by setting the properties of the combo box (taking trade as
an example) along the following lines:

As its RowSource property:

SELECT TradeID, Trade FROM Trades ORDER BY Trade;

Other properties of the combo box would be:

BoundColumn 1
ColumnCount 2
ColumnWidths 0cm;8cm (or rough equivalent in inches but the first
dimension must be zero to hide the first, TradeID, column, so only the name
shows.

This assumes that the Trades table uses a 'surrogate' TradeID such as an
autonumber as its primary key rather than using the text Trade column as the
'natural' key. If the latter is the case then the foreign keys in the
training tables would also be a text field and the combo box's properties
would be like so:

As its RowSource property:

SELECT Trade FROM Trades ORDER BY Trade;

Other properties of the combo box would be:

BoundColumn 1
ColumnCount 1
ColumnWidths Leave blank.

With the above set-up you should be able to enter records in the subform for
each trade appropriate to the client in the parent form. When you go to a
new blank record in the parent form to add a new client the subform's should
each show one blank row with empty combo boxes ready for you to select an
item.

The set up for the employment subform would be similar, but referencing the
Employers table in the RowSource of the combo box, which in this case would
be bound to an EmployerID foreign key or similar in the Employment table.

Check to see that your set-up is along the above lines. If it is then it
should work fine.

One thing I would suggest, however, is that rather than having separate
primary and secondary training tables you have a single Training table which
includes a Boolean (Yes/No) Primary column. You can still have two separate
subforms, which in this case would be based on queries on a single Training
table, in the case of that for primary training 'WHERE Primary' in the case
of secondary training 'WHERE NOT Primary'. The DefaultValue properties of a
Check Box control bound to the primary column in the subforms would be set
to TRUE and FALSE respectively so they are automatically given the correct
value. You could even hide them as there is no real point in showing them in
the subforms as they merely need to have the right values, not be seen.

Ken Sheridan
Stafford, England

 

[tflett]

So what you're saying is that all the relationships are many-to-many and I
should change the relationships i have to that? The relationships I have now
are all one-to-many, Client info table(one) to Employee Table(Many), Client
Info Table(One) to Primary Training Table(Many), Client Info Table(One) to
Secondary Training Table(Many), Employee Table(One) to Employer Codes(Many).
So what about the Trades Table to both Primary and Secondary Tables and
Employer Codes Table to Employee Table??
Could You help me out on this??

 

[Ken Sheridan]

The many-to-many relationships are between the entity types represented by
the tables, not the tables per se. This type of relationship is modelled by
a table, and only exists conceptually; the relationship is not created
directly between two tables. The table which models the many-to-many
relationship resolves it into two separate one-to-many relationships with the
referenced tables like so:

Clients-----<Primary Training>-----Trades

Clients-----<Secondary Training>-----Trades

Clients-----<Employment>-----Employers

where the < and > characters represent the 'many' end of each relationship.
The Training and Employment tables in this case model the many-to-many
relationships between Clients and Trades or Employers. A relationship type
is really just a special kind of entity type, so its still true to say that
tables model entity types.

In your case the subforms should be based on the tables modelling the
many-to-many relationship types in the way I described earlier.

Ken Sheridan
Stafford, England

 

[tflett]

Thanks a bunch Ken, your information was extremely helpful.

 

[tflett]

My **** form still won't update the combo boxes... Should the three tables
modelling the many-to-many relationships have multiple primary keys?

 

[Ken Sheridan]

The candidate keys of the tables underlying each subform will be a
combination of the two foreign keys, e.g. ClientID and TradeID, possibly
along with another column (or more) if for instance there could be two rows
in the subform for one client and the same trade, but with different dates
say, recording two different training instances in the same trade for that
client. These need not necessarily be designated as the primary key,
however, and many people would introduce a surrogate key column, most
commonly an autonumber. I would normally designate the multiple columns as
the key myself, but it shouldn't have any effect on whether the control can
be updated or not.

I can't really figure out where the problem lies if your set up mirrors that
I described. This type of form/subform scenario with the subform bound to a
table which models a many-to-may relationship between the parent form's
underlying table and a third table, is a very common arrangement and should
be straightforward to set up. In essence the combo box is bound to (i.e. has
as its ControlSource property) the foreign key column which references the
primary key of third table, Trades or Employers in your case, while the
foreign key column, ClientID in your case, which references the key of the
parent form's table, is the subform control's LinkChildFields property, by
virtue of which it is automatically assigned the value f the parent form's
current record's key.

You'll find an example of a subform used in this way in the sample Northwind
database which comes with Access. In that the Orders Subform is based on the
Order Details table (the subform actually uses a query, but that's immaterial
to this issue) which models the many-to-many relationship between Orders and
Products. In the subform a combo box is bound to the ProductID column, which
is the foreign key referencing the key of the Products table. The combo box
is set up in the way I described earlier to show the name of the product,
hiding the ProductID which is its actual value. It also shows a Discontinued
column in its list, but again that's immaterial to the current issue.

Ken Sheridan
Stafford, England