如何使用包含多行记录的SQL查询记录集填充多列列表框？

Let's fix up your Access VBA code to properly populate that multi-column listbox with records from your Bills table. Your original code has a handful of syntax and logic bugs that are keeping it from working—let's break them down first, then walk through the corrected version.

First, let's identify the issues in your original code #

• Variable declaration inconsistency: In Dim i,i3, i1, i2 As Integer, only i2 is declared as Integer—the rest default to Variant. Always explicitly declare each variable's type to avoid unexpected behavior.
• Invalid CurrentDb usage: Set v1 = CurrentDb("Bills") is incorrect syntax. CurrentDb returns a Database object; you don't pass a table name directly to it, and this line is entirely unnecessary.
• Non-numeric loop counter: i1 is assigned a SQL string instead of a numeric count of records. You can't use a string in a For...Next loop.
• Field index out of bounds: rst.Fields(i2).Value uses i2 (the total number of fields) as an index—but field indexes start at 0, so this will throw an error. Also, you're reusing i2 in the inner loop, which confuses the variable's purpose.
• Incorrect multi-column listbox population: Your nested loop structure doesn't align with how multi-column listboxes work. You need to add a row first, then set values for each column in that row.
• Unsafe SQL string concatenation: If billnumber is a text field, your SQL will fail because you're not wrapping the value in single quotes. This also exposes you to SQL injection risks.

Here's the corrected code with clear explanations #

Private Sub Combo13_AfterUpdate()
    Dim vBillNumber As Variant
    Dim iRecordCount As Integer
    Dim iFieldCount As Integer
    Dim iRow As Integer
    Dim iCol As Integer
    Dim rst As Recordset
    Dim strSQL As String
    
    ' Grab the selected value from the combo box
    vBillNumber = Combo13.Value
    
    ' Exit early if no selection is made to avoid errors
    If IsNull(vBillNumber) Then
        List6.Clear
        Exit Sub
    End If
    
    ' Build safe SQL: add single quotes if billnumber is text, remove if it's numeric
    strSQL = "SELECT * FROM Bills WHERE billnumber = '" & vBillNumber & "';"
    
    ' Open the recordset with our query
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
    
    ' Clear existing items in the listbox before adding new ones
    List6.Clear
    
    ' Get the number of records and fields from the recordset
    iRecordCount = rst.RecordCount
    iFieldCount = rst.Fields.Count
    
    ' Ensure the listbox has the right number of columns to match our recordset
    List6.ColumnCount = iFieldCount
    
    ' Populate the listbox with records
    If Not rst.EOF Then
        rst.MoveFirst
        iRow = 0
        Do Until rst.EOF
            ' Add a blank row to the listbox
            List6.AddItem
            
            ' Fill each column of the current row with recordset data
            For iCol = 0 To iFieldCount - 1
                List6.Column(iCol, iRow) = rst.Fields(iCol).Value
            Next iCol
            
            iRow = iRow + 1
            rst.MoveNext
        Loop
    End If
    
    ' Clean up database objects to avoid memory leaks
    rst.Close
    Set rst = Nothing
End Sub

Key fixes and improvements: #

1. Explicit variable types: All variables are now properly declared with their intended types, making the code easier to debug and maintain.
2. Safety check: We exit early if the combo box has no selection, preventing unnecessary database calls and errors.
3. Safe SQL construction: Added single quotes around vBillNumber (remove them if billnumber is a numeric field) to avoid syntax errors.
4. Correct listbox population: We first add a row with AddItem, then set each column's value using List6.Column(iCol, iRow)—this is the standard way to populate multi-column listboxes in Access.
5. Proper recordset handling: We check if the recordset has records with Not rst.EOF, use a Do Until loop to iterate through records, and explicitly close the recordset before cleaning up.
6. Column count alignment: We set the listbox's ColumnCount to match the number of fields in the recordset, ensuring all data displays correctly.

Bonus: Safer parameter query alternative #

To avoid SQL injection risks entirely (especially if billnumber is user-input text), use a parameter query instead of string concatenation:

Private Sub Combo13_AfterUpdate()
    Dim vBillNumber As Variant
    Dim iRow As Integer
    Dim iCol As Integer
    Dim qdf As QueryDef
    Dim rst As Recordset
    
    vBillNumber = Combo13.Value
    If IsNull(vBillNumber) Then
        List6.Clear
        Exit Sub
    End If
    
    ' Create a temporary parameter query
    Set qdf = CurrentDb.CreateQueryDef("", _
        "SELECT * FROM Bills WHERE billnumber = [SelectedBillNumber];")
    
    ' Assign the selected combo box value to the query parameter
    qdf.Parameters("[SelectedBillNumber]") = vBillNumber
    
    ' Open the recordset using the parameter query
    Set rst = qdf.OpenRecordset(dbOpenDynaset)
    
    List6.Clear
    List6.ColumnCount = rst.Fields.Count
    
    If Not rst.EOF Then
        rst.MoveFirst
        iRow = 0
        Do Until rst.EOF
            List6.AddItem
            For iCol = 0 To rst.Fields.Count - 1
                List6.Column(iCol, iRow) = rst.Fields(iCol).Value
            Next iCol
            iRow = iRow + 1
            rst.MoveNext
        Loop
    End If
    
    ' Clean up all database objects
    rst.Close
    Set rst = Nothing
    Set qdf = Nothing
End Sub

内容的提问来源于stack exchange，提问作者prem