List Box for Query (A2k)

Topic

New Reply

Good Morning All,

Attached is a small database with a form and 2 tables. On the form is a list box with 27 items and a button that I want to use to build a query based on the selections from the list box. The query needs to be able to update the corresponding fields in the main table to zero. My vba skills are lacking in something of this magnitude so any and all suggestions are greatly appreciated. TIA.

Reply | Quote

Replies

Here is code for the command button:

Private Sub Command2_Click()
Dim strList As String
Dim strSQL As String
Dim varItm As Variant
' Loop through the selected items
For Each varItm In Me.List0.ItemsSelected
strList = strList & ", " & Me.List0.ItemData(varItm) & "=0"
Next varItm
' Check whether anything was selected.
If strList = "" Then
' If not, warn user and get out
Me.List0.SetFocus
MsgBox "Please select at least one item from the list box.", vbExclamation
Exit Sub
End If
' Get rid of first ", "
strList = Mid(strList, 3)
' Create SQL string
strSQL = "UPDATE tbl_NewMainInfo SET " & strList
' Temporarily display the SQL, you can remove this later
MsgBox strSQL, vbInformation
' Suppress warnings
DoCmd.SetWarnings False
' Execute SQL
DoCmd.RunSQL strSQL, True
' Turn on warnings again
DoCmd.SetWarnings True
End Sub

See attached version.

Reply | Quote

Thank you Hans. Works great.

Reply | Quote