Full Version: how to put multi selections from list to table
UtterAccess Forums > Microsoft® Access > Access Forms
daffelito
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.
awolterm
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
CODE

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.
daffelito
Ok, thanks, i will try it and if i get stuck i will give u a note.
daffelito
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 :
CODE
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_laggtillpump_Click:
    Exit Sub
Err_laggtillpump_Click:
    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.
awolterm
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.
daffelito
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.
ame
Type
Voltage
Capacaty
Liftheight
Weight
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.
Example:
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
awolterm
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:
380
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.
daffelito
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.
datAdrenaline
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 ...
tblParent
------------
ParentID (Autonumber, PK)
<additional fields>
tblChild
---------
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:
awolterm
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.
daffelito
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.