Still Getting Duplicates in listbox

[DrallocD]

Try this:

Private Sub lstQuerySelection_Click()
On Error GoTo ErrorHappened
    Dim strQuerySelection As String
    Dim dbsPSU1 As DAO.Database
    Dim rs As DAO.Recordset
    
    If lstQuerySelection.ListIndex <> -1 Then
        strQuerySelection = "[" & lstQuerySelection.ItemData(lstQuerySelection.ListIndex) & "]"
    Else
        GoTo ExitNow
    End If
    
    Set dbsPSU1 = CurrentDb
    strSQL = "SELECT DISTINCT " & strQuerySelection & " FROM PSU WHERE nz(" & strQuerySelection & ","""") <> """" ORDER BY " & strQuerySelection & " ASC"
    Set rs = dbsPSU1.OpenRecordset(strSQL, dbOpenSnapshot)
    
    If rs.EOF Then
        MsgBox "No matches found; Please check your criteria", vbInformation + vbOKOnly, "PSU Query"
        GoTo ExitNow
    End If
    
    lstQueryResults.RowSource = ""
    Do While Not rs.EOF
        lstQueryResults.AddItem rs.Fields(0)
        rs.MoveNext
    Loop
ExitNow:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set dbsPSU1 = Nothing
    Exit Sub
ErrorHappened:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ")"
    Resume ExitNow
End Sub

 

[magster06]

DrallocD,

Your code works great!

I can see the subtle differences between your code and mine.

I will have to get a book and learn these differences between vb6 and vba.

I REALLY appreciate the help!

And thanks to the others that have replied as well.


***Another Question***

What would you guys recommend in place of a datagrid to display the results of my second listbox (lstQueryResults)?

I see where Access doesnt support a datagrid (boooo!).

 

[DrallocD]

Maybe a subform in datasheet view?

These can be very powerful but difficult to use.

 

[magster06]

Maybe a subform in datasheet view?

Yeah, I think I am going to try that. I have already used a few of them in my project, but they are bound to the PSU table; so populating was easy.

So far I have not been able to do it with code, but I am still working on it.