Full list of records in subform with link to main form

[Guest]

Hi I'm having problems trying to work out how to do this.

I am writing a database to store audit data and one of the things that needs
tracking are the ISO clauses that aplly to the audit.

I have set up a main form for the audit details and want to click a button,
open another form with the full list of ISO clauses and check a box against
several of the clauses to indicate that they apply. This data then needs to
be recorded against the audit so that when a user goes back into the record
and clicks the ISO button, the list of clauses opens with those that apply to
the audit already checked.

I can't figure out how to do this - can anyone help! It's probably really
simple ...

Thanks in advance

 

[Guest]

You will need to create a 'link' or 'junction' table.

Audit table fields: AuditID, other fields...
ISO table fields: ISOID, other fields...
AuditISO table fields: - AuditID, ISOID

This sort of table usually has a compound primary key which is the
combination of the two ID fields. To set that up, select both fields and then
click the Primary key button.

Now you want a method for adding data to this table.
In your ISO form, you will need to use some code to populate the 'link'
table with your selections. You can either do it each time you select a
particular item or select all your items and update the table.

In your code, you can pick up the current AuditID and supply that with each
selected ISOID.

If you are not too familiar with VBA code and SQL, you may find it easier to
do it in the AfterUpdate event of each checkbox using a CurrentDb.Execute and
an appropriate SQL string that defines what you want to do depending on the
value of the checkbox. If it is True, insert a record, if it is false, delete
a record (if exists).

Hope that helps!

Steve

 

[Guest]

Hi Steve,

I have a junction table set up and I have some code to pass the audit ID
field from the main audit details form to the ISO form - this works fine.

The problem I am having is working out a) how to create a form that shows
all the ISO clauses with a checkbox by each of them (at the moment all I get
when I open the form is the clauses that relate to the relevant audit) and b)
how to pass the "yes" from the checkboxes to the junction table. I would
prefer to pass the info in one go, rather than using After Update.

Could I do this with unbound fields and a lookup facility, then an append
query to pass the checkbox info to the junction table? If so, I am not too
familiar with lookup in Access - do you have an idea of the sort of function
I need to write?

Thanks for your help so far

 

[Guest]

You have and Audit table, ISO table and AuditISO junction table, yes?

The form you mention should be based on the ISO table with no reference to
Audit ID's. You should not be passing the 'Yes' value. Your junction table
should just have two fields, AuditID and ISOID, each row will consist of an
AduitID and a related ISOID. You would use this data to show related ISO
clauses for a particular Audit record.

How long are the descriptions for these ISO clauses?
Would they fit in a list box?

If you want to use chackboxes, you could just select all those that apply,
then create a procedure that inserts a record in your junction table
consisting of the current AuditID and selected ISOID (determined by the
related checkbox) for each ISO clause that has been selected.

Steve

 

[Guest]

I have:
tblAuditDetails
tblISOClauses (contains a list of the clauses and their headings)
tblISOCoverage (contains AuditID and ISOID which are both primary keys)

The form frmISO contains txtAuditID and txtAuditTitle and subfrmISOCoverage
(which is where I want to show all the ISO clauses and to check a box to
indicate that it applies to the audit). frmISO gets the AuditID from OpenArgs
which is initiated by clicking a button on frmAuditDetails.

I want to show all available clauses rather than using a list box so that
the user can see at a glance what applies. This form isn't just for data
entry - it is also for information.

Hope this helps

 

[Guest]

maybe you should consider different forms, physically overlaying each other
and at the right moment switched on and off ( form.visible = true/false)
one form: read only showing the ISO's for a specific audit
second form: for selection from the total list of available ISO's, for a new
entry in the audit table
third: a form showing all the ISO's and the ones already chosen, for editing
existing entries in the audit table i.e adding forgotten ISO's

regard Peter