Programattically add/Remove items from listbox

[spiralmind]

I am currently building a form to be used to allow users to add records to and remove records from a table (to be used as a more maintainable manner of defining relatively static query criteria than asking the users to directly edit a set of SQL statements). I would like to achive this by displaying a textbox, two listboxes, and three command buttons.
The textbox is to be used to enter a new item to add to the list, and will have an "Add to List" button to accompany it. The first listbox will hold all items currently in the list, and the second will hold any that the user has decided to remove (to allow the user to restore a value that they had mistakenly pulled from the first list). Between these two listboxes there will be two more buttons "Remove from list" and "Restore to List".
The challange I am facing is how to fill the first list (without locking it or binding it directly to the table it references), and how to add/remove items in each list through code written into the buttons' onClick events. Any help will be greatly appreciated as I have spent much time looking for an answer or suitable example and have not yet been able to locate one.

- Thankyou, Christian

 

[sanders720]

You should be able to modify the rowsource of a list or combo box, using a variable query string.

If you look at my code below, I am working on something similar, using Arrays to define data, and concatenating strings to define my variable of sql. The code below acts as a filter.

Option Compare Database
Option Base 1

Public A
Public B
Public B1

Public OBJECT As String
Public OBJECT2 As String
Public OBJECT3 As String
Public OBJECT4 As String


Private Sub PopulateComboBoxes()
Dim rst As Recordset
Dim sql As String
Dim c As Integer
Dim x As Integer
Dim extprice As Currency

sql = "SELECT * FROM qryInvoice"

Set rst = CurrentDb.OpenRecordset(sql)


rst.Close
Set rst = Nothing

sql = "SELECT qryInvoice.[Product Name], qryInvoice.[Product Manufacturer], qryInvoice.[Product Part Number], " & _
"qryInvoice.Quantity, qryInvoice.[Seats Covered], qryInvoice.[Purchase Price]" & _
"FROM qryInvoice " & _
"WHERE (((qryInvoice.[Invoice Number])= '" & Me.cboInvoiceNumber.Text & "'));"

Set rst = CurrentDb.OpenRecordset(sql)
rst.MoveLast
c = rst.RecordCount
rst.MoveFirst

With msgPurchasedDetail

If .Rows > 1 Then
.Rows = 1
End If

