Solved Three-part form

[Kayleigh]

I am working on a form which is going to be based on three tables. It is logging incidents reported so first table has general details (date/time), second table flags incident as specific categories - can be multiple, and third table records details of person involved - also may be more than one.
So form should show a couple of initial fields, then categories are ticked - how do I do this so that it adds the categories to table 2?
Ideally would like the categories to be displayed as tick boxes - is this possible?
Then third part of form is displayed depending on which category is ticked. Also not sure how to do this?

Regards,
Krayna

 

[theDBguy]

Hi. Can you post a sample copy of your db with test data?

 

[Ranman256]

along with the table ,tCatas, youd need a table for the individual to hold the ticked catas: Cata, Chosen.
youd fill tPickCata table from tCatas.
They user can then pick the catas needed, the query would join tPickCata to the main table and pull only those items.

 

[Kayleigh]

Thanks @Ranman256 !
Sorry a little tricky to post the DB as it is a component of many parts. Would screenshots be useful?

 

[MajP]

is logging incidents reported so first table has general details (date/time), second table flags incident as specific categories - can be multiple, and third table records details of person involved - also may be more than one.
So form should show a couple of initial fields, then categories are ticked

I interpret the categories being ticked as a "Checklist". To do a checklist you normally show the data in one way and write to a different table. In this example I have Users and Selections. You click the selections for each user and they are written to the User Selections table. It would be a junction table (many to many).

Then write to the junction table
tblUserSelections tblUserSelections

ID	UserID_FK	SelectionID_FK
40​	4​	1​
42​	4​	3​
54​	3​	18​
53​	3​	14​
38​	3​	3​
44​	3​	1​
43​	3​	2​

The code is done the same way. You need to load the existing checks in the on current event. Write to the junction table when you check a selection. Delete from the table when you uncheck.
See if this is the feature you want.

 

[MajP]

The design, if I understand this, is pretty much as I showed.
1. Main form bound to Incident table
2. Subform 1 is the checklist which is bound to the categories table, but writes to the (incident categories ) junction table
3. The 2 subform is bound to likely another junction table which is the incident persons involved. This depends if you pick people from a list of existing people or if people are pretty unique to each incident. If people are unique to just one incident then this is just a reqular table (1 to many). Need to understand how people relate to incidents. You can have many people related to a single incident, but can a person be related to multiple incidents?

 

[Kayleigh]

Here are some screenshots of the relationship between incident log and details, as well as the categories which can be selected multiple per incident.
So I have a main form for the basic details and one subform (there will be others depending on category of incident) and I would like to know how to list the categories as tick boxes and assign these to the jtblincidentcat?
Have had a look at your sample DB @MajP and looks like its on right track - will definitely give it a try! If you can think of a way the categories can be listed horizontally instead of vertically would appreciate so it looks more concise.

 

[mike60smart]

Hi Krayna
If you are selecting Multiple Categories then the only way to select them on your Form is using a Combobox which will always list them Vertically.

If you want them to be displayed Horizontally then you can do this in your Report based on your selected data.

 

[MajP]

Have had a look at your sample DB @MajP and looks like its on right track - will definitely give it a try! If you can think of a way the categories can be listed horizontally instead of vertically would appreciate so it looks more concise.

I think my assumptions are all correct based on your relationship. However, you did not show or answer my questions about people. Is there an incident to people junction table as well? Process is the same.

If you want to have checks then what I show using a subform will work. If you would like to use a multiselect listbox that will also work; however, there is not checks. Personally I do not find them to be intuitive especially if you have to scroll. The process is basically the same the code is a little different.

If you want to do this horizontally, you can do it but it would have to be unbound checks. That is OK if your amount of categories is small and unchanging, but it has big disadvantages. The code will be similar but more lengthy. If you change/add a category you have to redesign the form and the code. So it is not very flexible. If you are confident that the choices will not change, or you are comfortable with doing form and code updates then this is an option.

There is also a listview activex control. This is a listbox with checks. I would steer away from this for many reasons, but if you wanted to try out of academic curiosity it can be used.

If it was me and if you can do some a little coding, I would use the following. This would look nice and solve the "real estate" issues.

Faking a Multi Value Field

I have seen a few posts lately on people wanting to use multi valued fields. Most Access developers steer away from them and build their own related tables. Although they are properly normalized and work well, they can get very confusing even if you know the ins and outs. I wanted to see if I...

www.access-programmers.co.uk

Finally you could use a real Multi Value Field. A lot of purist hate these, but it gives you an out of the box solution. There are few cases where I would use these, but this may be a viable place.

If you like the fake MVF I show and you can post your DB, I can help you with it. It only requires a few code changes to make it work on any form.

 

[Kayleigh]

Your fake multi value field is very cool! Would like to give it a try.

So an incident can have many staff related (reporting, reported to, witnessed by etc) which are all listed in incident log form. Then the incident can also be about multiple students - listed in incident details continuous form. So the categories required are linked to the incident log and stored in a junction table joining incidentID and categoryID.

 

[MajP]

Your fake multi value field is very cool! Would like to give it a try

If you need help then posting a sample db would be the easiest. I could demo something closer to what you have.

 

[Kayleigh]

Working with the first example you provided. You have added 'selected' as an additional field in the selection table. Is there any option of having the check boxes and the same method without adding this field. Since it seems to be changing the original category table and all I would like to do is apply those records to the junction table so each user is created several records corresponding to the selected categories.

One line of your code was not working correctly (also in your example) in the loadChecks function.
I would like the 'Add details' command to open the appropriate subform based on the checked categories (there will be other forms for other options) and some fields are not relevant to all categories so would like to hide these.
So I have started the code but cannot reference the subform elements. Can you assist with this please?

And I have finally posted (a component of) my database for your reference!

Ps. Can you sort by tomorrow - having meeting with my boss!

 

[MajP]

I will try to demo this with the fake Multi value field. This does not require the extra Selected field. I will try to demo soon.

 

[MajP]

I kept the subform for ease.

 

[Kayleigh]

Wow that is great! Really enjoyed looking at the code to display subforms - simple and concise!

Do you mind to explain the concatCategories function and why its necessary?

Also, why did you remove the onLoad event? Would it be better to use command buttons to display new/previous records?

Thanks again

 

[MajP]

I did not use the function. I would use it for the fake MVF. However see demo. It is a standard concatenation of child records.
I removed the onload code just for demo purposes. It would be confusing not to see the previous records that had different subforms showing, and the categories visible.

 

[Kayleigh]

Thats fab! I'm sure I can implement that function for other parts of my database as well...

Keep up your great work! Cheers

 

[Kayleigh]

When I copy this over to my main database, the loadChecks function is flagging an error when running. See images enclosed.

Any ideas how to resolve this?

 

[MajP]

That error always means there is an issue with the SQL string.
Before that line put a debug.print StrSql
Then look at the string. Often you will find no space between words, spelling error, misnamed table or field, etc.
I almost alway put a debug.print strSql when I develop before trying to run the string.

Since this worked in previous version it is likely a field name or table name is different from the database names. If you cannot see the problem then drop the string into the query development window. It should help point out the issue.
Another issue is where a field is a text not numeric
where someField = Text
should be
where somefield = 'Text'
or
someField = Numeric