Selected Combo Box Item to List Box

[raziel3]

Good Day, I am trying to create a sales invoice (frmCalc) where by a user chooses an item from a Popup form (POP) and once selected it is transferred to a list box on the sales invoice form (frmCalc). After searching the internet I came across this: -

On Error GoTo AddItem
    For i = LBound(Forms!frmCalc!Sale.List) To UBound(Forms!frmCalc!Sale.List)
        If Forms!frmCalc!Sale.List(i) = Forms!POP!Text4 Then
            MsgBox " u cannot add this item"
            Exit Sub
        End If
    Next i
AddItem:
    Forms!frmCalc!Sale.AddItem (Forms!POP!Text4)

but i am getting a Run-time error 6014 'RowsourceType must be set to Value List'. Changing the combo box to Value List will not give me my product list to choose from.

 

[Ranman256]

instead of using code, use a listbox to add to a listbox.
(tho you can use combo)

user can Dbl-click all the items wanted, this runs an append query to the target table.

 

[theDBguy]

Hi. The .AddItem method "adds" an item to the list of choices. I think what you want is to do is "select" an item in the list. Is that correct?

 

[Frothingslosh]

Based on the description, the OP wants items selected from a pop-up to be added to a listbox. The error message, however, indicates that the listbox is based on a query, rather than a value list, so what we really need to do is determine what the criteria are for the query and how to add the selected item to the resulting recordset.

Failing that, I guess we teach him how to create a value list.

 

[raziel3]

Hi. The .AddItem method "adds" an item to the list of choices. I think what you want is to do is "select" an item in the list. Is that correct?

That's correct. My combo box row source is based on my Products table. I want to select a product and 'transfer' it to the List box on another form. Select another product and transfer to the next item in the list box and so on. If the product already exist in the list box "Error, Item already on invoice".

 

[theDBguy]

That's correct. My combo box row source is based on my Products table. I want to select a product and 'transfer' it to the List box on another form. Select another product and transfer to the next item in the list box and so on. If the product already exist in the list box "Error, Item already on invoice".

Hi. Thanks for the clarification. So, after you have added all the items to the Listbox, is the user now supposed to select from these added items?

 

[raziel3]

Hi. Thanks for the clarification. So, after you have added all the items to the Listbox, is the user now supposed to select from these added items?

No, just print the list. My Products table have the ProductID, Description, Price. I want to populate the list box with those 3 fields. Once selection is finished, close the POP Form and print the list.

 

[theDBguy]

No, just print the list. My Products table have the ProductID, Description, Price. I want to populate the list box with those 3 fields. Once selection is finished, close the POP Form and print the list.

Hmm, sounds good, but I'm a little confused. Why not just use a multi-select listbox rather than add the extra step of using a popup? Just curious...

 

[raziel3]

Products list is too long. The user can just type a couple characters in the combo box to get the product to be added. If there is a way to have the list box reference the product that would be easier. So i start with a blank list box and on each row enter the product(s) needed.

 

[theDBguy]

Products list is too long. The user can just type a couple characters in the combo box to get the product to be added. If there is a way to have the list box reference the product that would be easier. So i start with a blank list box and on each row enter the product(s) needed.

Ah, makes sense. It sounds similar to this other discussion. The issue is, if you use a listbox as the source of your criteria, the items will have to be selected. And since you want multiple criteria, you'll need to make it a multi-select listbox. You should be able to achieve the same effect if you simply use a "big" textbox to display the selections made from the popup. Then, you can use the textbox a bit more straightforward then a multi-select listbox for your criteria. You won't need any code to auto-select the listbox items and then loop through the selections to build your criteria, which would probably look like the textbox content anyway.

 

[raziel3]

You got me thinking. It would be easier to skip the popup step so I just created a combo box on the same form and inserted this

Private Sub Text4_AfterUpdate()
Me.Sale.AddItem (Me.Text4.Column(0))
End Sub

Which is much easier. Thanks.

 

[theDBguy]

Hi. Glad to hear you got it sorted out. Good luck with your project.

 

[raziel3]

My list and combo boxes are setup to display 3 columns but after I have selected the item from the combo box only one column is being populated in the list box. How do you populate the 3 columns in the list box?

 

[theDBguy]

Hi. Either add all the columns at once or one at a time. For example

.AddItem Column(0) & “;” & Column(1) & “;” & Column(3)

Or

.AddItem Column(0)
.AddItem Column(1)
.AddItem Column(2)