User Interface Form - Report (1 Viewer)

Good morning from the UK all,

I am not too sure if this is the right location for this post but as it covers multiple access functions I thought it best here.

Currently when performing Internal Audits at my company, It is a fairly manual process to print out the clauses that we are going to audit against as we copy and paste them into our audit template in excel and then print the entire audit.

So this brings me to my idea. Would it be possible in access to have a form where the user could select which clauses they would like to audit against using a tick box perhaps and then it covert into a report that would be printable.

If this is possible, would it be fairly complex to complete, I am no programmer myself but understand access for the most part.

In my head I envision a table with all the audit clauses in, then a form pulls the information from there. And then there is a generate report button that identifies what clauses have been selected and then publishes them into a report in the correct sections.


Any help would be appreciated.
- Mark

it should be possible

see this link for a similar requirement

https://www.access-programmers.co.uk/forums/showthread.php?p=1639470

start at the first post and work your way through.

Suggest your table would contain a uniqueID a sort order and the paragraph field

Hi Mark. Could you please define what a “clause” is? Thanks.

CJ_London - Great, thank you, I will work my way through and see if hellps.

theDBguy - So a clause is a description of a particular part of our audit standard that has to be followed. Usually around a paragraph long, with some longer / shorter than others of course.

There are 10 clauses in our standard but they may include sub clauses i.e. 7.1, 7.1.1, 7.2, 7.2.1 etc

Hope that helps.

Mooish said:

theDBguy - So a clause is a description of a particular part of our audit standard that has to be followed. Usually around a paragraph long, with some longer / shorter than others of course.

There are 10 clauses in our standard but they may include sub clauses i.e. 7.1, 7.1.1, 7.2, 7.2.1 etc

Hope that helps.

Click to expand...

Hi. Thanks for the explanation. And where do you store these clauses? If in a table, is your report bound to this table?

If the clauses contain no variable information, then you can just store the text in a memo (Long Text) field. One clause per record. You can even make them Rich Text so you can embed formatting. The table definition would be something like:
tblClauses
ClauseID (autonumber, primary key)
ClauseNum (integer) (needs a unique index to prevent duplicates)
ClauseText (Long Text)
LastChangeDT (DateTime)
LastChangeBy (Short Text)

The last two fields are internal audit fields so you know when a clause was last updated and by whom.

If you want to use sub clauses and you have only one level, then you can use a second table. However, if ALL sub clauses are always included when the parent clause is included, then just put the sub clauses in the text of the parent. But, you you can pick and choose subclauses, then the process requires them to be individual records. Although you can use a second table if you have only two levels, this will become a problem should you need a third level so I would probably try to use one table with a self referencing design similar to a Bill Of Material in a manufacturing system.

If the clauses contain variable information, you will also need additional tables and you will need code to fill the variables. You have to be very disciplined when making the text so that variables are enclosed in {} or []. These characters are rarely used so they make good delimiters. The names inside them need to be consistent and defined and you will need code to ensure that they are valid to prevent future errors.

To add to what the others have posted, since you will be running a report on this, I'd suggest two additional tables that you will want;

TblAudit-This contains information relevant to a given audit you will be doing (date, scope, department, conducted by, what not)
TblAuditClauses-Has the Primary key for both a TblAudit record and a TblClauses record. Links the two together to show which clauses are used for a given audit. You'd then set up a query off of TblAuditClauses joined to TblClauses to get the actual clauses used in a given audit.

This also means that, unlike just "ticking off the clauses" you'll have a record in your database of what should have been in there.

Welcome Moosh,
I had some time this afternoon so I took a stab at a solution for you. There is a form that explains some of the details.

Also take a look at the References window to see the database schema.

Sorry, there's a bug in this version with the opening the popup form. I'm not sure how to fix it but I'll work on it later.

Wow, I'm overwhelmed at the response! Thank you all very much for the help. I will certainly take it all in and consider all the extremely useful information you have provided.

@Pat Hartman - The solution you have come up with is brilliant and does help me out a lot! I will probably tweak a couple of areas but for the most part this is a massive help.

Again thank you all for the help. Hopefully I should be able to work with what you've all give me

I fixed the bug. The forms all work now. Since you are new to Access, please open the forms in design view and look at the code behind them. There is very little and hopefully you will understand what you read.