Results 1 to 2 of 2
  1. #1
    Join Date
    May 2006
    Posts
    1

    Question Unanswered: searching access db...

    I can't seem to find search function of database in the forum, so I'm posting this.

    I'm using VB6 and Access file (97 format) as my customer database

    One of the main thing I need the program to do is searching for customer name or phone numbers

    Here's a part of finding customer name
    Code:
                ElseIf optCompany.Value = True Then
                    strSearch = "CompanyName=" & Chr(34) & UCase(txtSearch.Text) & Chr(34)
                    rsCustomer.FindFirst strSearch
                                If rsCustomer.NoMatch = True Then
                                    Call NoRecord
                                    
                                Else
                                        lstResults.AddItem rsCustomer!CompanyName
                                        lblInfo(0).Caption = "Address: " & rsCustomer!Address
                                        lblInfo(1).Caption = "City: " & rsCustomer!City
                                        lblInfo(2).Caption = "Postal Code: " & rsCustomer!PostalCode
                                        lblInfo(3).Caption = "Phone: " & rsCustomer!PhoneNum
                                        lblInfo(4).Caption = "Phone #2/Extension: " & rsCustomer!Ext
                                        lblInfo(5).Caption = "Fax #: " & rsCustomer!Fax
                                        lblInfo(6).Caption = "Products: " & vbCrLf & rsCustomer!Products
                                        lblInfo(7).Caption = "Contact Name: " & rsCustomer!ContactName
                                        
                                End If
    I want all the available results to the listbox and display the info of chosen company (which I already got it working from another form)

    It's keep showing "Runtime Error-91 Object variable or with block not set"

    Also, how can I search only a part of field (ie. I search for Nacho and there are two customer's with Nacho in it 1) Nacho's Place 2) Tacho Nacho) instead of getting the exact valid name?

    Thank you

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Quote Originally Posted by tml240
    I can't seem to find search function of database in the forum, so I'm posting this.

    I'm using VB6 and Access file (97 format) as my customer database

    One of the main thing I need the program to do is searching for customer name or phone numbers

    Here's a part of finding customer name
    Code:
                ElseIf optCompany.Value = True Then
                    strSearch = "CompanyName=" & Chr(34) & UCase(txtSearch.Text) & Chr(34)
                    rsCustomer.FindFirst strSearch
                                If rsCustomer.NoMatch = True Then
                                    Call NoRecord
                                    
                                Else
                                        lstResults.AddItem rsCustomer!CompanyName
                                        lblInfo(0).Caption = "Address: " & rsCustomer!Address
                                        lblInfo(1).Caption = "City: " & rsCustomer!City
                                        lblInfo(2).Caption = "Postal Code: " & rsCustomer!PostalCode
                                        lblInfo(3).Caption = "Phone: " & rsCustomer!PhoneNum
                                        lblInfo(4).Caption = "Phone #2/Extension: " & rsCustomer!Ext
                                        lblInfo(5).Caption = "Fax #: " & rsCustomer!Fax
                                        lblInfo(6).Caption = "Products: " & vbCrLf & rsCustomer!Products
                                        lblInfo(7).Caption = "Contact Name: " & rsCustomer!ContactName
                                        
                                End If
    I want all the available results to the listbox and display the info of chosen company (which I already got it working from another form)

    It's keep showing "Runtime Error-91 Object variable or with block not set"

    Also, how can I search only a part of field (ie. I search for Nacho and there are two customer's with Nacho in it 1) Nacho's Place 2) Tacho Nacho) instead of getting the exact valid name?

    Thank you
    Instead of opening a recordset, (Which, BTW, I'm not sure you've done...) and searching the recordset, let SQL do the work for you.

    Open a recordset with the search criteria being used in the SQL query, and then, if the rs ie empty, either rs.addnew, or, use SQL to INSERT a new record directly.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •