Adding Multiple Values In A Memo Field

[cdacey]

Could anyone point me in the right direction on this. I would like to have the ability to select multiple values in a field that can string to one another. My problem is that by using drop down menus, I can only place one value in the field.

As example, I would like a memo-type field that would allow a physician to add several preset findings (contained in another table" in one paragraph.
If the base table contained record values; "eyes clear", "lungs clear", "good rythym", etc..

I would like the physician using the form to enter a memo-type field and have the ability to draw in all those values so that the resultant entry in the memo field was ; "Eyes clear, lungs clear, good rythym. "

I am guessing this is not easy, but if someone could point me in the right area to study, I could probably take it from there.

Thanks Chris

 

[Robert Dunstan]

One thing you could do is to create a list box that is based on your table containing these preset terms, make list box multi select and then you can loop through the selected item(s) and add them to your memo field.

Have a look at the attached...this should get you started.

Rob

 

[cdacey]

Still Stumped

Bob,
Itried that code in a command button, but it just replaces the value in the cell, and did not append to the values in the field. Any Thoughts

Is it possible to create a Macro that copies the highlighted value in the list box, goes to the memo field, and pasteappends the data ?

Thanks again
Chris

 

[bradcccs]

Just had a look at Rob's example.

Works quite well in my opinion. You may want to play with the actual display output. (ie: use of ';' etc).

You will find that the list box is set to "extended" for multi select, and you will therefore need to use the 'ctrl' key to multi select (as per the windows shell operations).

You may find it easier to set the list box to "simple" for the multiselect. (just click on each value you want - no need for ctrl or shift etc)

Another twist to Rob's example would be to use the 'On Click' event of the list box to trigger the code, that way the user would see the text box compiled as they clicked on each option.

I have re-worked Rob's example. All credit to Rob though, I think he nailed your problem.

Brad.

 

[Pat Hartman]

You need to do some reading on database design. Mushing multiple values into a single field violates first normal form. I presume that at some point you'll want to do something with the values that you stuffed into this memo field. Display them in the listbox for example or search for them or perform some kind of analysis. You'll be writing a ton of code when NONE was necessary if you had only followed the basic tenents of normal table design.

Look here for starters:

http://support.microsoft.com/support/kb/articles/Q288/9/49.ASP

http://support.microsoft.com/support/kb/articles/Q234/2/08.ASP

 

[Robert Dunstan]

Brad

Many thanks for your comments . However it's Mile-O-Phile who deserves the credit on this one as he gave me the code a couple of days when I was trying to solve a problem with looping through the selected items of a list box and appending them to a variable for use in sending email.



Chris

I agree with Pat's comments. If you are intending to use this field for searching etc. then the example does break the normal rules of table design. However if you are just using using the field to store the selected items and for no other purpose then it should be OK. My advice would be to think carfeully about what you want to achieve and then design your table(s) accordingly.

Good luck
Rob

 

[cdacey]

Robert,
Your code is working well when I choose all three items at the same time. But there may be instances when I need to choose two items, enter free text and add two more items from the list box. Is there another command other then strData that will pasteappend the data rather then overwrite whatever is in the text box. I am pretty good with access, but no very little VBA.

I understands Pats concerns, But this is meant for physician exam notes and it flows much better in one text box then having to set up multiple drop-down combo boxes to choose from, which is how I originally set the form up. I will never need to report on the individual items added to each text box, just the entire combined text, which becomes an individual record.

Thanks again
Chris

 

[Robert Dunstan]

Chris,

You mentioned the following:

Your code is working well when I choose all three items at the same time. But there may be instances when I need to choose two items, enter free text and add two more items from the list box

In the first instance, rather than have a combination of free text and selecting from the list box concatenating themselves into 1 text box, could you have 2 text boxes, one for the free text typing and the other for the list of preset findings?

If yes, then you keep the list of preset findings in a seperate table and link to the patients table (or equivalent) by say FindingID. That way you can then bind the listbox on your form to this table and if you need to make amendments / additions to the list then you just do it in the table.

This is just a thought at this stage, let me know what you think.

Rob

 

[cdacey]

Rob,
I may have to go with that approach if there is no way to append data to a text box. But what you gave me has been very helpful.
Thanks for your time !
Chris

 

[Robert Dunstan]

Hi Chris

Glad I could help

If your still having problems or need anymore help then keep on posting! Good luck

Rob

 

[cardgunner]

Rob,

This could help me as well. Although can you tell me how I would get the selected text to another form? Meaning if I create a new form and create a list box in that form what do I have to substitute in the code to make it go to a text box in the previous form?

 

[Ade F]

Sorry to butt in people

I wondered if anyone had and idea of how I would go about modding this code to overcome my problem here

http://www.access-programmers.co.uk/forums/showthread.php?t=98787

It's really being a pain now and I would love some expert advice.

Cheers

Ade