For x = 1 To c
.Rows = .Rows + 1
.TextMatrix(.Rows - 1, 1) = rst("[Product Manufacturer]&quot
.TextMatrix(.Rows - 1, 2) = rst("[Product Name]&quot
.TextMatrix(.Rows - 1, 3) = rst("[Product Part Number]&quot
.TextMatrix(.Rows - 1, 4) = rst("Quantity&quot
.TextMatrix(.Rows - 1, 5) = rst("[Seats Covered]&quot
.TextMatrix(.Rows - 1, 6) = Format(rst("[Purchase Price]&quot, "$###0.00&quot
.TextMatrix(.Rows - 1, 7) = Format(.TextMatrix(.Rows - 1, 4) * .TextMatrix(.Rows - 1, 6), "$###0.00&quot
rst.MoveNext

Next x
End With

rst.Close
Set rst = Nothing

End Sub


Private Sub cboAuthorizingUser_Click()
Me.txtAuthorizingUser.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboCDKey_Click()
Me.txtCDKey.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboExtendedPrice_Click()
Me.txtExtendedPrice.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboInstalledPC_Click()
Me.txtInstalledPC.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboInvoiceNo_Click()
Me.txtInvoiceNo.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboITJobNo_Click()
Me.txtITJobNo.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboLicense_Click()
Me.txtLicense.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboManufacturer_Click()
Me.txtManufacturer.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboName_Click()
Me.txtName.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboPartNo_Click()
Me.txtPartNo.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboPurchaseDate_Click()
Me.txtPurchaseDate.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboPurchasedPrice_Click()
Me.txtPurchasedPrice.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboQuantity_Click()
Me.txtQuantity.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboRequestingUser_Click()
Me.txtRequestingUser.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboSeats_Click()
Me.txtSeats.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboSerialNo_Click()
Me.txtSerialNo.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboUpgrade_Click()
Me.txtUpgrade.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboUser_Click()
Me.txtUser.Value = "1"
BUILDQUERY
REPOPULATE

End Sub

Private Sub cboVendor_Click()
Me.txtVendor.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub cboWarrantyDate_Click()
Me.txtWarrantyDate.Value = "1"
BUILDQUERY2
REPOPULATE

End Sub

Private Sub Form_Load()

Dim x As String
Dim c As Integer
Dim q As Integer
Dim sql As String
Dim rs As ADODB.Recordset

With msgPurchasedDetail

.Rows = 1
.Cols = 21

.ColWidth(0) = 0
.ColWidth(1) = 600
.ColWidth(2) = 2000
.ColWidth(3) = 1000
.ColWidth(4) = 1500
.ColWidth(5) = 1500
.ColWidth(6) = 800
.ColWidth(7) = 800
.ColWidth(8) = 2200
.ColWidth(9) = 2400
.ColWidth(10) = 1800
.ColWidth(11) = 2000
.ColWidth(12) = 2200
.ColWidth(13) = 600
.ColWidth(14) = 600
.ColWidth(15) = 600
.ColWidth(16) = 1600
.ColWidth(17) = 1600
.ColWidth(18) = 2200
.ColWidth(19) = 900
.ColWidth(20) = 1000

For q = 0 To 19
.ColAlignment(c) = 1
Next q

.TextMatrix(0, 1) = "IT Job No."
.TextMatrix(0, 2) = "Vendor"
.TextMatrix(0, 3) = "Inv No."
.TextMatrix(0, 4) = "Req User"
.TextMatrix(0, 5) = "Auth User"
.TextMatrix(0, 6) = "Purch Date"
.TextMatrix(0, 7) = "Warr Date"
.TextMatrix(0, 8) = "Manufacturer"
.TextMatrix(0, 9) = "Name"
.TextMatrix(0, 10) = "Part No."
.TextMatrix(0, 11) = "Serial No."
.TextMatrix(0, 12) = "CD Key"
.TextMatrix(0, 13) = "Upgr"
.TextMatrix(0, 14) = "Qty"
.TextMatrix(0, 15) = "Seats"
.TextMatrix(0, 16) = "User"
.TextMatrix(0, 17) = "Inst PC"
.TextMatrix(0, 18) = "License"
.TextMatrix(0, 19) = "Purch Price"
.TextMatrix(0, 20) = "Ext Price"

End With

Set rs = New ADODB.Recordset

A = Array("[IT Job Number]", "Vendor", "[Invoice Number]", "[Requesting User]", "[Authorizing User]", "[Purchase Date]", _
"[Warranty End Date]", "[Product Manufacturer]", "[Product Name]", "[Product Part Number]", "[Product Serial Number]", "[CD Key]", _
"[Upgrade Flag]", "Quantity", "[Seats Covered]", "[User Assignment]", "[Installed PC Name]", "[Product License Number]", _
"[Purchase Price]&quot

B = Array("cboITJobNo", "cboVendor", "cboInvoiceNo", "cboRequestingUser", "cboAuthorizingUser", "cboPurchaseDate", _
"cboWarrantyDate", "cboManufacturer", "cboName", "cboPartNo", "cboSerialNo", "cboCDKey", _
"cboUpgrade", "cboQuantity", "cboSeats", "cboUser", "cboInstalledPC", "cboLicense", _
"cboPurchasedPrice&quot

For q = 1 To 19
Debug.Print A(q)
Debug.Print B(q)

sql = "SELECT qrySoftwareTracking." & A(q) & " FROM qrySoftwareTracking;"
Debug.Print sql

' Forms!frmSoftwareTracking(B(q)).RowSource = sql (syntax for modules)
Me(B(q)).RowSource = sql

' The following code is not necessary, but is good syntax reference for ADO
With rs
.CursorLocation = adUseClient
.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
.MoveLast
c = .RecordCount
Debug.Print c
.MoveFirst

.Update
.Close

End With
Next q

Set rs = Nothing

End Sub

Private Sub BUILDQUERY()

Dim c As Integer
Dim q As Integer

Dim pL As String
Dim pR As String

For q = 1 To 19

If B1(q).Text <> &quot;2&quot; Then Me.B(q).Clear

If B(q) <> &quot;&quot; Then
c = c + 1
If Len(OBJECT) > 0 Then
OBJECT = OBJECT & &quot; and (&quot; & A(q) & &quot;= '&quot; & B(q) & &quot;'.Text)&quot;
Else
OBJECT = &quot;(&quot; & A(q) & &quot;= '&quot; & B(q) & &quot;'.Text)&quot;
End If
Debug.Print OBJECT

End If

Next q

For q = 2 To c
pL = pL & &quot;(&quot;
pR = pR & &quot&quot;
Next q

OBJECT = pL & OBJECT & pR

End Sub

Private Sub BUILDQUERY2()

Dim c As Integer
Dim q As Integer

B1 = Array(&quot;txtITJobNo&quot;, &quot;txtVendor&quot;, &quot;txtInvoiceNo&quot;, &quot;txtRequestingUser&quot;, &quot;txtAuthorizingUser&quot;, &quot;txtPurchaseDate&quot;, _
&quot;txtWarrantyDate&quot;, &quot;txtManufacturer&quot;, &quot;txtName&quot;, &quot;txtPartNo&quot;, &quot;txtSerialNo&quot;, &quot;txtCDKey&quot;, _
&quot;txtUpgrade&quot;, &quot;txtQuantity&quot;, &quot;txtSeats&quot;, &quot;txtUser&quot;, &quot;txtInstalledPC&quot;, &quot;txtLicense&quot;, _
&quot;txtPurchasedPrice&quot

For q = 1 To 19
If B(q) <> &quot;&quot; And B1(q) <> &quot;2&quot; Then
c = c + 1
If Len(OBJECT2) > 0 Then
OBJECT2 = OBJECT2 & &quot; and (&quot; & A(q) & &quot;= '&quot; & B(q) & &quot;'.Text)&quot;
Else
OBJECT2 = &quot;(&quot; & A(q) & &quot;= '&quot; & B(q) & &quot;'.Text)&quot;
End If
Debug.Print OBJECT2

B1(q).Value = &quot;2&quot;
B(q).Enabled = False

End If

Next q

End Sub

Private Sub REPOPULATE()

Dim rs As Recordset
Dim q As Integer
Dim c As Integer

Set rs = New ADODB.Recordset

For q = 1 To 19
With rs
OBJECT3 = &quot;SELECT qrySoftwareTracking.* FROM qrySoftwareTracking WHERE 1=1 AND (&quot; & OBJECT2
Debug.Print OBJECT3

' Forms!frmSoftwareTracking(B(q)).RowSource = OBJECT3 (syntax for modules)
Me(B(q)).RowSource = OBJECT3

End With

Next q

End Sub