How to create multi value select in access form and add values in database table (1 Viewer)

[afia]

I have a design a form in which i have below fields:
First name, Surname, Dob, Age, Gender, Referral reason, Work Pattern, Priority , Treatment location, Initial Appointment date, Discharge date, intervention, Nature of Injury.

For "Nature of Injury", user can select multiple values for example, hand, elbow, foot, back ,neck etc.

How shall i add this in access form and how shall it can be recorded in table

Thank you

 

[Gasman]

I would have a table that would record each type of injury, linked to the patient and possibly date of reporting.

 

[afia]

Thank you. How shall i add on form? in the form of list?

 

[GaP42]

Generally a nature of injury field would be free text, given that there is so much that can be described in that context ( body location, severity, ...), however you may apply a classification - for example based on applicable SNOMED items (if this scheme is available to you).
I would advocate you create a reference table for the NatureofInjuryClassification which is used to assign one or more NatureOfInjury to the Patient (date stamped?) as a specific table, separate from the patient table. This is to resolve the many-many patient to nature of injury classification relation.

 

[afia]

Generally a nature of injury field would be free text, given that there is so much that can be described in that context ( body location, severity, ...), however you may apply a classification - for example based on applicable SNOMED items (if this scheme is available to you).
I would advocate you create a reference table for the NatureofInjuryClassification which is used to assign one or more NatureOfInjury to the Patient (date stamped?) as a specific table, separate from the patient table. This is to resolve the many-many patient to nature of injury classification relation.

and what do you recommend how shall i add this NAture of Injury field on the form?

 

[Gasman]

I would be using a subform.

 

[GaP42]

As @Gasman indicates, the use of a subform which allows selection of a Nature of Injury Classification value (using a drop down list box / search) to populate the NatureofInjury table associated to the patient record from the main form would be appropriate.

 

[MajP]

You can use a child table or you can use a multi value field. Most people discourage the MVF because they can cause some problems/limitations if you do not know how they work. In this case where you are only storing a short text string an MVF may work fine.
With the child table you would have something like

First you need a table that holds all the choices
tblNatureOfInjury
-- NatureOfInjuryID ' autonumber PK
-- InjuryDescription
-- InjuryGroup (optional field but I assume you want to group type of injuries)

Next you need a Many to Many junction table
tblPatient_Injury
-- PatientID_FK ' holds a foreign key to the patient
-- InjuryID_FK ' holds a foreign key to tblNaturnofInjury

If patient A, has injuries 1,5,7

A	1
A	5
A	7

The Main form is Patient and Subform is bound to the tblPatient_Injury and linked by PatientID to PatientID_FK
In the subform you have a combo box on InjuryID_FK that shows the description by stores the InjuryID.

 

[MajP]

The MVF or subform are the easiest because they require no code. The MVF is the easiest since it creates the child table behind the scenes. However read the pros and cons on MVF. I do not have heartburn using them since I fully understand how they work, but they can get you into trouble.

I do like to make actual checklists for the UI, but that requires code. There are advantages to a checklist over a subform since you can show all the choices and only select the item once. In the subform you do have to write code so you do not duplicate the injury.
If interested in a checklist approach here are some examples .

Making a custom checklist that saves to a table

Good evening all! I have a need to build a checklist form. We have 19 steps that our leads have to do every engagement. Items 1-4 are pre-engagement, 5- 8 are mid-engagement, and 9- 19 are post-engagememt. I need a checklist form that saves checked items for the next time the DB is opened and I...

www.access-programmers.co.uk

Solved - Three-part form

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...

www.access-programmers.co.uk

and also a fake MVF without any downfalls

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

 

[GaP42]

I have a design a form in which i have below fields:
First name, Surname, Dob, Age, Gender, Referral reason, Work Pattern, Priority , Treatment location, Initial Appointment date, Discharge date, intervention, Nature of Injury.

For "Nature of Injury", user can select multiple values for example, hand, elbow, foot, back ,neck etc.

How shall i add this in access form and how shall it can be recorded in table

Thank you

You may consider some restructuring of your data: You have basic patient data (DoB, Gender - age is calculated and should not be stored). You then have data that varies for that patient depending upon the interaction: Referral Reason - multiple referrals may occur over time - suggesting a separate table for the Patient Referral, which may then involve multiple appointments - if you track these. The nature of Injury is like an observation of the patient associated with the referral. So the relationship involving Nature of Injury related tables would be Referral - Patient Nature of Injury - Nature of Injury Classification. (not directly to Patient)

There are likely more fields that may need to be stored in the Patient-NatureOfInjury table besides the foreign keys to the Referral and NatureofInjuryClassification - eg if the date of the injury is important, severity of the injury, notes about the specific injury

 

[Josef P.]

An example file with several variants:

access-examples/forms/MuliValueSelection.zip at main · josef-poetzl/access-examples

