Create selection tool form that autopopulates fields on report

[Giles64]

Hi All

I would appreciate guidance and help to create a simple selection tool for our Project Managers to combine elements from different Work Method Statements(WMS) which creates a Site Specific Work Method Statement (SWMS). There would be some general elements that would be required regardless of any activity (so default tick against those).

I would like this tool to:
1) select a project number from a combo list which auto populates the report with address, client details etc.
2) set default against generic elements, that can be deselected if required
3) auto populate corresponding fields from table to report based on "Process Step" selections on form


e.g. Table would have Process Step, Potential Hazard/Risk, Hazard Control Measures, Action By
I select "Process Step" on form and report will show all the above fields corresponding data from table
4) have the ability to rank the order of each step so they are inserted on report in that order
5) display selection results and then be able to rate "Residual Risk Rating"
5) complete a tick sheet of what standards, legislation etc. applies based on their selections.
Within table you could have field that should you select this "Process Step" that legislation etc applies and ticks the appropriate box
6) have the ability to type in unique "Process Step" and associated fields if required
7) be able to select relevant MSDS, PSDS etc from library on form and embed pdf of same to report

To give you an example, current WMS's may be for
1) Civil Works, i.e. would include trenching by hand digging, mechanical excavation, vacuum etc
2) Prevention of Falling from Heights, i.e. would include use of ladder, elevated work platforms, working on roofs etc
3) Hauling of Cable, i.e. hand rodding, use of compressor, optic winches etc

For each method i.e. hand digging, mechanical excavation, vacuum - there will be multiple selections to make – so it would be good if it could “select all” of WMS or all of a "Process Step" or individual lines from a "Process Step" as required.

Scenario - I want to create a Site Specific Work Method Statement whereby I am going to relocate an aerial service (by climbing a ladder) underground (by mechanical excavation). I would need to incorporate:

1) Generic Site activities – default select Step 1-3,24,26,27 of attachment
2) Civil Works – mechanical excavation Step 7,11,14,16 of attachment
3) Prevention of Falling from Heights – use of a ladder
4) Haul cable - hand rodding

At the moment I’m thinking if we have a cascading combo box that will lead you to the base WMS (of which there are 20-30) and then at each level all options are displayed it allows you to select those elements required for that WMS (example attached) and then go onto the next base WMS and the process is repeated until all elements of the scenario have been addressed. You then rank order as required and create your report (in same format as attachment).

I’m just not sure which way to go because the success is in the planning. I am self taught but definitely need some expert advice in this instance – I’m sure it is possible just not sure how to set the framework.

Thanks for your time and help.

[rpeare]

Have you got a database already built? I'm not clear whether you need help designing or whether you have a functional database and you are trying to get information out of it in a more robust way than reprinting a 29 page document.

[Giles64]

It would be a new database.

[rpeare]

So you are looking for advice on the structure?

If you think about a database every table should have it's own primary key field *first* so keep that in mind as you go forward. If you're just starting out use the autonumber data type. Having a primary key that can not be changed by the user will help you organize.

Now, for each piece of data on your form, if it is a field that you want to be able to pick from a list, each one of those items you will want a supporting table. So for instance on your first page where you have COMPANY/ORGANIZATION, there would be a supporting table like:

Comp_ID  Comp_Name  Comp_Contact  Comp_Address  Comp_State ---> other company related information 
1        Company A  John Doe      1 First St    FL
2        Company B  Jane Doe      2 Last St     WI

Your page 2 would be a little trickier if those 'options' are a static list but you want to require certain ones for certain choices that would all have to be set up within your table structure or within your code running your forms. If those options change (i.e. if this were not a civil project) depending on the project type you would have to account for that in your table structure as well, but without knowing more about that I can't be more specific so let's take a generic example. Let's say you have 2 project types (CIVIL and HAULING CABLE) each of these project types has a number of required 'tasks', if the tasks overlap then you'd have to have 3 tables, a table for the project type, a table for the tasks and a junction table that connects the project to the task. Personally I would do it this way no matter what just in case, in the future, you do have tasks that overlap on different project types.

