Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2004
    Posts
    20

    Question Unanswered: searching with text field

    Hello,
    Im very new to Access (using version 2002), and Im trying to figure out how to search my table with a text field...
    For simplicity, say I have a table which has columns "Name" and "Age"
    I know that I can create a form and make a text field, but I have no idea
    how to make it so what I type in that text field is what I search for.

    What I ultimately hope to have is a form that looks like this:
    <combo box> <text field> <submit search button>

    where the combo box has the columns (in this case "Name" and "Age") and
    depending on what I used for my combo box, the text field will search that
    column for the value I entered.

    If someone could help me, I would greatly appreciate it!
    Thank you,
    nullptr

  2. #2
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Okay so you have a form and on that form you have a combo box and a text field.

    The combo box includes the following 2 rows of information: Name, Age

    Is that so far correct?

    The text box you want to search the table based on what is selected in the combo right?

    Such as if Name is selected, you want to be able to type Smith in the text box and it comes up with all of the entries containing Smith as the last name?

    Okay to do that you will have to write some sql and some vba do you know what either of those are?

    Let me know,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  3. #3
    Join Date
    Jun 2004
    Posts
    20
    Hi JS,

    Thanks for the reply. So far what you said is correct. I also wanted a button to submit the search. I know some SQL since Ive worked very briefly with it, but I dont know any VBA ...
    but Im trying my best to learn.

    Thanks so much for the help,
    nullptr

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    HI

    Is this the sort of thing you are looking for?

    Code:
    Private Sub txtFind_AfterUpdate()
        Dim Bkmrk As Variant
        Dim Criteria as string
    
        If nz(txtFind,””)=”” then exit sub
    
        Bkmrk = Me.Bookmark
        
        If cboSerchTyp = “Name”
            Criteria = “NameFieldName Like ‘*” & txtFind & “*’”
        Else
            Criteria = “AgeFieldName = ‘” & txtFind & “’”
        End If
    
        Me.RecordsetClone.FindFirst Criteria
    
        If Me.RecordsetClone.NoMatch = True Then
            Me.Bookmark = Bkmrk
            MsgBox "No record found!", vbExclamation, “Title”
        Else
            If isnumeric(txtFind) then
                Me.Bookmark = Me.RecordsetClone.Bookmark
            Else
                MsgBox “Age must be numeric!” !", vbExclamation,”Title”
                Exit Sub
            End If
        End If
    
    End Sub

    Just an idea

    Usually you would normally store birthdays in the DB as Age changes each year!!


    MTB

  5. #5
    Join Date
    Jun 2004
    Posts
    20
    I tried the code but it didnt seem to work.
    This gave me an error:
    Code:
        If cboSerchTyp = “Name”
    I tried changing it to this (and I still got an error):
    Code:
        If cboSerchType = “Name”
    by "Name" did you mean, "NameFieldName" ?
    cause Im not sure what "Name" represents in this case.

    This also gave an error:
    Code:
                MsgBox “Age must be numeric!” !", vbExclamation,”Title”
    but if I change it to this the error goes away:
    Code:
                MsgBox "Age must be numeric!", vbExclamation,”Title”
    is that what you meant?

    could you explain what you meant by "Name"? I think I understand this all except that line...

    thanks a lot for the help,
    nullptr
    Last edited by nullptr; 08-19-04 at 15:09.

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I assumed you have a combo control with two rows containing “Name” and “Age” to indicate which field to search. Whichever is selected gives the combo control a value of either “Name” or “Age”.

    This can then be check ie

    If cboCotrol = “Name” then etc. and then use the appropriate field name to constructed search criteria as required, as indicated in the previous code.

    However if the comboControl contains the actual FieldName then it is even simpler

    Then the Criteria would be

    Criteria = cboSearchType & “ = “ & txtFind

    However if Age and Name fields are not the same types ie. both Text, then the Criteria will need different syntax, also as previously indicated. Therefore you will need to know which field is being searched.

    Sorry about the typo in the code but I was interrupted when editing (work does get in the way!).

    May be I have interpreted your requirements, if so, sorry.


    MTB

  7. #7
    Join Date
    Jun 2004
    Posts
    20
    oh no you havent. Im just new at VBA here. Im understanding this now though. Thank you very much!

Posting Permissions

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