Full Version: how to put multi selections from list to table
UtterAccess Forums > Microsoft® Access > Access Forms
I have a listbox that shows some values from a query. I have changed the preferences of the listbox so that i can select multiple values, but it doesent store any value in the designated row in the table.
If i disable the multiselection preference on the listbox i can store one value in the row.
That I want isfor example.
1. Select values from my listbox.
value 1
value 2
value 3
2. Store them in a row in the table so it looks like this.
value 1, value 3
The row in the table is an ordinary text row.
The way I have done it through vba code. You need to add a record using the INSERT method of SQL.
Here is some code to add the first column of your listbox to a table named Test, into the field named Field1

Private Sub cmdAddRecord_Click()
dim x as integer
dim strsql as string
for x = 0 to nameofyourlistbox.listcount
      if nameofyourlistbox.selected(x) = true then
         strsql = "INSERT INTO Test (Field1) Values ('" & nameofyourlistbox.column(0, x) & "')"
          ' this string is the sql method of adding a record, it is adding the value of the selected row in column 0 into a        
          ' table called Test, into the field called Field1. This assumes that the value in column 0 is text. If it is not
          ' then get rid of the quotes that will go around the value.
        currentdb.execute strsql, dbfailonerror
         ' this command executes the sql statement, and if pieced together correctly will insert the record into the
         ' Test table
       end if
next x
end sub

Look through this, hopefully this will help you. If you need anymore assistance don't hesitate to shout. There is another method of actually figuring out what records are selected. Perhaps someone can assist with that, but the only way I can figure it, is you need to use the INSERT INTO method for adding a record.
Ok, thanks, i will try it and if i get stuck i will give u a note.
I have disabled the normal addreccord function and made a button for it so the older coworkers can see it wink.gif
So the code lokes like this :
Private Sub laggtillpump_Click()
Dim x As Integer
Dim strsql As String
For x = 0 To Listruta15.ListCount
If Listruta15.Selected(x) = True Then
strsql = "INSERT INTO tblPumpsorter (El) Values ('" & Listruta15.Column(0, x) & "')"
CurrentDb.Execute strsql, dbFailOnError
End If
Next x
On Error GoTo Err_laggtillpump_Click
    DoCmd.GoToRecord , , acNewRec
    Exit Sub
    MsgBox Err.Description
    Resume Exit_laggtillpump_Click
End Sub

The problem is that it don´t add all values to the same reccord it makes a new record for every selected value, and that kind of suck.
Ok, I just saw your post how you want to do it.
should read better.
How many fields are in your table?
And why do you want to store it across, what if the user picks like 50 of them, you would have to have a at least 50 fields in your table. What if they pick 75, or 100?
You might want to store them in one table, then use a crosstab query to arrange them how you see fit.
I will explain it further.
This part of the DB lets the user to ad a new product in to our system, in this form it´s about a pump.
So the table is as folow.
But in swedish the row "Voltage" is called "El"
The thing is that the pumps can operate with various type of voltage, so what i want is to select wich types it can use an then add it to a single reccord.
Name: J84HD
Type: Water
Voltage: 380V, 500V, 1000V
Capacaty: 1100 L/sec
Liftheight: 60 M
Weight: 75 KG
And the voltage listbox only contains five different values
Ok, well it can be done, however, I think there is a better way for you to normalize that.You could have a voltage table, then you would have the pkProductID then a field called voltage. So now you would have the table shown like:
1 500
1 1000
2 380
3 500
3 1000
Then you would have the query and the reports pull all the data together and sort and view it how you need it.
Thats just my idea, there are better people on here that can help with your database structure.
Yes, I was a little to hasty in my idea. When I thought about it I se several problems to have it my first way, sort of speaking. It could be problems when you for example wants to list all the pumps that can be run on 500V, so its better to use a query and a report to pull it togheter.
Thanks for helping me out.
You should not be storing multiple values in a single field ... that goes against proper database design guidlines.
You should set up a child table then create a Relationship between the parent and child tables through a Primary Key - Foreign Key field ...
ParentID (Autonumber, PK)
<additional fields>
ChildID (Autonumber, PK)
ParentID (Number/Long; FK to tblParents.ParentID
<additional fields>
I would advise you to read the following posts regarding the concept of "Normalization" and other db design guidlines:
I don't think they wanted to store multiple values in a single field. They wanted multiple fields to be filled in. Thinking more along the lines of horizontally than vertically storing the data.
Please do check out those articles on normalization. You will be much happier in the end that you took the time to normalize.
Thanks mate, i will learn from your advise.
Thanks * 1000
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.