In practical terms your table would be something like:

tblProjType
PT_ID  PT_Desc  ----> other project type related information
1      Civil
2      Hauling Cable

tblTaskType
TT_ID  TT_Desc
1      WHS Act 2011
2      WHS Regulation 2011
3      Environmental Protection Act 1994
4      Environmental Protection Regulation 2008

tblProjTaskXref
PTX_ID  PT_ID  TT_ID  -----> other project task related information
1       1      3
2       1      2
3       1      4
4       2      3
5       2      1

In this example a CIVIL project type would have required tasks 2, 3 and 4, where a HAULING CABLE project type would have required tasks 1 and 3.

Setting up like this allows you to require certain fields (if done properly), you could for instance have a 'required' field in your tblProjTaskXref table, if the required field is set to yes then the item must be checked, if not it just appears as an option on your data entry screens.

Page 7 to 24 seem to be free form but I don't know your business it might be that the left most column is a static item as are all the options that you can choose under that item. If that's the case you would do a similar setup to the project type/task type where you relate options to a specific parent item so they user's allowable selections are limited when they choose an item.

Just keep in mind, depending on how this is set up (again I don't know your business) but this may not fit on a single report unless you design a few subreports and fit them on to a 'main' report, access is limited to 22 inches in width in the report designer and, I think 22 inches in depth.

[Giles64]

Thanks for your advice, I will give it a try this week

[Giles64]

So sorry for the lack of response, trying to get enough together to show you what I’m thinking, very basic at the moment – I would really appreciate your input whether you think this would work. I took on board your suggestions and they did make sense and I understood your thinking, but as I started to emulate your suggestions into our requirements I realised if possible I would like to keep the format the same as several of our other databases I have created – but they don’t require the amalgamation of lots of info selected from multiple sources into the same report. Ultimately my intention is to integrate them into SQL or similar format.

I have basically
1) split the SWMS format into sections and tabbed them off the main form, then added Sforms for generic WMSs to enable selections
2) split all our WMS’s Process Steps into sections and ranked them (tblStepProcessOrder) according to where they will sit within report/subreport. Should there be a requirement to split/add “Task Steps” and prioritise further, that is possible
3) created tblTaskSteps with accompanying “PotentialHazRisk, HazardControlMeasure, and ActionBy” fields. I have added OLE objects x 2 on SfTaskSteps for diagrams to be included, not sure this is at all right – haven’t ever had a need to do this before
4) created generic WMS subforms which are in a tick format that when you select the “ProcessStep” the “PotentialHazRisk, HazardControlMeasure, and ActionBy” from “tblTaskSteps” would autopopulate the report. Project Manager to rate “residual risk” at this stage and that would need to autopopulate into report also.
5) created tblReferenceTask” so based on selection made in 4, reference (i.e. Standards/COPs) will autopoulate into relevant section of report.

My Wish List

1. All items in (tblAllSWMS) to be automatically selected and only unticked if they don’t apply – they are generic to each project
2. Ability to “select all” of a section on the WMS tabs if all of section applies, e.g. WMS018 Excavating of trench using Excavators and Skid steer loaders (backfilling) and Tippers
3. Be able to import SDS given a combo box to select from and auto populate into Part 3b of SWMS report, SDS would generally be in pdf format
4. On selections from WMS tabs auto populate report (Part3b) with only the legislation, COPs, Standards etc that apply to that WMS (tblReferenceTask)
5. Have all ticked items from all tabbed subforms for that record auto populate report in the order given (tblStep Process Order)
6. Be able to import diagrams into SfTask Steps where I have OLE object fields if this is the best way to do it or find an alternative
7. Ensure sychronisation all subforms to main form

I have very little experience in building reports and any associated VBA, Event Procedure and Macros building – I EVENTUALLY seem to manage as much as I need. Any help would be greatly appreciated though, especially given the size of report I would need. I would value your input and experience. Thanks!


I also upgraded to Access 2013 software, I will attach what I have done so far.