Still Getting Duplicates in listbox

magster06

Registered User.
Local time
Today, 10:51
Joined
Sep 22, 2012
Messages
235
Hello All,

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!
 
Where does

strQuerySelection

get a value?
What does this give? Debug.Print strSQL
 
strQuerySelection gets its value from the lstQuerySelection selection.

Debug.Print strSQL just shows me that the sql statement is receiving the correct info from the strQuerySelection.
 
The Data would be better stored in a Table of it's own. This is part of the standard normalisation process.
 
Go through your code logic again. Your strSQL with the DISTINCT is not used anywhere.
 
RainLover- I am not sure what you mean. Which data? The data loading the first lstbox (lstQuerySelections) or the second one?

spikepl
Go through your code logic again. Your strSQL with the DISTINCT is not used anywhere.

I know what you mean, but I tried in different ways, but to no avail.

this is what I use in vb6

rs.open strSQL, cn, etc...

but vba only has a .openrecordset and when I used that it would generate an error
 
The Data for a Combo Box or a List box should be stored in a separate Table.
 
The Data for a Combo Box or a List box should be stored in a separate Table.

OK, now I understand you. I did do that with my other comboboxes and listboxes, but I forgot to do it with my lstQuerySelections listbox.

I will change it and see if that will help out with my issue.


I still would like to find out where to put my sql statement.

**Update**

Still getting duplicate entries. Anyone know how to call a sql statement in vba?
 
Last edited:
It looks like you need to:

strSQL = "SELECT DISTINCT " & strQuerySelection & " FROM PSU1 ORDER BY " & strQuerySelection & " ASC"

set rs = dbsPSU1 .OpenRecordset(strSQL, dbReadOnly)
 
It looks like you need to:

strSQL = "SELECT DISTINCT " & strQuerySelection & " FROM PSU1 ORDER BY " & strQuerySelection & " ASC"

set rs = dbsPSU1 .OpenRecordset(strSQL, dbReadOnly)

I tried that already and it just throws an error (3141)

"The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect"

The error points at "Set rs = dbsPSU1.OpenRecordset(strSQL, dbReadOnly)"
In vba, is the strQuerySelection wrapped in the correct format?

By the way DrallocD, thanks for the reply!
 
Please show us what the row sources are for the List boxes.
 
Please show us what the row sources are for the List boxes.

The row source for the first listbox (lstQuerySelections) is from the table tblQueryFill

The row source for the second listbox(lstQueryResults) is from the code in my first post.
 
Can you post the sql statement that is in the row source for each list box?

We need to see what Access has.
From your post #3
strQuerySelection gets its value from the lstQuerySelection selection.

Debug.Print strSQL just shows me that the sql statement is receiving the correct info from the strQuerySelection.
 
What is output by "Debug.Print strSQL" ?

Hmmm, it is showing " & strQuerySelection & " instead of what should be in the string.
 
Can you post the sql statement that is in the row source for each list box?
The first listbox (lstQuerySelections) comes from the table; it does not show a sql statement there. Should it?

The second listbox shows nothing. I guess because I am trying to use an event procedure instead?

I am new at this vba , so please bear with me.

I am going to try and zip my db and see if anyone can tell what I am doing wrong (lol, which I am sure is a lot of things).
 
Ok, I have attached my project.

Please be kind, as I had mentioned, this is all new to me.


**Hmm, says it is too big of a file**
 
There is no attachment. However, I have acc 2003 and can not open an accdb format database. Can you save a copy in mdb format?

If your file is too large you should
a) run Compact and Repair, then
b) make a zip file to compress the file.
 
How do I do that?


I ran compact and repair and zipped it and it is still 2.3 and needs to be 2.0 in size



**Getting off duty, so I will try later to compress the file some more and upload it.

Ok, finally got it small enough. I had to strip the db down. When you start it, the db will give an macro error, just hit ok and it will start and run ok.
 

Attachments

Last edited:
Use the wizard in the properties box to build a new SQL.

In English it should be Select * from the LookUpTable order By one of the fields.
 

Users who are viewing this thread

Back
Top Bottom