?lookup form function (1 Viewer)

icruy09

New member
Local time
Today, 06:47
Joined
Sep 17, 2019
Messages
2
Hi, still getting to grips with vba, macros etc and can't for the life of me figure this one out.. don't know if i'm overthinking?

For an internal audit database, I have a single form for the main audit details, then a continuous subform for entering clause conformance evidence connected by the audit ID.

I'm hoping to have a list of clauses in the subform(autopopulated depending on the audit section selected in the main form) that can be picked from, which are then removed from the list in the next entry as they are selected.

Is there a simple way of doing this?
 

Ranman256

Well-known member
Local time
Today, 02:47
Joined
Apr 9, 2015
Messages
4,339
see example. I have a pick list the user dbl-clicks to add to the Pick table.
the query then joins tPicked to the data table and only shows those results.

or add the picks to a sub table.
 

Attachments

  • pick state-lbl.png
    pick state-lbl.png
    34.3 KB · Views: 58

Micron

AWF VIP
Local time
Today, 02:47
Joined
Oct 20, 2018
Messages
3,476
Not sure I agree with the approach, but maybe I'm not understanding your post. Here's my take on this phase:
- the form header should hold everything pertaining to the audit that would not change regardless of what clauses are being audited (date, auditee, location, auditor(s) etc.)
- the subform would be below that and already contain the clauses for entering findings, complete with the requirement.
- these clauses would have been set up by a different process (a similar form for building the audit plan).
- you'd enter the findings beside the requirement for each clause/sub-clause record


You seem to be compiling the clause selection at the time of entering findings, which suggests you have built the audit using some other tool like Word and are using the db to store findings. Why not do it all in the db?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:47
Joined
Oct 29, 2018
Messages
21,358
Hi icruy09. Welcome to AWF!
 

icruy09

New member
Local time
Today, 06:47
Joined
Sep 17, 2019
Messages
2
Not sure I agree with the approach, but maybe I'm not understanding your post. Here's my take on this phase:
- the form header should hold everything pertaining to the audit that would not change regardless of what clauses are being audited (date, auditee, location, auditor(s) etc.)
- the subform would be below that and already contain the clauses for entering findings, complete with the requirement.
- these clauses would have been set up by a different process (a similar form for building the audit plan).
- you'd enter the findings beside the requirement for each clause/sub-clause record


You seem to be compiling the clause selection at the time of entering findings, which suggests you have built the audit using some other tool like Word and are using the db to store findings. Why not do it all in the db?

Thanks for your reply. The audit was originally a word document however it had multiple sections with repeat information and I am in the process of trying to streamline it.

The breakdown of our internal audit system is this:

Our QMS sections contain clauses from 2 different standards (totaling 500+ clauses altogether, but none are cross referenced in different sections)
Each section is audited at least once per year by different auditors
Each audit has to list the clauses with whether or not each individual one conforms and have attached evidence, but some evidence can cover more than one clause
The non-conformances are then added to a separate table where a corrective action, root cause and person responsible is added

So far I have separate pre-filled tables with linking IDs for clauses, sections, audit information, standards and auditor information to avoid repeat information being added for each audit, then an empty table to record conformance/non-conformance with evidence

What I'm interpreting from your suggestion is that each audit should have a table with the clauses pre-entered? Surely where a section is audited more than once it would defeat the point of using access over excel if the clause information is just going to be repeated? I don't know if we're just misunderstanding each other..
 

Micron

AWF VIP
Local time
Today, 02:47
Joined
Oct 20, 2018
Messages
3,476
What I'm interpreting from your suggestion is that each audit should have a table with the clauses pre-entered?
Not at all. It seems like your schema is closer to what I would do than what I interpreted from this comment
I'm hoping to have a list of clauses in the subform(autopopulated depending on the audit section selected in the main form) that can be picked from, which are then removed from the list in the next entry as they are selected.
Maybe you will have to clarify and embellish that statement if the answer you got isn't satisfactory.

To me it sounded like that at the time of findings entry you were building a set of records of what clauses were involved. I was saying that when you pick the audit to enter findings, all the clauses pertinent to a specific audit are listed because you'd have already defined them in the audit setup process. That's how you'd replace Word documents, because your audit questions would also be predefined (and are in their own table) and you run them as reports. All that is based on the assumption that surely you don't audit every clause for an internal audit. I guess that's just the way I was used to doing it. Sorry for any confusion.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:47
Joined
Feb 19, 2002
Messages
42,981
I can't see a solution for you in this thread so I'll tell you what I do.
1. There is a table of all the clauses you will need. Given the number of these, you might want to categorize them to make filtering for the ones you want simpler but that's for later.
2. In the subform of the audit form, you have a combo and the rowsource of the combo selects the clauses from the table.
3. To make this combo show only the clauses so far unused for this audit, you need to join to a query that includes selection criteria that selects all the clauses currently used for this audit. the join of the clause table to this query will be a left join and it will select rows where the auditID is null. You can use the unmatched query wizard to build this if you need to.

If you can post the form and a few tables with data, someone will help you to build the RowSource query.
 

Users who are viewing this thread

Top Bottom