Solved

List box does not work properly

Posted on 2004-10-08
10
192 Views
Last Modified: 2010-05-02
Please help:

I'm trying to get a list box to display the results of a loop.  The box does not list anything.  I've reviewed the syntax, and I cannot find what I'm doing wrong.

The box will not populate no matter what I do, even when I tell it to add a static entry.  I've tried both normal and db controls, updated my components, and other solutions.

I've even borrowed sample code and forms from other projects with working list boxes, yet when I insert them into my own project, they fail to work.

Please help.

Private Sub SearchRecords_Click()
     
     dbConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Documents and Settings\USER1\Desktop\tsm project files\test project BUILD 2.7\test1.mdb;" & _
        "Persist Security Info=False" & _
        "Initial Catalog = patient;"
     dbConnection.Open
     Dim findStr As String
    If Not DataEnvironment1.rsPatient.Supports(adFind) Then
        MsgBox "Recordset doesn't support the FIND method"
    Else
        If DataEnvironment1.rsPatient.Supports(adBookmark) Then
           lastrow = DataEnvironment1.rsPatient.Bookmark
        End If
       
        findStr = LastName
        If findStr = "" Then Exit Sub
        DataEnvironment1.rsPatient.MoveFirst
        List1.Clear
        DataEnvironment1.rsPatient.Find "[Last Name] LIKE '*" & findStr & "*'"
   
        If DataEnvironment1.rsPatient.EOF Then
            MsgBox "Could not find any matching records. " & findStr
            If DataEnvironment1.rsPatient.Supports(adBookmark) Then
                DataEnvironment1.rsPatient.MoveFirst
                    While DataEnvironment1.rsPatient.Fields("Last Name") = LastName
                    List1.AddItem DataEnvironment1.rsPatient.Fields("Last Name")
                    DataEnvironment1.rsPatient.MoveNext
                    If DataEnvironment1.rsPatient.EOF Then Exit Sub
                    Wend
            End If
        Else
             frmPatientAdd.Show
           
        End If
    End If
0
Comment
Question by:antaren
  • 3
  • 2
  • 2
  • +1
10 Comments
 
LVL 8

Accepted Solution

by:
mladenovicz earned 168 total points
ID: 12258985
This is a problem

 If DataEnvironment1.rsPatient.EOF Then
0
 
LVL 8

Expert Comment

by:mladenovicz
ID: 12259003
You will populate list only if EOF is true. If eof is True, there are no records
0
 
LVL 8

Expert Comment

by:mladenovicz
ID: 12259032
You will have to reorganize this code

        If DataEnvironment1.rsPatient.EOF Then
            MsgBox "Could not find any matching records. " & findStr
            '-- CAN NOT PROCEEDE HERE
            If DataEnvironment1.rsPatient.Supports(adBookmark) Then
                DataEnvironment1.rsPatient.MoveFirst
                 While DataEnvironment1.rsPatient.Fields("Last Name") = LastName
                    List1.AddItem DataEnvironment1.rsPatient.Fields("Last Name")
                    DataEnvironment1.rsPatient.MoveNext
                    If DataEnvironment1.rsPatient.EOF Then Exit Sub
                 Wend
            End If
        Else
             frmPatientAdd.Show
         
        End If
0
 
LVL 3

Expert Comment

by:thunder_moose
ID: 12259549
Hey, I would move the entire

            If DataEnvironment1.rsPatient.Supports(adBookmark) Then
                DataEnvironment1.rsPatient.MoveFirst
                 While DataEnvironment1.rsPatient.Fields("Last Name") = LastName
                    List1.AddItem DataEnvironment1.rsPatient.Fields("Last Name")
                    DataEnvironment1.rsPatient.MoveNext
                    If DataEnvironment1.rsPatient.EOF Then Exit Sub
                 Wend
            End If

To the Else section of the code, before frmPatientAdd.Show. Like one of the previous posters mentioned, the code that adds to the listbox will only be executed when rsPatient is EOF, so there will be no records to add.

TM
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 18

Assisted Solution

by:mdougan
mdougan earned 166 total points
ID: 12259748
Hi antaren,
Yes, what mladenovicz says is correct... but even after you correct that, you'll still have a problem.  In the Find statement, you're looking for any last name "like" the find string..., but then later you are doing a while last name =

You'd need to change this line:
While DataEnvironment1.rsPatient.Fields("Last Name") = LastName

To
While Instr(1, DataEnvironment1.rsPatient.Fields("Last Name"), LastName) > -1

This will look for LastName inside of the entire string for the field "Last Name"

If you want an exact match on last name, then you should not do the Find with a LIKE, but rather with an = (and no wildcards)

Also, Find will always find the first record with that criteria... but it won't ensure that all the records that might qualify for that criteria are then sorted one after another, as your While loop suggests.  To do that, you'd probably want to set a Filter on the recordset (which may or may not be available depending on the type of recordset your are creating.  Example, say that you've sorted your data set on Last Name, your results could look like

Johnstone
Smith
Stone
Taft
Therstoner

Your find would always first bring back Johnstone... then, your movenext is going to bring back Smith... then, you're going to exit out... if you filter, then only Johnstone, Stone and Therstoner will be in the list... and you can simply movefirst, and then do a while not .EOF  (this is if your type of recordset has the .filter property.  If not, then it's best just to do a Select statement instead of setting the recordsource to being the whole table...

Select * from mytable where [last name] like '*stone*'

That will bring back a recordset with only the qualified rows.





Cheers!
0
 
LVL 27

Expert Comment

by:Ark
ID: 12344536
For LIKE clause you should use % instead of *
0
 
LVL 18

Expert Comment

by:mdougan
ID: 12350479
Since Anteren's connect string specifies a Microsoft Access database, the asterisk might be correct for the wildcard character... I believe that Access SQL required that character up to a certain point... later versions of access might require the % which is consistent with all other database standards.
0
 
LVL 27

Assisted Solution

by:Ark
Ark earned 166 total points
ID: 12444958
Hello mdougan

'===quote from  http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnima01/html/ima0601.asp ===

[b]The ADO monkey wrench[/b]
Though Access supports SQL statements, Access SQL doesn't completely adhere to ANSI SQL standards. In Access 97, Access SQL most closely resembles the SQL-89 standard. However, with the release of Jet 4.0 in Access 2000, Access SQL was updated to more closely emulate the SQL-92 standard through syntax exposed by ADO and the Microsoft Jet OLE DB Provider. This impacts many aspects of using SQL statements in code, one of which is wildcard behavior. The pattern-matching characters we looked at last month were provided through DAO. Rather than using the asterisk (*) and question mark (?) symbols as wildcards, ADO requires that you use the percent sign (%) to match multiple characters and the underscore (_) to match a single character.

'========================end quote===========

Cheers
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now