Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2009
    Posts
    4

    Unanswered: Access Search function

    Hi,

    I'm a complete beginner using Access, with very little computer programming knowledge & I've been asked to makea database for the company I'm working for.

    I've currently made a simple (but probably very messy) database which has stored company, plant location and contact ID.

    The form I have has the Company name & ID (from Company ID table) as the main form, and then all the info from the Plant Info table, and Contact Info table as 2 subforms.

    I'm trying to add a search bar as a header so I can search through contact name & ID, plant name & ID and also company name & ID (i.e the subforms and main form), then locate it & take me to it.

    I've spent days trying code & now my database is a mess...

    Any help would be greatly appreciated, and appologies if this is vague but i really don't know what to write to be as helpfull as possible...

    Albi

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You do not supply enough information to deliver a complete solution to your problem. However these are some elements that could help you:

    1) In a well organized database each table has a column that is a unique identifier for each row. It can be the primary key or, at least an indexed column with a UNIQUE constraint. This entails that two rows in the table cannot be exactly the same (at least the unique identifier column differenciates them).

    If your database lacks such U.I. (Unique Identifier) it's not too late. Open each table in design view, create a new column, name it something like Primary, U_Identifier, SysCounter, PK or anything you want, set its data type to AutoNumber and made it the primary key (if there is none already) or create an UNIQUE (no duplicates) index on it.

    2) The most common mechanism used for searching for a specific record in a form is as follows:
    a) When bound to a data source, a form has an underlying recordset. The first step consists in creating a clone (a photocopy if you want) of this recordset. Therefore we use the RecordsertClone method:
    Code:
    Dim rst as DAO.Recordset
    Set rst = Me.RecordsetClone
    b) We will perform the search on this clone:
    Code:
    Dim strCriteria as string
    strCriteria = "FirstName Like '" & Combo_FirstName.Value & "'"
    rst.FindFirst strCriteria
    c) If a record matching the criteria is found then we can use the Bookmark property of both recordsets (the one of the form and the clone we created) to move the current record of the form to the one that was found in the clone:
    Code:
    If rst.NoMatch = False then
        Me.Bookmark = rst.Bookmark
    Else
        ' Handle Not Found situation
    End If
    Set rst = Nothing    ' Clean up
    Altogether:
    Code:
    Private Sub Combo_FirstName_AfterUpdate()
        Dim rst as DAO.Recordset
        Dim strCriteria as string
        Set rst = Me.RecordsetClone
        strCriteria = "FirstName Like '" & Combo_FirstName.Value & "'"
        rst.FindFirst strCriteria
        If rst.NoMatch = False then
            Me.Bookmark = rst.Bookmark
        Else
            MsgBox "Cannot found " & Me.Combo_FirstName.Value, vbInformation, "No match"
        End If
        Set rst = Nothing
    End Sub
    3) If in a form you know the unique identifier of a record in another form, you can use the same method as above except that you have to address the target form, so you will use:
    Forms!TargetForm. instead of Me.
    or in case of two forms in a Parent/child relationship (form and subform):
    Me.SubForm.Form. ' From the parent form
    Forms!Me.Parent.Name. ' From the child form

    4) Another method is to use a Filter on the Form:
    Code:
    Dim strFilter as String
    strFilter = "FirstName Like '" & Combo_FirstName.Value & "'"
    Me.Filter = strFilter    ' Or Forms!AnyForm.Filter = ...
    Me.FilterOn = True
    In any case, using a unique identifier is the most performant method, but you can always use any criteria or filter you see fit. This criteria (or this filter) is simply the WHERE clause of a SQL sentence without the WHERE word. E.g.,
    Code:
    FirstName = 'Smith' AND BirthDate > #01/01/1980#
    I hope this can help you a little bit.

    Have a nice day!

  3. #3
    Join Date
    Aug 2009
    Posts
    4
    Thanks Sinndho... I'm pretty sure that all my database sections have UIs, I spent a long time trying to ensure that the whole thing was normalized.

    I appreciate this is going to sound exceptionally dense, but where am I supposed to be typing this code?!

    Would it be useful for you to see the database? I don't want to waste your time obviously, and I will understand if you have more important things to be doing, but if you'd be willing to have a quick look I'd be very greatful.

    Albi

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No problem, post it and I'll have a look at what needs to be done.

    Have a nice day!

  5. #5
    Join Date
    Aug 2009
    Posts
    4
    Cheers for this, ideally I'd like to be able to search through customer name & ID, plant name & ID and company name & ID as the database will be expanded after I've finished my project (which is to build the database).

    thanks

    albi
    Attached Files Attached Files

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    albi,

    Here's your database back. I modified the code in the frmCustomers form module in order to perform searches based on the contact id.

    I also created a copy of this form: frmCustomers_New, that uses three combos to perform searches based on the contact id, the first name or the last name.

    Every procedure has comments explaining how it works, so the mechanisms should be rather easy to understand; just pay a special attention to the RowSource property of the Combo_ContactIdentity combo.

    I hope this can help you.

    By the way, and if you would allow me, the schema of your database is well organized.

    Have a nice day!
    Attached Files Attached Files

  7. #7
    Join Date
    Aug 2009
    Posts
    4
    I cannot thankyou enough for that, it's the first database I've ever tried to make, and so that is an enormous help.

    Also thankyou for the complement on the structure!

    Once again, thank you.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome.

    Have a nice day!

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Sinndho, you are a blessing to many, not many of us "do the job for them". You really go the extra mile! Good work!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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