Selected Combo Box Item to List Box (1 Viewer)

raziel3

Registered User.
Local time
Today, 05:34
Joined
Oct 5, 2017
Messages
273
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: -

Code:
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

Well-known member
Local time
Today, 05:34
Joined
Apr 9, 2015
Messages
4,339
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.

Charge fees.jpg
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:34
Joined
Oct 29, 2018
Messages
21,358
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

Premier Pale Stale Ale
Local time
Today, 05:34
Joined
Oct 17, 2012
Messages
3,276
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

Registered User.
Local time
Today, 05:34
Joined
Oct 5, 2017
Messages
273
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

I’m here to help
Staff member
Local time
Today, 02:34
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 05:34
Joined
Oct 5, 2017
Messages
273
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

I’m here to help
Staff member
Local time
Today, 02:34
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 05:34
Joined
Oct 5, 2017
Messages
273
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

I’m here to help
Staff member
Local time
Today, 02:34
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 05:34
Joined
Oct 5, 2017
Messages
273
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

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

Which is much easier. Thanks.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 02:34
Joined
Oct 29, 2018
Messages
21,358
Hi. Glad to hear you got it sorted out. Good luck with your project.
 

raziel3

Registered User.
Local time
Today, 05:34
Joined
Oct 5, 2017
Messages
273
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

I’m here to help
Staff member
Local time
Today, 02:34
Joined
Oct 29, 2018
Messages
21,358
Hi. Either add all the columns at once or one at a time. For example
Code:
.AddItem Column(0) & “;” & Column(1) & “;” & Column(3)
Or
Code:
.AddItem Column(0)
.AddItem Column(1)
.AddItem Column(2)
 

Users who are viewing this thread

Top Bottom