[RESOLVED] VB6 Help for Searching

[Raaj71]

Hi All !

I am a beginner and learning VB6 programming as a hobby.

I have made a small program for storing names and address just like address book.

Program is working fine. Am able to access, add, edit and delete the contents of my database through coding.

After i reviewed it, I found something missing. A program is not complete if it can't search the database, so i added a search button to it.

I have few text boxes on the form which got filled automatically with the last record when the form is loaded. All buttons i.e. First, Last, Next, Previous works fine and show appropriate records of database.

I have added a textbox to collect the string for search operations. When FIND button is clicked, it shows all the records according to my search criteria. Now what happens is the data base file got limited to search results and functionality to NEXT, PREVIOUS, FIRST AND LAST buttons show only the searched records. (I assume it is creating another instance of database on my search criteria).

Here is the code :

--------------------------------------------------------------------------
Private Sub Form_Load()
    Set AddCn = New ADODB.Connection
    AddCn.Provider = "Microsoft.Jet.OLEDB.4.0; Data Source = " + App.Path & "\Address Book.Mdb"
    AddCn.Open
    Set AddRs = New ADODB.Recordset
    With AddRs
        .Open "Address", AddCn, adOpenDynamic, adLockPessimistic, adCmdTable
        If Not (.EOF And .BOF) Then
            AddRs.MoveLast
            FillFields
            DisFields
        End If
    End With
    EnbNavButt
End Sub
 
--------------------------------------------------------------------------

Private Sub cmdFind_Click()
    AddRs.Close
    txtSearch.Text = Trim$(txtSearch.Text)
    AddRs.Open "SELECT * FROM Address WHERE First_Name LIKE ('" & txtSearch.Text & "%')", AddCn, adOpenStatic, adLockOptimistic
    If AddRs.EOF = True Or AddRs.EOF = True Then
        MsgBox ("No Record Exists")
        Exit Sub
    Else
        FillFields
    End If
End Sub
--------------------------------------------------------------------------

Now, if i want to refer to the first record of the database, it shows the first searched record and my navigation buttons works fine but are restricted to show the searched records.

What could be the reason ? Anyone can help please ?

[DataMiser]

Once you execute the select where statement your recordset contains only the items returned in that select. To get all the records you would need to issue another select without that where clause.

[Raaj71]

Thanks for your reply and let me correct my mistake as follows :

Private Sub Form_Load()
        Set AddCn = New ADODB.Connection
        AddCn.Provider = "Microsoft.Jet.OLEDB.4.0; Data Source = " + App.Path & "\Address Book.Mdb"
        AddCn.Open
        Set AddRs = New ADODB.Recordset
        With AddRs
                .Open "Address", AddCn, adOpenDynamic, adLockPessimistic, adCmdTable
                If Not (.EOF And .BOF) Then
                        AddRs.MoveLast
                        FillFields
                        DisFields
                End If
        End With
        EnbNavButt
End Sub

--------------------------------------------------------------------------

Private Sub cmdFind_Click()
        AddRs.Close
        txtSearch.Text = Trim$(txtSearch.Text)
        AddRs.Open "SELECT * FROM Address WHERE First_Name LIKE ('" & txtSearch.Text & "%')", AddCn, adOpenStatic, adLockOptimistic
        If AddRs.EOF = True Or AddRs.EOF = True Then
                MsgBox ("No Record Exists")
                Exit Sub
        Else
                FillFields
        End If
End Sub

Your reply is very much clear but i have a little doubt. Please excuse me.

Where should I write SELECT command again ? Shall i give a command button [CLEAR SEARCH] to clear search criteria and code it with SELECT * from <table> or should i used it somewhere else?

I need help on one more issue.

I want to break execution of the program using escape key. In fact i do not want the program to exit or form to unload (since i have only one form because i have an EXIT button to stop program) when escape is pressed but i want it to break execution and return the program to the initial start up.

How should i code this ?

[dglienna]

Take a look at my article. It includes a sample program that should help. Here

[DataMiser]

Where you put the select statement is really up to you and depends on what exactly you want the program to do.

Not sure I understand the other question about the esc

[dglienna]

You can pause your program, but ESCAPE only works *sometimes*. You can't really stop a SQL Query, only AFTER it executes (before it populates your form)

[Raaj71]

Thank You Friends. Its working fine now.

[Raaj71]

Once again thanks for evaluating my code deeply and suggesting me tips and techniques.

I was making this one as an testing program to check my knowledge and skills. I intend to make a program for my business through which I will track Orders Received, Order Processing Status, Delivery Date, Advance Received and Balance due.

Now I am done with testing part i.e. accessing more than one table from vb, it was real troublesome but any how i learnt it with help of you people online, who shared their knowledge and guided me with detailed answers, checking my code.

Am all set to start my project. I just wanted to know, if I am allowed to start a new topic and share my day to day progress (starting from scratch) regarding that project. I guess, It will be helping me and many users like me who are learning or want to learn VB6 programming.

Am expecting to learn various newt things this way. Many of you experts have done/been doing it for long (and may have invented various things which you would like to share with inexperienced people) and if am going wrong somewhere, I can get help from your experience?

Regards