Hello All,
Access 2010
Windows 7
I have the following code:
I have 2 listboxes: lstQuerySelections, lstQueryResults
When a user clicks on the lstQuerySelections then the results are populated in the lstQueryResults listbox.
This works as it should, but what happens is that I am still getting duplicate entries even though I am using "DISTINCT" in the sql statement to prevent this.
What am I missing (besides my brain)?
Oh, I used DAO to open the database and recordset because I could not get the connection to open with code.
Also, I hope this is the correct area to post this question.
Thanks ahead of time!
Access 2010
Windows 7
I have the following code:
Code:
Private Sub lstQuerySelection_Click()
Dim strQuerySelection As String
Dim i As Integer
Dim intCurVal As Integer
Dim strCompare As String
Dim intCompare As Integer
Dim dbsPSU1 As DAO.Database
Dim rs As DAO.Recordset
Set dbsPSU1 = CurrentDb
Set rs = dbsPSU1.OpenRecordset("PSU")
strSQL = "SELECT DISTINCT " & strQuerySelection & " FROM PSU1 ORDER BY " & strQuerySelection & " ASC"
Debug.Print strSQL
If lstQuerySelection.ListIndex <> -1 Then
strQuerySelection = lstQuerySelection.ItemData(lstQuerySelection.ListIndex)
With lstQueryResults
.RowSource = ""
Do While Not rs.EOF
If Not IsNull(rs.Fields(strQuerySelection).Value) Then 'removes most of the blank spaces from the listbox
.AddItem rs.Fields(strQuerySelection).Value
.Requery
End If
rs.MoveNext
Loop
End With
Else
If (rs.BOF Or rs.EOF) Then
MsgBox "No matches found; Please check your criteria", vbInformation + vbOKOnly, "PSU Query"
End If
End If
rs.Close
dbsPSU1.Close
Set rs = Nothing
Set dbsPSU1 = Nothing
End Sub
I have 2 listboxes: lstQuerySelections, lstQueryResults
When a user clicks on the lstQuerySelections then the results are populated in the lstQueryResults listbox.
This works as it should, but what happens is that I am still getting duplicate entries even though I am using "DISTINCT" in the sql statement to prevent this.
What am I missing (besides my brain)?
Oh, I used DAO to open the database and recordset because I could not get the connection to open with code.
Also, I hope this is the correct area to post this question.
Thanks ahead of time!