Click here to Skip to main content
15,742,518 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
When I click a button with a letter on, say M I cannot loop through my table to get results to display in a Listview. I am using VB6 and Access 97

What I have tried:

Numerous hours searching the internet and various coding ways, from previous applications I have done. This is my first using a connection string. The code for the button is as follows:
Private Sub cmd_SelectByLetter_Click(Index As Integer)
    Dim Kga_RS As Recordset
    Dim sql, myLetter As String
    Dim i As Long 'use long if the database has a huge amount of records; other wise use integer
    ' clear the listview
    Set Kga_RS = New Recordset
'    Kga_RS.LockType = adLockOptimistic
'    Kga_RS.CursorType = adOpenKeyset
    myLetter = Chr(CLng(Index) + 65)
    sql = "SELECT * FROM [tbl_KGA_MEMBER_DETAILS] WHERE [F_Name] LIKE '" & myLetter _
        & "*' ORDER BY [F_Name] ASC"
    Kga_RS.Open sql, dbCon, , adLockOptimistic
    i = 0
    With lstv_AllKgaMembers
        Do Until Kga_RS.EOF
            FullName = Kga_RS!F_Name & " " & Kga_RS!L_Name
            ' "i" will be used to point to the row number for the items being added
            ' to the listview increment the i each time it loops past it
            i = i + 1
            ' now write the records from the recordset to the listview fields
            .ListItems.Add , , Kga_RS!KgaNo 'control id
            ' you can short cut this, but it is important not to do so check for blank fields,
            ' otherwise you'll get errors when there is a field that is blank (for example, if there is no address)
            If Not FullName = "" Then .ListItems(i).ListSubItems.Add , , FullName Else .ListItems(i).ListSubItems.Add , , ""
    End With
    cmd_SelectAllMembers.Enabled = True
    cmd_ClearAll.Visible = True
End Sub
Updated 7-Mar-19 2:11am
Maciej Los 17-Sep-18 4:12am    
Unclear! Please, be more specific and provide more details about your issue.
Note: i do not see any connection string.
Mick_of_Goju 17-Sep-18 19:43pm    
I have a form with a Listview control and several buttons. One button when clicked fills the listview with all the members from my DB that is Membership No and Name no issues when this button is clicked. I have buttons which are indexed and the buttons have the first letter of the alphabet that is A.....Z if I want to list members with the first name staring with M I click the M button but the code does not loop but goes from "With lstv_AllKgaMembers" then "Do Until Kga_RS.EOF" then straight to "End with" but does not Loop through the record to display all name starting with M. Also the code for my connection is.
dbFile = App.Path & "\KgaDB.mdb"
Set dbCon = New Connection
' for Access below version 2007 I am using Access’97
dbCon.Provider = "Microsoft.Jet.OLEDB.4.0"
' dbCon.Provider = "Microsoft.ACE.OLEDB.12.0" 'this string also works OK
dbCon.Mode = adModeReadWrite
dbCon.Open (dbFile)
I also point out that all my other forms I use do not have any issues. I thank you for your time and hope I have provided enough info for you to assist.
Richard Deeming 18-Sep-18 15:50pm    
VB6 and Access 97 - a language that's been "dead" for over 15 years, talking to a database that's been "dead" for 18 years.

You'll struggle to find anyone who still has either of those running, or can remember enough about how they work to help you. :)

1 solution

Assuming this is still an issue here are a few things to try

sql = "SELECT * FROM [tbl_KGA_MEMBER_DETAILS] WHERE [F_Name] LIKE '" & myLetter _
        & "%' ORDER BY [F_Name] ASC"
Note using string concatenation is a huge sql injection risk, but I don't remember if you can use parameterised queries with VB6 (and I haven't had it installed for years).

If you were writing the query in Access you would use * for the wildcard but using OleDb I think you need to use the sql-like '%' for the wildcard. You could always removed the WHERE altogether until you track down the problem.

If that doesn't work try putting
before the loop

If that still doesn't work then look again at your connection string. Try
Set DBcON = New Connection
Dim strConn As String
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbFile & ";"
    conn.Open (strConn)

Finally, you have not said where App.Path is - if that is (e.g.) Program Files (x86) then any data added to the database will probably be in the users AppData folder and not in the App.Path (Assuming you are using a version of Windows >= Vista) - try moving the database to an explicit folder (like C:\temp for example)
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900