Selecting multiple data and saving it in a field! 4

[broodis]

Hi All,

So far I'm able to select multiple things in a list box but I can't save it into a field in one row. (eg. machine1 has three problems so I select those 3 problems and its saved into Findings field in a row.)Any ideas??
Thanks

 

[fiep]

Create a variable and store the found values into the variable. After processing all selected items store the value of the variable into the field.

dim StrFound as string
For Each varItm In Me.SelectionList.ItemsSelected
StrFound = StrFound + " " + Me.SelectionList.ItemData(varItm)
Next varItm
me.FindingsField = strFound

Is this what you want to do?

 

[broodis]

I think so. I'll try it out first. By the way can you explain what varItm, Me.SelectionList.ItemsSelected and Me.SelectionList.ItemData(varItm) do. Sorry my programing sucks haha. Thanks heaps!

 

[broodis]

Do you mean I have to create a listbox and store this code into it??? Thanks.

 

[RoyVidar]

How would you later search/extract info on these problems per machine? Or do math on them (count problems per machine...)? Stuffing more than one value into one field breaks the rules of normalization, and is the cause of headaches and lack of social life;-)

The way I read this challenge, I think a better approach might be along the lines of the following

tblMachine
MID
...

tblProblemMachine
MID
PID

tblProblem
PID
...

i e - implementing what seems to be a M:N relationship between machine and problem through a junction table.

To add/edit, use a subform where you add a new problem through a subform based on tblProblemMachine, where you select the problem from a combo.

Roy-Vidar

 

[broodis]

To RoyVidar,

Using your way I have to key in the problems one by one and that will be very inconvenient for the user. (Eg. Machine1 - part A not working
Machine1 - part B stuck
Machine1 - part C not working

What I want to do now is combine all this problems together.

eg. Machine1 - partA not working
- partB stuck
- partC not working

Is there a simpler way to do this without coding?

 

[GingerR]

Roy's right. Some day someone will say "Hey, what's our most problematic machine?" and then you'll post a question on here on how to figure that out.

It is not more inconvenient for the user. Seems like they'd be using the same amount of effort to pick three things from a list box vs. three things from combo boxes. Plus you get the added benefit of having your db structured properly.

If you are still intent on keeping the list box method, check HELP or this forum. When you set it to multi-select = SIMPLE or EXTENDED, it's handled differently than a text box, combo box, or a list box that has Multi-Select = NONE. You can see why--there are multiple items selected, and you have to loop thru them and put them some where. That's what fiep's code is doing.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[broodis]

Thanks RoyVidar and GingerR,

I think I'll stick to my initial plan. But can anybody explain to me fiep's code, like where they are all linked to. Especially

1.varItm
2.Me.SelectionList.ItemsSelected
3.Me.SelectionList.ItemData(varItm)

 

[PHV]

When in VBE (Alt-F11) search the leftmost pane of the Object browser (F2) for ListBox, click it, search the right pane for ItemsSelected, click it and press F1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[broodis]

Does anybody know how a variant works. Because I think thats what varItm is.

 

[broodis]

Hey Fiep,

Can you explain to me how your code works. Thanks.

 

[PHV]

When in VBE, click inside the debug window (Ctrl+G), type variant and press F1.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[broodis]

Now it keeps on saying "Type Mismatch". I thought it was because I referenced the findings as follows

RefFind Findings
1 part A stuck
2 part B not working (etc)

and it only reads the RefFind. So I changed StrFound to an Integer. But its not working. Any ideas?

Dim StrFound As Integer
For Each varItm In Me.List61.ItemsSelected
StrFound = StrFound + " " + Me.List61.ItemData(varItm)
Next varItm
Me.RefFind = StrFound

 

[GingerR]

strFound will be a string, not an integer. It will be something like

1 6 8

where 1,6, and 8 are the RefFind items from your list box.

What is the name of your text box where you want the result? You have the result going to me.RefFind, but I thought (orig post) your result was going into a text box called "Findings"?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[broodis]

Oh that was what Finding was. I thought it was a field where I saved the data in. But after I changed StrFound to String it says

"The value you entered isn't valid for this field"

Hmm is there any way I can share my Access file with you all? So that everybody can see it???

 

[GingerR]

Oh that was what Finding was. I thought it was a field where I saved the data in.

?? You said in your first post that the field you wanted to save the result in was called "Findings". Now you seem surprised? This is very confusing.

Honestly, this has gotten way too difficult. You have the correct code for dealing with a list box in the manner you asked for. I suggest you continue to fiddle with it until you figure it out. Perhaps you are trying to save the result into a text box/field which is not the correct field type?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[broodis]

Is there an example for this type of problem? Sorry I confused all of you.

 

[broodis]

Okay I think I managed to fix the problem. But now its not saving it into the field Findings. Do I need to make an update command button to update it?? And if so, how do I do it??

 

[GingerR]

where is it saving it to?
please post your code.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[broodis]

It's okay guys. I got it working already. Thanks fiep, GingerR,PHV and RoyVidar for all your advice. It really helped me.