Need to separate data in table taken from mult select list box 1

[miaka13]

I am extremely new at VBA coding. I am trying to put together a user friendly database in Access 2002 for my coworkers. I managed to get a code from this forum to work in my database. What I currently have my form doing is to take the information selected in my multi select list box and place it in a table. Unfortunately it is taking all of my selected data and placing it in one field on my table. I would like to put each selected item into a seperate field in my table. Can anyone help me? Please go easy on little old beginner me.

 

[GingerR]

please paste your code here so we can see it and help you out )

 

[miaka13]

Here is the code that I used:


Private Sub Submit_Click()

Dim strVal As String
Dim varItem As Variant
Dim ctl As Control
Dim rst As DAO.Recordset

Set ctl = Me.List0

For Each varItem In ctl.ItemsSelected
strVal = strVal & ctl.ItemData(varItem) & ","

Next
strVal = Mid(strVal, 1, Len(strVal) - 1)

Set rst = CurrentDb.OpenRecordset("mytable&quot
rst.AddNew
rst!FavoriteColor = strVal
rst.Update

Set rst = Nothing
Set ctl = Nothing


End Sub

 

[GingerR]

you are making one long string, separated by commas, and plunk it into one field.

the code below is what i use. it's similar to yours, but puts each item from the list box into a separate field. you'll have to change your table field names from Model, MinEFF and MaxEFF to whatever your field names are. to the right of the "=" signs, is where you pick them out of the list box. column 0 is the first item in the list box; 1 is the second (to the right in the same row), and so on:

Dim varRow As Variant
Dim rst As DAO.Recordset
Set rst = CurrentDb.OpenRecordset("PARAnalyze-SelectedEFF", dbOpenDynaset)

For Each varRow In Me!List0.ItemsSelected
                   rst.AddNew
                   rst!Model = Me!List0.Column(0, varRow)
                   rst!MinEFF = Me!List0.Column(1, varRow)
                   rst!MaxEFF = Me!List0.Column(2, varRow)
                   rst.Update
Next varRow
Set rst = Nothing

 

[miaka13]

I'm pretty new at VBA coding and I don't understand the purpose of the following line:

Set rst = CurrentDb.OpenRecordset("PARAnalyze-SelectedEFF", dbOpenDynaset)

Could you clarify for me?

 

[Rolliee]

miaka,

This opens a recordset from a table or query named:

"PARAnalyze-SelectedEFF"

That is where Ginger is storing her selected items.


Rollie E

 

[GingerR]

it's the same as yours:

Set rst = CurrentDb.OpenRecordset("mytable")

i just have a different table. yours is called 'mytable'.

 

[miaka13]

Thank you so much Everything is going to it's own field now.

I do have another question though. I have a bad feeling that the coding for this will be difficult. What I am wanting to do as well is for each selected item in the multi select box that goes into the table, I would like the text from several other text boxes to correspond with the field in the table. For example, Jane enters her name on the form along with the job that she needs to do. This job requires 2 colors. I would like for each color to be on a seperate line and for the other text boxes on the form to go along with the selected items from the multi select list box. For example:

Color Person Job

blue jane deck​

red jane deck​

 

[GingerR]

do you mean that you want Jane and Deck to go into your new table, along with the items selected from the list box?

you can reference the items on the form using Me!NameField or Me!JobName or whatever you are calling the text boxes with the info you want. the ME keyword means "the object (form, report, etc) you are looking at."

so say in your new table, you have fields Person and JOB.

then add into the code above:
rst!Person = Me!NameField
rst!JOB = Me!JobName

put this along with the other code, inbetween rst.AddNew and rst.Update. Tweak as necessary.
so this is saying "Put the contents of the field NameField on the current form into the field PERSON in my new table......etc....."

 

[miaka13]

THANK YOU SO MUCH! Everything works the way that I wanted. Now I can go and successfully put together my database. Thank you again for all the help. Enjoy your star.

 

[miaka13]

Turns out that I have yet another question. What do I do if someone wants to go back and change their choices? I know that I need to use a refresh code on my form for when I want a new record but can you use refresh to over write an old record?

 

[GingerR]

hmm....maybe there's an entirely different way to set this up. are you familiar with SUBFORMS?

say you have a main table: tblJobs
fields: JobID, JobName, Person, JobDesc, etc

and another tblcolors
fields: ColorID, Color, etc

then a third table, which relates the two:
tblJobColor
fields: JobID, ColorID

then you have a main form, with recordsource tblJobs.
you have a second form, based on tblJobColor. within this form, the field for COLOR is a combo box, with selections based on tblColor to make sure people choose only the colors you want them to.

you embed this second form (frmJobColor) inside of the main form, which then makes it a SUBFORM. when you make the Master/Child link be JobID, then whatever you enter into the subform gets automatically related to the JobID of the main record you're looking at. you can delete and edit any records in the SUBFORM.

maybe this is more what you're looking for?

 

[miaka13]

The form/subform idea sounds good, but with a combo box you can only choose one color. Is it possible to keep the list box idea and combine it with the form/subform?

 

[GingerR]

but you would select one color per row.

your tblJobColor would have data like this:

JobID Color
122 Blue
122 Red
122 Green
123 Blue
134 Green
134 Black

see? the subform will look like a datasheet (like an excel spreadsheet) with just one column, embedded in the main form. the person picks i.e. RED. say they also want blue. as soon as they pick RED, a blank record is sitting just below that waiting for them to make another selection. so they pick BLUE. now there are two rows showing in what looks like a datasheet, embedded in the middle of the main form. say now they want to change BLUE to BLACK. they simply click on the combo box where BLUE is chosen, and choose BLACK instead. now say they want to delete RED. they simply choose the record selector (the grey square) to the left of the RED, and hit DELETE on their keyboard. done.

the way you have it, you'd have to write some fancy code.

or, create a subform from your existing table that has the colors/job numbers in it, and make that a subform within your main form. people can DELETE from that form, but to add they'd continue using your list box method. it seems a little hack-jobbish tho, when they could just choose a color right there in the 'new' subform...they'd be adding using one method, and deleting using another. what about editing (changing BLUE to BLACK)?

anyhow, just a suggestion...........

 

[miaka13]

Uh oh! I'm doing something wrong. When I set up the subform. I am not getting a second row in my combo box. I have the drop down menu, but unfortunately a blank row does not appear. Could you point me in the right direction?

 

[GingerR]

are your subform DEFAULT VIEW and VIEWS ALLOWED properties set to DATASHEET?

 

[miaka13]

The DEFAULT VIEW is set to DATASHEET. I can't find the VIEWS ALLOWED portion though.

 

[GingerR]

it's right under DEFAULT VIEW
if you want you can compact/zip and send to me
rowe147@hotmail.com
put your tek-tips screen name in the email so i know who it's from

g

 

[miaka13]

Guess what?! I figured out what I did wrong. Now everthing works just fine. Thank you again for everything!

 

[GingerR]

what was it? it could help someone else.