Edit listbox entries

[Kuleesha]

Hi,

I am trying to create a form where i have a list box from which I want to choose items which can be populated into a textbox bound to a field in a table.
1. I need to be able to enter multiple values into the textbox using the list box
2. I need to be able to remove an already entered item by reclicking on the list box entry
3. I need to be able to manually enter values which are not in the listbox into the same textbox and to edit values that have already been entered into the textbox

I tried various things without success. Is this possible with a list box? Is there a better way to achieve same result?

Thanks in advance

 

[theDBguy]

Hi. Should be possible. On #3, are you saying you want to enter and modify values in the Textbox without using the Textbox?

 

[Kuleesha]

I want to be able to edit the values that have already been entered into the text box (using the list box) by typing inside the textbox.
This is a bound text box so the edits have to be reflected back in the related table.
Thanks

 

[theDBguy]

I want to be able to edit the values that have already been entered into the text box (using the list box) by typing inside the textbox.
This is a bound text box so the edits have to be reflected back in the related table.
Thanks

Okay, just trying to understand. Why can't you do that already? How exactly are you populating the Textbox with the items selected from the Listbox? You should be able to then click into the Textbox and then type away, right?

 

[Kuleesha]

I have created a small sample database to indicate what I need to do (see attached)
I have a list box which uses values from a table (table InvestigationTemplate) in its row source. The control source of this list box in a field called InvestigationsOrdered in another table (table Patient). The text box I want to enter data into is also on the same form and has its control source also to same InvestigationsOrdered field.
When I have the multiselect property of the list box set to none, I can enter values into the textbox using the listbox and also edit these data / enter new data manually inside the text box. But this way I can only choose one value from the listbox. When I turn on multiselect property, I cannot enter data at all using the listbox but can manually enter data into textbox.
I need to be able to
1. enter multiple values into the textbox using the list box
2. remove an already entered item by reclicking on the list box entry
3. manually enter values which are not in the listbox directly into the textbox

Please help.

 

[theDBguy]

Hi. Not sure if this is what you want, but take a look.

 

[Kuleesha]

Thank you loads. Exactly what i was looking for.
One small thing - this creates a carriage line at the biggening of the entries also. Anyway to get around this ? My vba is pretty poor so can't workout how to get around this.

 

[theDBguy]

Thank you loads. Exactly what i was looking for.
One small thing - this creates a carriage line at the biggening of the entries also. Anyway to get around this ? My vba is pretty poor so can't workout how to get around this.

I think this one covers that now.

 

[moke123]

here's another alternative

 

[Kuleesha]

Yes these are perfect now.
Thank you so much.

 

[Pat Hartman]

Sorry, I couldn't stand it so I'm posting the properly normalized solution.

It is always wrong to store multiple attributes in a single field. That is why First Normal Form specifies that all attributes should be atomic. This poor practice may not come back to bite you for a while but eventually it will.

At some point, you will probably want to store details about the investigations. Using the proper schema will give you a place to put that related data and the subform will give you a path to a form that shows the details of an investigation.

 

[darrenislearning]

@theDBguy , I tried to follow your sample file and will have error when empty and it does not work like yours (e.g. move to the first line) when remove.

 

[theDBguy]

@theDBguy , I tried to follow your sample file and will have error when empty and it does not work like yours (e.g. move to the first line) when remove.

Give this one a try...

 

[darrenislearning]

Give this one a try...

Thanks, it works. If I want to expand the course list, which value should i change in the afterupdate? E.g. I got 10 courses.

Edit: I tried on new record, when i select the listbox item, it will be reflected at the 2nd item. (with a line break before value)

 

[theDBguy]

Thanks, it works. If I want to expand the course list, which value should i change in the afterupdate? E.g. I got 10 courses.

Edit: I tried on new record, when i select the listbox item, it will be reflected at the 2nd item. (with a line break before value)

Try this one...

 

[darrenislearning]

Try this one...

Thank You so much. Appreciate it

 

[theDBguy]

Thank You so much. Appreciate it

You're welcome. Good luck with your project.

 

[Pat Hartman]

theDBGuy loaded the bullets in your gun for you but in the long run, you would learn more and have a better foundation going forward if you did this the right way. Your name implies that you are interested in learning but maybe not.

 

[moke123]

As Pat points out, the correct way to do this is to use a junction table with each record recording the primary key of the student table and the primary key of the course table. You can then show the courses each student is enrolled in using a subform or you can use the multi-select listbox which will show the selected values from the junction table.
The attached example shows both ways to display the information. The records are added or deleted from the junction table in the after update event of the multi-select listbox.