Solved Form with multiple list boxes

[zelarra821]

Hello people. I ask for your help.

I am creating a database to make clinical records for patients (dogs and cats) with behavioral problems.

I am going to try to explain with examples and everything as much as I can and can imagine right now. So, if something is not clear, or if I overlook any relevant information, do not hesitate to tell me.

In the clinical record, for example, I have the following page:



In the case, for example, of the necklace, right now there are six options, but I would like to achieve two things:

- On the one hand, the user can add more options tomorrow without having to resort to me to do so.
- Be able to select more than one option.

To do this, I can choose checkboxes, but it fails that the user cannot enter more options. If I put a combo, I can put more options, but I can only choose one option. As it is, I have to choose a list box.

Like that page, I have four or five more, with many more questions that I would like to achieve the same thing.

Therefore, I would like someone to suggest a simple method to create multiple list boxes in the same form without dying trying to create so many. majP showed me one, but it is too complex for what I need.

Thank you so much.

 

[CJ_London]

not clear whether these are 'multiple answer' questions or a single choice. Using your necklace example, can users select both conventional and electric? Appreciate you say users can only choose one option but from your example, they all seem mutually exclusive.

As an alternative to a multi select listbox, you do have the multi value combo option, although I don't recommend it - better to 'grow' you own' as you have more control

And if users can add more options - what is the impact of doing that? just another item? or are there implications for calculations/summaries, etc

 

[zelarra821]

The example has not been very successful. I give you the case of the barking, which does not appear in the image. For that I have two or three options, but another reason may be repeated in the query that needs to be recorded. The reason for having the possibility of adding is to categorize and be able to carry out clinical studies with the data obtained in each clinical file, because whoever is going to use the database will then present it at scientific conferences. If the user cannot add new ones, or you put a text box, it is very difficult to draw conclusions because the same reason for the barking, to follow the example, can be reflected in several different ways. The impact is not that it is more calculations, not at all, but that I am looking for two things: that they can add more options to the questions that may arise and, as I said before, that it is categorized to obtain clinical conclusions from the data, of so that the reports are updated reflecting these new options, without having to constantly modify them. If I only leave three options closed, tomorrow I may need to add another option and they will have to call me, and it is a hassle for the user and for me, because I don't know what will become of me then.

 

[ebs17]

Your picture is poor in information. It's probably common practice to send a picture of the dog to the doctor and they'll make a suitable diagnosis...?

I am creating a database

Then you don't start with any form stuff, but with a well thought-out database schema, and that's exactly what you would present first. Forms and codes result from this.

Flat rate: For an intended multiple selection and for the user to expand the options (of course), you should deal with many to many relationships.

 

[CJ_London]

OK - so you need to decide how you are going to handle the situation where one question can only have one answer whilst another can have multiple answers - and maybe that has some restrictions as well (e.g. some answers are mutually exclusive)

Agree with Eberhard, you need to get your table design/relationships sorted out first, probably based on the the nature of the reporting requirements. Forms and reports come later

 

[mike60smart]

The example has not been very successful. I give you the case of the barking, which does not appear in the image. For that I have two or three options, but another reason may be repeated in the query that needs to be recorded. The reason for having the possibility of adding is to categorize and be able to carry out clinical studies with the data obtained in each clinical file, because whoever is going to use the database will then present it at scientific conferences. If the user cannot add new ones, or you put a text box, it is very difficult to draw conclusions because the same reason for the barking, to follow the example, can be reflected in several different ways. The impact is not that it is more calculations, not at all, but that I am looking for two things: that they can add more options to the questions that may arise and, as I said before, that it is categorized to obtain clinical conclusions from the data, of so that the reports are updated reflecting these new options, without having to constantly modify them. If I only leave three options closed, tomorrow I may need to add another option and they will have to call me, and it is a hassle for the user and for me, because I don't know what will become of me then.

Can you upload a zipped copy of the database with no Confidential Data?

 

[zelarra821]

The structure is clear to me: a table with the main data of the clinical record and annexed tables with the options of the fields that require it.

This is the relationship of fields that I have created in the main table. I also add if I have created an attached table (generally, if it says Id it refers to an attached table).

IDCanineClinicFile
ClientId --> Customer table
AnimalId --> Animals table
Date
Refers
Opinion
Study
Guidelines
Veterinary Report
Education
Analytics
Video
Official report
Diary
IDVeterinary --> Veterinary table
ReasonConsultation
Other problems
HousingID --> Housing table (here there are two options: house and apartment, but you may want to add more)
Square MetersHousing
PlaceId --> Places table (places where the animal is, type balcony, terrace...)
Square MetersPlace
InteriorAccessId --> Interior access table
Where Does He Stay When He Is Alone
Clinic history
Relatives
Unknown people
Children
GroupDogs
DogsMaleUnknown
DogsFemaleUnknown
TEPFharmacological
PTSDTherapyModificationBehavior
TEPFeromone
PTSDTherapiesAlternatives
HMProblemsDoctors
HMMedication
HMExternal Parasites

RelativesAggression --> I would like to put this in a Social Behavior table, so that he can add more options if he sees fit, since this file is the Autonomous University of Barcelona, and he also wants to personalize it to his liking
RelativesFear
RelativesHyperattachment
RelativesAttention

