Multiselect List Box 1

[Zorro1265]

I have a database that currently uses multiple combo boxes with value lists attached to each box. The items from the value lists are used to generate a descriptive report from predetermined phrases. One problem is I get one choice per combo box. Another problem is my tables are storing the entire phrase from these combo boxes and I can foresee this becoming a problem since the files are getting large. Each report can have 20 to 30 phrases each.

I have found the code on microsofts page to create a multiselect list box and have played with it and I like the interface. It allows 1 list to have lots of phrases and pick several from the list. These phrases are then concantonated (did I spell that right?)into a paragraph. This also has the problem of storing alot of text into the database. The selections for the list box are lookup tables. What I would like to do is only store the reference to the lookup table and then use that to create the paragraph when its needed. The other sticking point is if the text that makes the paragraph is edited to something not in the list it needs to be saved somewhere.

Is this making any sense or am I nuts? I would be happy to send a sampls MDB to anyone willing to help. This has been making me crazy for several months, I have read a couple thick books and cant get any ideas. Please help ( insert pitiful whimper sound effect)

 

[kathryn]

OK, if I understand you correctly, you can make one change that will save you a ton of space. Use a ID with the phrases. Then instead of storing the phrase you store the ID.

If you change the text of a phrase, then you need to create a new phrase with a new ID and, using a flag or something, make the old phrase no longer accessible.

Could you explain what the purpose of the app is? This may help with ideas.

Kathryn

 

[Zorro1265]

This is part of a reporting system for cardiac ultrasounds I have developed. My combobox version has worked great for about 5000 tests but I think its time to refine things. Ideally the phrase part of the system would store things in one table so it would make it easy to search. I have all the other parts figured out and I can go with the multiselect box but I know I wont be happy with it. I assign a unique value to each exam and all the phrases selected are linked to that exam number.

This is a linkfor the code to the box I got off of microsoft.

http://support.microsoft.com/suppor...Control)&rnk=2&src=DHCS_MSPSS_gn_SRCH&SPR=ACC

I wish I knew what it all means (haha) but it works just, not what I want. This code is great because when you deselect something its removed from the concantonated paragraph. Any ideas?

 

[kathryn]

OK, if I get you correctly, each exam can have multiple phrases associated with it, and each phrase can be associated with multiple exams. Therefore you have a many-to-many relationship.

You need 3 tables

tblExam
-------
ExamID (Autonumber PK)
ExamDate
etc.....


tblPhrase
---------
PhraseID (Autonumber PK)
Phrase


tblExamPhrases
--------------
ExamPhrasesID (Autonumber PK)
ExamID (Foreign Key to tblExam)
PhraseID (Foreign Key to tblPhrase)

The data for each exams phrases is stored in the tblExamPhrases, but since you are only storing keys, the size is not an issue.

What do you think.

Kathryn

 

[Zorro1265]

Well thats about it, but I have several other tables in the database. The phrases are only part of the interpretation. I use the phrase generation as a subform in the main form for interpretations.

1 table
tblPatientEncounter
holds unique identifier for each test (PEN) plus other stuff

2nd table
tblkupCategory
category for different types of phrases

3rd table
tblkupPhrase
all the phrases with category from table2, a column for sort order within each phrase category and a few other columns for other uses later

4th table
tblInterpDetail
holds all the selected data. If its a premade phrase it will just look things up if a non-standard phrase it should hold whatever they type in.

Like I said this is just part of the whole database, I store measurements and patient demographics and some other data as well. I would like to have a form that has a list (datasheet view) on the one side of the subform of all the possible categories (table2) and another spot for a view of the combined phrases in paragraph form. When someone clicks a category a subform pops opens with the list of phrases for that category. They make their selections and it is stored, then displayed inthe combined paragraph I spoke of. If they type something non-standard in the paragraph it should store that in the table as well. It would be nice if they can just freetype in that paragraph and add a word to one of the predeterming phrases and then that modified sentence would go into the table. Does this sound possible with this list box format? I have a version that works like this but only with combo boxes in the popup, I think its better to see the list and see what you have to click on and can click off something later if you like. Thanks a bunch for helping with this!!! I am at a loss. I know this takes far more skill than what I have. Do you think this should be a module like the microsoft sample?

 

[Zorro1265]

Anyone else have any ideas? If someone has figure out how to make a list box store each selection into a table please point me in the right direction.

 

[Zorro1265]

Kathryn
Have you given up on me?