Access - Need help with subform

[messineo5]

I hope this makes sense. I have created a database for seminar/tradeshow
speakers.

My problem is that my subform does not show changes to data made in other
tables/forms. The data exists in the table the subform runs on, but does not
show in form view.

Contacts Form has a Registration Subform that pulls session names from a
separate sessions table.

I can register a contact for a session in the subform. If I close the
form(s) and update the name of a session from the session table, the
registration no longer appears in the subform view.

Can I make these changes auto update and not lose the record view?

Thanks!

Kristen

 

[Ken Sheridan]

Kristen:

Assuming that you have tables such as Contacts, Sessions and Registrations,
the last modelling the many-to-many relationship between the first two and
containing columns such as ContactID and Session, then you have two options:

1. Enforce the relationship between Sessions and Registrations and in the
relationship dialogue enforce Cascade Updates. Before you do this, however,
you'll need to ensure that all the values in the Session column in
Registrations match the current values in the Session table.

2. Give the Session table an autonumber 'surrogate' primary key such as
SessionID rather than using the session name as the 'natural' primary key.
Then in Registrations use a long integer number SessionID as the foreign key
in pace of the Session column. In the subform you'll need to amend the
design so that it uses a combo box bound to the SessionID foreign key column,
set up so that the SessionID is hidden but the Session name shows. That way
you change the session name in Sessions without affecting the values of the
keys, so the subform will show the amended name. As with option 1 above
you'll need to fill the SessionID column in Registrations with values which
match those of the key of Sessions before you can create and enforce the
relationship between the tables. You won't need to enforce Cascade Updates,
however, as the value of an autonumber cannot be changed.

The first of these is the simpler and, provided the session names are
distinct, would be the most appropriate solution.

Ken Sheridan
Stafford, England

 

[messineo5]

Thank you Ken! You are a life saver!

Kristen:

Assuming that you have tables such as Contacts, Sessions and Registrations,
the last modelling the many-to-many relationship between the first two and
containing columns such as ContactID and Session, then you have two options:

1. Enforce the relationship between Sessions and Registrations and in the
relationship dialogue enforce Cascade Updates. Before you do this, however,
you'll need to ensure that all the values in the Session column in
Registrations match the current values in the Session table.

2. Give the Session table an autonumber 'surrogate' primary key such as
SessionID rather than using the session name as the 'natural' primary key.
Then in Registrations use a long integer number SessionID as the foreign key
in pace of the Session column. In the subform you'll need to amend the
design so that it uses a combo box bound to the SessionID foreign key column,
set up so that the SessionID is hidden but the Session name shows. That way
you change the session name in Sessions without affecting the values of the
keys, so the subform will show the amended name. As with option 1 above
you'll need to fill the SessionID column in Registrations with values which
match those of the key of Sessions before you can create and enforce the
relationship between the tables. You won't need to enforce Cascade Updates,
however, as the value of an autonumber cannot be changed.

The first of these is the simpler and, provided the session names are
distinct, would be the most appropriate solution.

Ken Sheridan
Stafford, England