CODEWITHSUNDEEP
ms-accesslistboxvba
user2346044
user2346044

Reputation:

Add Values to a multi column listbox in Access '07 VBA

I am a beginner to Access '07. I currently am working on a Canteen Query System. In the Add/Modify Item screen, I have a combobox which draws values from a table tblSampleData which contains Item, Rate, QtyAvailable fields. I have the required items transferred from the combo (which is bascially a list of all available items) to a listbox (which contains the items the user wants) through the click of a transfer button. Both the combo and the list have column headers. How do you CREATE ANOTHER row, which is not associated to any table, in the listbox, called Quantity, where the user can enter the quantity of his item?

Urgent help will be congratulated. Any other way to solve this will be complimented. Also, Please feel free to ask ANY queries regarding this, if you didn't understand.

Regards, S Sandeep

Upvotes: 1

Views: 13730

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123839

If I understand your requirements correctly, you will need to add a textbox to your form so the user can specify a quantity when they select an item.

[assumptions]

--> Table data: My [tblSampleData] looks like this:

ID  Item    Rate    QtyAvailable
1   Item1   Rate1   3
2   Item2   Rate2   5

--> Combo box: The Bound Column of the combo box is 1, the (hidden) first column, which is [ID].

--> Data bindings: The combo box has a Row Source (tblSampleData), but no Control Source. The list box is completely unbound.

If your situation is different they you'll need to tweak the sample code to suit.

[/assumptions]

I created a test form that looks like this when it is first opened:

firstopened

The VBA code behind the form is this:

Option Compare Database
Option Explicit

Private Sub btnTransfer_Click()
Dim cdb As DAO.Database, rst As DAO.Recordset, qtySelected As Long
Set cdb = CurrentDb
Set rst = cdb.OpenRecordset("SELECT * FROM tblSampleData WHERE ID=" & Me.cbxItems.Value, dbOpenSnapshot)
qtySelected = Val(Nz(Me.txtQty.Value, 0))
If qtySelected <= 0 Then
    MsgBox "Please specify a (positive) quantity.", vbExclamation
Else
    If qtySelected <= rst!QtyAvailable Then
        Me.lstSelected.AddItem rst!ID & ";" & rst!Item & ";" & rst!Rate & ";" & qtySelected
    Else
        MsgBox "Quantity selected exceeds quantity available.", vbExclamation
    End If
End If
rst.Close
Set rst = Nothing
Set cdb = Nothing
End Sub

Private Sub Form_Load()
Do While Me.lstSelected.ListCount > 0
    Me.lstSelected.RemoveItem 0
Loop
Me.lstSelected.AddItem ";Item;Rate;QtySelected"
End Sub

The user selects the item from the combo box...

cbx

...enters the quantity in the text box...

qty

...and then clicks the "Transfer" button to move the item + selected quantity into the list box:

itemadded

Upvotes: 3

Related Questions