Unknown PeopleAggression
Unknown PeopleFear
Unknown PeopleAttention
ChildrenAggression
ChildrenFear
ChildrenAttention
DogsOfTheGroupAggression
DogsGroupFear
GroupDogsHyperattachment
DogsOfTheGroupAttention
DogsMaleStrangersAggression
DogsMaleStrangersFear
DogsMaleStrangersAttention
DogsFemaleStrangersAggression
DogsFemaleStrangersFear
DogsFemaleStrangersAttention
TEPFharmacologicalYesNo
PTSDTherapyModificationBehaviorYesNo
TEPFeromoneYesNo
PTSDTherapiesAlternativesYesNo
Fixed Strap
MailExtensible
NecklaceConventional
NecklacePuas
NecklaceDrown
NecklaceHalter
CollarHarness
Electric Necklace
Dry Diet
DietWet
Ingredients
Brand
AdministrationAdLibitum
Rationed Administration
Awards

And many more that I haven't included yet.

I just wanted to ask the way to create a multiple list box.

 

[MajP]

To do this, I can choose checkboxes, but it fails that the user cannot enter more options. If I put a combo, I can put more options, but I can only choose one option. As it is, I have to choose a list box

See if this is of interest. A multiselect combobox using normalized data.

Solved - Unbound Combo box need multiple value

Hi, I have a searcher form. It is worked. But now like to put multiple value to search. CODE : Private Sub cmdFilter_Click() Dim strWhere As String Dim lngLen As Long If Not IsNull(Me.fltCompanyName) Then strWhere = strWhere & "([CompanyName] = """ &...

www.access-programmers.co.uk

 

[zelarra821]

Can you upload a zipped copy of the database with no Confidential Data?

Yes of course. There is no confidential data, only invented data to test.

The problem is that it is in Spanish.

 

[zelarra821]

See if this is of interest. A multiselect combobox using normalized data.

Thanks, this afternoon I saw that database of examples, which I had saved, but it didn't work for me and searching here in the forum I couldn't find it.

I do not know how it works. For example, it does not save the selection to any table. Therefore, how do I know what data you have chosen in a specific file? I don't know how to apply it to my case, sorry.

 

[mike60smart]

Yes of course. There is no confidential data, only invented data to test.

The problem is that it is in Spanish.

Hi
You need to close the database and then upload a copy.

 

[zelarra821]

I have upload a copy. I have deleted the tables that we don't need now.

 

[MajP]

I do not know how it works. For example, it does not save the selection to any table. Therefore, how do I know what data you have chosen in a specific file? I don't know how to apply it to my case, sorry

This example is using unbound comboboxes. This concept could be done with a bound field. Here is an example.

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

To do this with multiple fields, it may work if the tables are very standardized for all questions. If not it will be hard to make update queries that are generic.

 

[mike60smart]

I have upload a copy. I have deleted the tables that we don't need now.

Hi

In the modified database attached I created the Form that opens at startup

The Main Form is based on Clients
The Subform is based on ClientsAnimals
The additional Subform allows you to add Multiple Collars for the Animal Selected.

 

[zelarra821]

Thanks to both.

I think I'm going to stick with Mike's option. Although in essence it is similar to what majP proposed to me with the list boxes, it uses a subform and I don't need any code, just the table that connects the clinical record with the options table.

Good night.

 

[MajP]

You may consider some form of an Entity Attribute Value model

record value in table when one of many buttons are clicked

That's exactly what I did with my tblParts, tblFinalProducts, and tblAssemblyComponents. Exactly that.....and it works great. I get that. Thank you for the effort of showing me a "map." Works well for me. If that is what we're all talking about then it may be conceivable to incorporate that...

www.access-programmers.co.uk

Somewhere I have a mock up of one.
You have lots of Characteristics and you would want to make it tailorable. This is one technique that works well. It simplifies a lot of things, but makes other simple things more difficult. You need to weigh the pros and cons. This will make managing your tables much easier.

 

[MajP]

Here is a EAV demo. It is pretty basic, but gives the idea. Imagine if you had 100s of characteristics. Also in your case you would probably push all the characteristics instead of having the user pull them down.

 

[zelarra821]

Ok, thanks. I will check it out!

 

[Pat Hartman]

I know you marked this as solved but you might want to consider this sample as a solution. I think you are talking about an application where the data will be entered on forms even though your original post showed a report. If that is the case, then the following mini-app can be used to solve the problem of combo box maintenance. This sample uses two tables. A table of tables and a child table with the table contents. So if you have 30 combos that have lists that you want to allow the user to manage, then the table of tables would contain 30 rows. The child table would contain the details for each "table". You will have to change the data type for all your combo box fields to be long integer. That is the price for having a consolidated solution. You can add tables (the users can't add tables. Only the developer can add tables) But the user can add items and change their text. You should prevent deletes because unless you add code to the application so that the table management form "knows" where a particular table is used and can run a dCount() to find out if "necklace" has been used on any record, allowing deletes is too dangerous.

To use this you would copy the items from the sample to your application. Change them to have the look of your app so they don't stand out. There are directions in the app.

Table Maintenance Mini-app

When you have several lookup tables in an application, it becomes a pain to create separate tables and maintenance forms to manage the contents. This sample is an application that I first developed over 40 years ago, long before Access was a twinkle in Bill's eye. It was constructed using...

www.access-programmers.co.uk

 

[zelarra821]

Ok. Thanks