如何在VBA标签中显示从SQL查询到的多条STA_ID数据？

Hey there! I see the issue here—your current code is overwriting label3.Value every time the loop runs, which is why only the last STA_ID shows up. Let's fix that by concatenating all matching STA_IDs instead of replacing the value each time.

The Problem Breakdown #

In your loop, this line is the culprit:

label3.Value = rs(0)

It replaces the existing content in label3 with the current record's STA_ID on every iteration. By the end of the loop, only the last record's ID remains visible.

The Solution #

We'll use a temporary string variable to collect all STA_IDs as we loop through the recordset, then assign the full list to label3 once the loop finishes. Here's the updated code:

SQL = "SELECT STA_ID, AVG(STA_INCOME) " & _ 
"FROM STAFF " & _ 
"WHERE STAFF_EXP = '" & Label1.Value & "' " & _ 
"GROUP BY STA_ID"
rs.Open SQL, cn

' Initialize a string to hold all STA_IDs
Dim allStaffIds As String
allStaffIds = ""
' Optional: Store average incomes if you want to display all (your original code only shows the last one)
Dim avgIncomes As String
avgIncomes = ""

With rs
    i = 0
    Do Until .EOF
        ' Append current STA_ID to our list (add a separator for readability)
        If allStaffIds <> "" Then
            allStaffIds = allStaffIds & ", " ' Use vbCrLf instead if you want line breaks
        End If
        allStaffIds = allStaffIds & rs(0)
        
        ' Optional: If you want to show each STA_ID's corresponding average income
        If avgIncomes <> "" Then
            avgIncomes = avgIncomes & ", "
        End If
        avgIncomes = avgIncomes & rs(1)
        
        i = i + 1
        .MoveNext
    Loop
    
    ' Assign the full list of IDs to label3
    label3.Value = allStaffIds
    ' Optional: Update label2 to show all averages (or keep as last one if that's your intended behavior)
    ' label2.Value = avgIncomes
    label2.Value = rs(1) ' This retains your original logic of showing the last average
End With

Bonus: Fix SQL Injection Risk #

Your current code directly inserts Label1.Value into the SQL string, which is a major security risk (SQL injection). Let's switch to parameterized queries to make it safer:

Dim cmd As New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "SELECT STA_ID, AVG(STA_INCOME) FROM STAFF WHERE STAFF_EXP = ? GROUP BY STA_ID"
' Adjust parameter type (adVarChar) and length (50) to match your STAFF_EXP field's properties
cmd.Parameters.Append cmd.CreateParameter("@Experience", adVarChar, adParamInput, 50, Label1.Value)
Set rs = cmd.Execute

' Rest of the loop code stays the same...

Formatting Options #

• For comma-separated IDs: Stick with ", " as the separator (like in the example).
• For line-separated IDs: Replace ", " with vbCrLf—this will make each ID appear on a new line in label3, just ensure label3 has enough height to display all lines.

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