multiselect listbox: how to store field values

[holgar]

I have a coboboxwith a list of products and a multiselect listbox "Countries". The price of the product varies (two variants) and depends of the country being chosen. This price has its own field on form. Then it is calculated in discount and newprice field textbox.
And here is the problem. With code behind Save Records(command button)i can't get the other price variant. Records are filled only with one value from textboxes.
Is there a way to get the real values for every record?

Everything works fine if i divide countries in two listboxes where only one price variant exists.


Thanks

 

[jdegeorge]

You can't save multi-select values like that. You can pass them along to a report as a filter, for example, but you can't store them.

Jim DeGeorge

 

[Isadore]

hol: not exactly sure what it is you're trying to do but I don't see why you can't caputre multiple values -- and then proceed to dice 'em up ---

jd may be correct; I'm just throwing this in just in case it may help, just a snippet of code:

Dim strItems, strItem As String
Dim intCurrentRow As Integer
Dim i, intMyIndex As Integer
Dim strMyID As String
Dim ctlSource As Control
Dim ctlDest As Control

...to concatenate values from a list box...

strItems = ""
i = 0
Set ctlSource = Forms![frmClients]![lstClients]
Set ctlDest = Forms![frmClients]![Concat]
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
i = i + 1
If i = 1 Then
strItems = ctlSource.Column(0, intCurrentRow)
Else
strItems = strItems & ", " & ctlSource.Column(0, intCurrentRow)
End If
End If
Next intCurrentRow
[Concat] = strItems

...or looping through listbox and capturing names...

i = 0
Set ctlSource = Forms![frmClients]![lstClients]
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
i = i + 1
If i = 1 Then
[C1] = ctlSource.Column(0, intCurrentRow)
ElseIf i = 2 Then
[C2] = ctlSource.Column(0, intCurrentRow)
ElseIf i = 3 Then
[C3] = ctlSource.Column(0, intCurrentRow)
ElseIf i = 4 Then
[C4] = ctlSource.Column(0, intCurrentRow)
ElseIf i = 5 Then
[C5] = ctlSource.Column(0, intCurrentRow)
End If
End If
Next intCurrentRow

...just a tidbit, nothing serious

 

[holgar]

Hm I'm not sure if this code can help. I will try to explain a little bit more.
As I said I have a combo with four columns.

ID Product PriceA PriceB

There is a multiselect listbox with countries. Some countries are in Price A category, the rest in Price B cat.
These are defined by

If Me.CountryCode.Column(2) = "-1" Then
Me.Price = Me.Products.Column(3)
If Me.CountryCode.Column(2)= "0" Then
Me.Price = Me.Products.Column (4)

When I multiselect countries and click on a command button
(DAO addrecord) all records are saved at once and everything works but is correct price and discount calculation on this price. It is the same price and discount percent in all saved records .If I chose one country and save it all fields are OK.

It seems that something is missing but I can't figure it out.

Here is the code behind command button

Private Sub cmdSave_Click()
Dim Db As Database
Dim rs As DAO.Recordset
Dim ctlList As Control
Dim varItem As Variant

Set Db = CurrentDb()
Set rs = Db.OpenRecordset("tblDiscount&quot
Set ctlList = Forms!frmDiscount!CountryCode
For Each varItem In ctlList.ItemsSelected
rs.AddNew
rs("CountryCode&quot = Me.CountryCode.ItemData(varItem)
rs("ID&quot= Me.ID
rs("CampaignName&quot = Me.CampaignName
rs("Products&quot = Me.cboProducts.Column(1)
rs("Price&quot= Me.Price
rs("Discount" = Me.Discount
Next varItem
rs.Close
Set rs = Nothing
Set Db = Nothing

There are some more fields but they are not important here.

 

[jdegeorge]

This may be a multi-select list box, but it looks like the "multi" in this instance is the column value based on the selection of the country. Do you ever select multiple countries in the same record?

Jim DeGeorge

 

[holgar]

No, they are not in the same record. This code stores every country selected end the rest but the problem is that it doesnt work when it comes to save one variant of two possible. It just save the variant of the last selected item.
If you know how to do this I woul appriciate you help.