Contribute to josef-poetzl/access-examples development by creating an account on GitHub.

github.com

• Continous form with ComboBox (This is probably the most commonly used variant.)
• Continous with CheckBox (all items visible)
• ListBox with property Multi Select
• Listbox bound to a MultiValue data field (in temp table, I do not use multi-value data fields in real tables.)

 

[afia]

You may consider some restructuring of your data: You have basic patient data (DoB, Gender - age is calculated and should not be stored). You then have data that varies for that patient depending upon the interaction: Referral Reason - multiple referrals may occur over time - suggesting a separate table for the Patient Referral, which may then involve multiple appointments - if you track these. The nature of Injury is like an observation of the patient associated with the referral. So the relationship involving Nature of Injury related tables would be Referral - Patient Nature of Injury - Nature of Injury Classification. (not directly to Patient)

There are likely more fields that may need to be stored in the Patient-NatureOfInjury table besides the foreign keys to the Referral and NatureofInjuryClassification - eg if the date of the injury is important, severity of the injury, notes about the specific injury

Thats right we can receive multiple referral for one patient in a year. I don't need to keep a record of all appointment, it would be just one column of no. of appts.

I have tried to create a subform but comes up with an error when i try to add multiple values as shown below:

Below is the relationship diagram:

Not sure what i am doing wrong. Also in the subform its showing ID and not Injury description. Even if i add the description, its not giving me drop down to select value like shoulder etc.

Nature of Injury is a a Main category . It has a sub category like for example if i select Shoulder and Hip , i think need to select sub categories of these two as well e.g. Fracture or dislocation

Shoulder subcategories as below:

Adhesive capsulitis (frozen shoulder)

Rotator cuff disorders

tendinopathies

bursitis

sub-acromial pain

impingement

Thank you

 

[Gasman]

The message is quite obvious.
You are attempting to create duplicate data, yet have set the table not to have duplicates.

 

[afia]

The duplicate value issue is resolved. Only thing left is in subform is that its not showing up injury description. Even if i add the column it only lets me select the options from drop down InjuryID_FK and not from description

 

[MajP]

The trick to show description the description but save the PK is to
1. Write a rowsource that includes the PK and the description. Like
Select InjuryPK, InjuryDescription from tblInjury order by InjuryDescription

2. In the combo box properties
Number of Columns: 2
Bound column: 1 (the PK column)
Widths: 0";2" (where 2 is some width)

since your combo has 2 columns it shows ID, Description. But since Width of the first column is 0 you only see the description. The first column is ID and it is bound to the field.

 

[GaP42]

Hopefully the relationship diagram is a simplification of what you actually have - specifically "Table1" - as it has some serious structural issues as mentioned in #10.
@MajP has provided advice on getting the combo box to work correctly - as a simple 2 item list of InjuryPK and InjuryDescription
However you have also mentioned the need for Categories for the Nature of Injury, which might be used to filter the combo box list to display the specific Nature of Injury descriptions found in that category? (Combos generally should not display long lists of options, or allow a find-as-you-type filtering option: MajP has published some code for this: search for FAYT on this site)
An approach may be to add the Category field to tblNatureOfinjury, and record the combinations of injuryDescription and injuryCategory.
Then use a new Category combo control to filter the available options in the NatureofInjury combo, or modify the NatureofInjury combo to include 3 columns, by adding Category, sorted by Category then Injury description.
BTW your subform is a datasheet. A continuous form would be best to develop the combo controls (the combo/s can be in the footer to allow change to the selected record)

 

[MajP]

An approach may be to add the Category field to tblNatureOfinjury, and record the combinations of injuryDescription and injuryCategory.
Then use a new Category combo control to filter the available options in the NatureofInjury combo

If you went with Cascading combos to filter the Nature of Injury by an Injury Group it is not trivial to do it in a continuous form. Not super hard but does take some tricks. Here is an example where they select a group from the first combo and that limits the selections of the second combo to items in the group.

Cascade combobox on continuous form

Hi all, I would like add a cascade combo box to my subfom, but it's in continuous form and I have always error about parameter. It works on second combobox ("ValoriCarrieraIDcmb"), only if I put manually the number of ElencoCarrieraID, for example "8". How can I filter it? Thank you very much

www.access-programmers.co.uk

There are plenty of other examples, but all require some code. This is easier to do in a continuous form vice datasheet.
Not the easiest example since the database is in Italian (if I remember)

 

[afia]

I have tried everything but still unable to successfully create synchronized subcategory. If i select a category, it does show subcategories but all of them and not the specific ones. I have attached access db for reference if can help please

 

[Gasman]

Sorry, you have to explain what sets what, not just chuck up a form in a DB
What is meant to filter what?

 

[Gasman]

Now crossposted at https://www.accessforums.net/showthread.php?t=89754