Can one table's record affect another tables record?

[buscher75]

I created a database to track internal audits. I have two tables; the main
table “ComplAudit†and a reference table with the auditor’s schedule. In my
entry form for the ComplAudit table, I added subforms to show, by auditor and
month, what departments are assigned. This all works great.

I am wondering - if I add a yes/no field named “complete†to the reference
table, could the entry in the ComplAudit table trigger a “yes†value in the
reference table’s “complete†field? Thus in the ComplAudit table, if Auditor
1, for Sept 2008, audits Dept "A", there is a yes value added to the
reference table within the record that called out Auditor 1, Sept 2008, Dept
"A". Is this possible? Thanks for your opinions and advice.

 

[Jeff Boyce]

It sounds like you are trying to do this within and between tables. If so,
DON'T!

Access tables store data, Access forms display it. Use a form and the rich
event environment to help you manage your data.

By the way, I don't understand your data structure well enough to offer
specific suggestions. Are you saying that your second table is a "child"
table to the "ComplAudit" table?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[buscher75]

I would say yes, I believe so. There are relationships created between the
tables. Would it be best to add a hidden checkbox to the form and write code
for it? Something like if value of fields SchdMonth, AuditorNumb, and Dept =
a record in reference table then check box = yes or true? Better yet those
three fields must = a record in the reference table.

The reference table fields are MnthSchd, AuditorNumb and three department
fields labeled Audit1, Audit2, and Audit3. Each record in the ComplAudit
table should equal the SchedMonth, AuditNumb and one of the three department
fields.

Would this code be in the before update event? And, would you be able to
help me write it? I really appreciate your insight on this. Thanks.

 

[Jeff Boyce]

Usually, it is YOU that creates relationships between the tables. Since
your tables reflect the data, the relationship(s) reflect the data. What
are the relationships between your two tables?

Based on your description of 'repeating fields' (i.e., "Audit1, Audit2,
Audit3"), I suspect your table structure is an attempt to replicate a
spreadsheet, rather than use the relational design available in Access. In
fact, unless your tables are well-normalized, both you and Access will have
to struggle to make use of Access' relationally-oriented features/functions.

Until the table structure is cleaned up, it doesn't make much sense to be
working on cleaning up the forms/procedures...

Folks here in the newsgroups are largely volunteering their time. If you
need someone to help you build an application, you may want to look to hire
someone. If you have specific questions about what you've already
done/tried, this is a great place to post them.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP