Access with VB III

I have this code that point to a database, the submit is working fine, I am working on the Search.

If I type the firstname in the form and click search, I want it to find it and all other fields and display in the form fields.

Private Sub Command1_Click()
Dim db As Database
  Dim rstData As Recordset
  Dim strSQL As String
  Set db = OpenDatabase("C:\ps\employees.mdb")
  strSQL = "SELECT * FROM Contacts"
  Set rstData = db.OpenRecordset(strSQL)
  rstData.AddNew
  rstData!firstname = txtFirstName.Text & ""
  rstData!lastname = txtLastName.Text & ""
  rstData!workphone = txtWorkPhone.Text & ""
  rstData!emailaddress = txtEmailAddress.Text & ""
  rstData.Update
  rstData.Close
  db.Close
End Sub

Private Sub cmdClear_Click()
    Dim ctl As Control
    For Each ctl In Me.Controls
        If TypeOf ctl Is TextBox Then
            ctl.Text = ""
        End If
    Next
End Sub

Private Sub cmdSearch_Click()
  Dim db As Database
  Dim rstData As Recordset
  Dim strSQL As String
  Set db = OpenDatabase("C:\ps\employees.mdb")
  'substitute whatever field you want to search on where I used first_name below
  strSQL = "SELECT * FROM Contacts WHERE firstname LIKE ' " & txtFirstName.Text & " ' "
  Set rstData = db.OpenRecordset(strSQL)
 If Not rstData.EOF Then
 'results were returned, loop through them and do something with them
End If
rstData.Close
db.Close
End Sub
CMILLERAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PaulHewsCommented:
The thing is, what do you want to do when there's more than one with that first name?
CMILLERAuthor Commented:
can it scroll, until I find the one I was looking for?

or would I need a search to start finding a person and then have a next button to scroll
PaulHewsCommented:
Ok, is the Email field the primary key here?
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

CMILLERAuthor Commented:
well, currently there is not one, I could not get the code to work with one ( Primary Key ).

I had a feeling this might mess me up ( not having a primary key )
PaulHewsCommented:
Pretty much every table should have a primary key.  Email is a good choice, since email is always unique... You can set the primary key in by opening the table in design mode, selecting the Email column, then click the key button on the toolbar.  It shouldn't mess anything up, and it will help us select a user from a list later (I'll code that up for you in a bit.)
CMILLERAuthor Commented:
ok, I will try to set up a primary key
CMILLERAuthor Commented:
I set the email address as primary key
PaulHewsCommented:
Ok, now you will need a new form.  Name it frmSearch in the properties.  Add a listbox and call it lstUsers.  Add two command buttons and call them cmdOK and cmdCancel.

Now add this code for the new form:
Option Explicit

Private mCancel As Boolean

Private Sub cmdCancel_Click()
    mCancel = True
    Me.Hide
End Sub

Private Sub cmdOK_Click()
    mCancel = False
    Me.Hide
End Sub




Public Function GetSelectedEmail() As String
    Dim strUserString As String
    Dim strFields() As String
   
    If lstUsers.ListIndex > -1 Then
        strUserString = lstUsers.List(lstUsers.ListIndex)
        strFields = Split(strUserString, ", ")
        GetSelectedEmail = strFields(2)  'return the email, which is the last field
    Else
        GetSelectedEmail = ""
    End If
   
End Function


Public Sub PassRS(rs As Recordset)
    Do While Not rs.EOF
        lstUsers.AddItem rs("Lastname") & ", " & rs("Firstname") & ", " & rs("Email")
        rs.MoveNext
    Loop
   
   
End Sub

Public Property Get Cancel() As Boolean
    Cancel = mCancel
End Property

Public Property Let Cancel(NewValue As Boolean)
    mCancel = NewValue
End Property


Now Add this code to your cmdSearch button:

Private Sub cmdSearch_Click()
      Dim db As Database
      Dim rstData As Recordset
      Dim rsMult As Recordset
      Dim rsSingle As Recordset
      Dim strSQL As String
     
      Set db = OpenDatabase("C:\ps\employees.mdb")
     
      strSQL = "Select Count(Email) As CountReturn From Contacts WHERE FirstName LIKE '*" & txtFirstName.Text & "*'"
      Set rstData = db.OpenRecordset(strSQL, dbOpenSnapshot)
     
      Select Case rstData("CountReturn")
      Case 0
        MsgBox "No contacts found with first name = " & txtFirstName.Text
       
      Case 1  'Single contact found
            strSQL = "SELECT * FROM Contacts WHERE FirstName LIKE '*" & txtFirstName.Text & "*'"
            Set rsSingle = db.OpenRecordset(strSQL, dbOpenSnapshot)
            If Not rsSingle.EOF Then
                txtFirstName.Text = rsSingle("FirstName")
                txtLastName.Text = rsSingle("LastName")
                txtWorkPhone.Text = rsSingle("WorkPhone")
                txtEmail.Text = rsSingle("Email")
            End If
            rsSingle.Close
           
      Case Is > 1  'multiple contacts found, show the list
           
           
            strSQL = "SELECT * FROM Contacts WHERE FirstName LIKE '*" & txtFirstName.Text & "*'"
            Set rsSingle = db.OpenRecordset(strSQL)
            If Not rsSingle.EOF Then
                frmSearch.PassRS rsSingle
                frmSearch.Show vbModal
                rsSingle.Close
                If frmSearch.Cancel = False Then
                    strSQL = "SELECT * FROM Contacts WHERE Email = '" & frmSearch.GetSelectedEmail & "'"
                    Set rsSingle = db.OpenRecordset(strSQL, dbOpenSnapshot)
                    If Not rsSingle.EOF Then
                        txtFirstName.Text = rsSingle("FirstName")
                        txtLastName.Text = rsSingle("LastName")
                        txtWorkPhone.Text = rsSingle("WorkPhone")
                        txtEmail.Text = rsSingle("Email")
                    End If
                    rsSingle.Close
                   
                End If
                Unload frmSearch
            End If
      End Select

    rstData.Close
    db.Close
End Sub
CMILLERAuthor Commented:
I am getting an error here:

Public Sub PassRS(rs As Recordset)
PaulHewsCommented:
What is the error?
CMILLERAuthor Commented:
It has a compile error:

user-defined type not defined
PaulHewsCommented:
Do you have a reference to Microsoft DAO in your project properties?
BarshamDevelopment Team LeadCommented:
you must use Project ->Reference Menu to locate Microsoft ActiveX Data Object 2.x Library

x is number like 2.1 or 2.6
depend on your database that u want use jet 3.5 or 4.0 :)
CMILLERAuthor Commented:
I added the Microsoft DAO, it let me compile.

so, I will have to programs:

members.exe and search.exe

they both need to be open?
CMILLERAuthor Commented:
I have both open, and I type an email address that I know is in the database, click search.

I get a runtime error: 3061, to few parameters. Expected 1

should there be anything in the search.exe listbox?
CMILLERAuthor Commented:
I have to leave for the weekend, have a good one, talk to you next week.
PaulHewsCommented:
It isn't two programs.  It's just a form to add to your existing program...  Your existing program should already have that reference.
PaulHewsCommented:
Sure, have a good weekend.  :)
BarshamDevelopment Team LeadCommented:
=))
ironwill96Commented:
CMILLER,

If you look in the code that PaulHews provided here:

 If lstUsers.ListIndex > -1 Then
        strUserString = lstUsers.List(lstUsers.ListIndex)
        strFields = Split(strUserString, ", ")
        GetSelectedEmail = strFields(2)  'return the email, which is the last field
    Else
        GetSelectedEmail = ""
    End If

You will notice that he is checking linstindex of > -1 and if that is not true he sets GetSelectedEmail to a blank string.  You are getting this error because your listbox is empty.  Your listbox must contain some data and have something selected in it for that query to work.  -1 in a listindex indicates that the listbox does not have anything selected yet.

Nathan
CMILLERAuthor Commented:
I moved the Search form into the project, now both forms are in the same project.

What should I have this set to:

this one, Microsoft DAO or this one, Microsoft ActiveX Data Object 2.x Library or both?

I have it only set to this:
Microsoft DAO

here is what is in the database: ( along with a few others just to have more than one in it )
firstname: Chris
lastname: Miller
workphone:123
emailaddress:chris.miller @ somewhere . com


If I type anything in any field and click search I get this error:
3061, to few parameters. Expected 1

and this in yellow: Set rstData = db.OpenRecordset(strSQL, dbOpenSnapshot)

If I have this set:
Microsoft ActiveX Data Object 2.x Library

If I type anything in any field and click search I get this error:

Compile error: user-defined type not defined
Dim db As Database

and this in yellow: Private Sub cmdSearch_Click()
*************************************************
Ironwill96,

so, are you saying it should be ( 1 ), If so I tried it still the same error
PaulHewsCommented:
Microsoft DAO is what your project was using, so that is what I coded my addition to yours for.

>If I type anything in any field and click search I get this error:
The message about too few parameters means that something in the query is not being recognized as a database object.  Double check that the name of the table is Contacts (and not Contact) and post the names of the fields.  Also, the search is for firstname, as that was what was specified.  If you have nothing in the firstname textbox, you will get a list showing all the users.

Here is the test harness project that I build when I was coding it.  It's working for me against a simple test database with just the contacts table:
http://www11.brinkster.com/notbono/download.asp?txtFile=SampleDB.zip



CMILLERAuthor Commented:
Paul,

I used your zip file.

I still get this error:  3061, to few parameters. Expected 1

CMILLERAuthor Commented:
the table is " Contacts "
CMILLERAuthor Commented:
Firstname, Lastname, Workphone, Emailaddress

I noticed you had Email, I changed all to Emailaddress
CMILLERAuthor Commented:
It errors on the line below

Case 1  'Single contact found
            strSQL = "SELECT * FROM Contacts WHERE FirstName LIKE '*" & txtFirstName.Text & "*'"
            Set rsSingle = db.OpenRecordset(strSQL, dbOpenSnapshot)
            If Not rsSingle.EOF Then
                txtFirstName.Text = rsSingle("FirstName")
                txtLastName.Text = rsSingle("LastName")
                txtWorkPhone.Text = rsSingle("WorkPhone")
 <<ERROR>> txtEmailaddress.Text = rsSingle("Emailaddress")
PaulHewsCommented:
Aha, I see the problem.  Where I have "Email", you will need to put "EmailAddress"

'frmSearch code
Option Explicit

Private mCancel As Boolean

Private Sub cmdCancel_Click()
    mCancel = True
    Me.Hide
End Sub

Private Sub cmdOK_Click()
    mCancel = False
    Me.Hide
End Sub




Public Function GetSelectedEmail() As String
    Dim strUserString As String
    Dim strFields() As String
   
    If lstUsers.ListIndex > -1 Then
        strUserString = lstUsers.List(lstUsers.ListIndex)
        strFields = Split(strUserString, ", ")
        GetSelectedEmail = strFields(2)  'return the email, which is the last field
    Else
        GetSelectedEmail = ""
    End If
   
End Function


Public Sub PassRS(rs As Recordset)
    Do While Not rs.EOF
        lstUsers.AddItem rs("Lastname") & ", " & rs("Firstname") & ", " & rs("EmailAddress")
        rs.MoveNext
    Loop
   
   
End Sub

Public Property Get Cancel() As Boolean
    Cancel = mCancel
End Property

Public Property Let Cancel(NewValue As Boolean)
    mCancel = NewValue
End Property


Now Add this code to your cmdSearch button:

Private Sub cmdSearch_Click()
      Dim db As Database
      Dim rstData As Recordset
      Dim rsMult As Recordset
      Dim rsSingle As Recordset
      Dim strSQL As String
     
      Set db = OpenDatabase("C:\ps\employees.mdb")
     
      strSQL = "Select Count(EmailAddress) As CountReturn From Contacts WHERE FirstName LIKE '*" & txtFirstName.Text & "*'"
      Set rstData = db.OpenRecordset(strSQL, dbOpenSnapshot)
     
      Select Case rstData("CountReturn")
      Case 0
        MsgBox "No contacts found with first name = " & txtFirstName.Text
       
      Case 1  'Single contact found
            strSQL = "SELECT * FROM Contacts WHERE FirstName LIKE '*" & txtFirstName.Text & "*'"
            Set rsSingle = db.OpenRecordset(strSQL, dbOpenSnapshot)
            If Not rsSingle.EOF Then
                txtFirstName.Text = rsSingle("FirstName")
                txtLastName.Text = rsSingle("LastName")
                txtWorkPhone.Text = rsSingle("WorkPhone")
                txtEmailAddress.Text = rsSingle("EmailAddress")
            End If
            rsSingle.Close
           
      Case Is > 1  'multiple contacts found, show the list
           
           
            strSQL = "SELECT * FROM Contacts WHERE FirstName LIKE '*" & txtFirstName.Text & "*'"
            Set rsSingle = db.OpenRecordset(strSQL)
            If Not rsSingle.EOF Then
                frmSearch.PassRS rsSingle
                frmSearch.Show vbModal
                rsSingle.Close
                If frmSearch.Cancel = False Then
                    strSQL = "SELECT * FROM Contacts WHERE EmailAddress = '" & frmSearch.GetSelectedEmail & "'"
                    Set rsSingle = db.OpenRecordset(strSQL, dbOpenSnapshot)
                    If Not rsSingle.EOF Then
                        txtFirstName.Text = rsSingle("FirstName")
                        txtLastName.Text = rsSingle("LastName")
                        txtWorkPhone.Text = rsSingle("WorkPhone")
                        txtEmailAddress.Text = rsSingle("EmailAddress")
                    End If
                    rsSingle.Close
                   
                End If
                Unload frmSearch
            End If
      End Select

    rstData.Close
    db.Close
End Sub
CMILLERAuthor Commented:
I changed it in the database to just email , and it is working
PaulHewsCommented:
I assume that the field is called "EmailAddress" instead of "Email" as I first thought.
PaulHewsCommented:
> I changed it in the database to just email , and it is working

OK, you could do that too.  :)
CMILLERAuthor Commented:
Paul,

I just got what the Ok and Cancel buttons were for. I entered two names of the same, and thats how and why the list box
opens. I guess at first I didnt understand the ok and cancel buttons.

CMILLERAuthor Commented:
Paul,

I noticed on the search, that if there is more than one match, and the list box opens, if you click cancel, and click search again
it adds the same names again. It keeps doubling them. It will always continue to do this.
PaulHewsCommented:
This will fix that:

PPrivate Sub cmdSearch_Click()
      Dim db As Database
      Dim rstData As Recordset
      Dim rsMult As Recordset
      Dim rsSingle As Recordset
      Dim strSQL As String
     
      Set db = OpenDatabase("C:\ps\employees.mdb")
     
      strSQL = "Select Count(Email) As CountReturn From Contacts WHERE FirstName LIKE '*" & txtFirstName.Text & "*'"
      Set rstData = db.OpenRecordset(strSQL, dbOpenSnapshot)
     
      Select Case rstData("CountReturn")
      Case 0
        MsgBox "No contacts found with first name = " & txtFirstName.Text
       
      Case 1  'Single contact found
            strSQL = "SELECT * FROM Contacts WHERE FirstName LIKE '*" & txtFirstName.Text & "*'"
            Set rsSingle = db.OpenRecordset(strSQL, dbOpenSnapshot)
            If Not rsSingle.EOF Then
                txtFirstName.Text = rsSingle("FirstName")
                txtLastName.Text = rsSingle("LastName")
                txtWorkPhone.Text = rsSingle("WorkPhone")
                txtEmail.Text = rsSingle("Email")
            End If
            rsSingle.Close
           
      Case Is > 1  'multiple contacts found, show the list
           
           
            strSQL = "SELECT * FROM Contacts WHERE FirstName LIKE '*" & txtFirstName.Text & "*'"
            Set rsSingle = db.OpenRecordset(strSQL)
            If Not rsSingle.EOF Then
                frmSearch.PassRS rsSingle
                frmSearch.Show vbModal
                rsSingle.Close
                If frmSearch.Cancel = False Then
                    strSQL = "SELECT * FROM Contacts WHERE Email = '" & frmSearch.GetSelectedEmail & "'"
                    Set rsSingle = db.OpenRecordset(strSQL, dbOpenSnapshot)
                    If Not rsSingle.EOF Then
                        txtFirstName.Text = rsSingle("FirstName")
                        txtLastName.Text = rsSingle("LastName")
                        txtWorkPhone.Text = rsSingle("WorkPhone")
                        txtEmail.Text = rsSingle("Email")
                    End If
                    rsSingle.Close
                   
                End If
                Unload frmSearch  '<<< Add this to make sure form unloads
            End If
           
      End Select

    rstData.Close
    db.Close
End Sub

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CMILLERAuthor Commented:
Yep, it works.

Thanks
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.