Adding new details in Access 2000

  • Thread starter Thread starter Scotty
  • Start date Start date
S

Scotty

Heres's hoping...
I have a database which is made up of several tables "contacts",
"coaching", "misc" etc. All the tables are linked by an "ID" field.
I have a form to input new records and I need to tnter the "ID" value
int 2 tables to maintain the link.
a) How can I do this ?
B) IS this the best way to enterdata and maintain the relationship?
 
B - yes, access is built for this so you do it Access's way!

A - All your tables should have ContactID as a field. It is the PK in
TblContacts and the FK in the other tables. Your main form should be based on a
query based on TblContacts and each of the other tables should be represented by
its own subform. Each subform should be based on a query based on one of the
other tables. Once you have created the main form and embedded the first
subform, open the main form in design view, click on the subform control to
select it, open properties, go to the Data tab and type in ContactID in the
LinkMaster and Link Child properties. Repeat this step after you embed each
subform.
 
PC said:
B - yes, access is built for this so you do it Access's way!

A - All your tables should have ContactID as a field. It is the PK in
TblContacts and the FK in the other tables. Your main form should be based on a
query based on TblContacts and each of the other tables should be represented by
its own subform. Each subform should be based on a query based on one of the
other tables. Once you have created the main form and embedded the first
subform, open the main form in design view, click on the subform control to
select it, open properties, go to the Data tab and type in ContactID in the
LinkMaster and Link Child properties. Repeat this step after you embed each
subform.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
Click to expand...
Thanks for that, I think it is going to work.

While I am on, I also need to find a way to create a "last updated"
field. I was hoping that if any field in a record, possibly spanning
more than one table, is changed it would time stamp this field.
Can this be done?
 
If your tables are designed correctly, there should be no fields that are
contained in more than one table. You can however add, edit or delete records in
multiple tables on the same form.

If the basis of your form is directly one table or indirectly through a query
one table, you can timestamp records by including a TimeStamp field and adding
the following code to the AfterUpdate event of the form:
Me!TimeStamp = Now() 'records current date and time
or
Me!Timestamp = Date() 'records current date only

If the basis of your form is more than one table, you need a Timestamp field in
each table and in the AfterUpdate event of each field for each table you need
the following code:
The fields from TableA would have:
Me!TableA.Timestamp = Now() 'records current date and time
or
Me!TableA.Timestamp = Date() 'records current date only

The fields from TableB would have:
Me!TableB.Timestamp = Now() 'records current date and time
or
Me!TableB.Timestamp = Date() 'records current date only
 
PC said:
B - yes, access is built for this so you do it Access's way!

A - All your tables should have ContactID as a field. It is the PK in
TblContacts and the FK in the other tables. Your main form should be based on a
query based on TblContacts and each of the other tables should be represented by
its own subform. Each subform should be based on a query based on one of the
other tables. Once you have created the main form and embedded the first
subform, open the main form in design view, click on the subform control to
select it, open properties, go to the Data tab and type in ContactID in the
LinkMaster and Link Child properties. Repeat this step after you embed each
subform.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com
Click to expand...
Not as simple as I thought...
I have the main contacts table with a sub-form containing coaching info
linked as described. Lookin good...
I now have a situation where I need to link club info from a table of
club details. The club table has fields club_code and club_name. I
want to be able to link club_code in this to the club_code field in
contacts so that I can select a club name fron a dropdown and enter the
club id into the record in contacts
 
Back
Top