Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Seach Function (2000)

    Re post 31111 and the reply from Hans. I have done the coding as suggested and put this in a module, how do I now call the function on the after update event of my combo box.

    Kind regards Darren.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Seach Function (2000)

    You probably mean <post#=301111>post 301111</post#>. For the future: unless a thread becomes very long, it is usually better to ask a follow-up question in the same thread, that makes it easier for other Loungers to keep track of the questions and answers. Thanks.

    There are two ways you can use the function you created. Both start the same way:
    - Open the form in design view.
    - Select a combo box.
    - Activate the Event tab of the Properties window.
    - Click in the After Update event.

    Method 1:
    - Type =DoSearch() in the After Update box.

    Method 2:
    - Select Event Procedure from the dropdown list.
    - Click the Builder button (the three dots to the right of the dropdown arrow.)
    - Complete the code to look like this:

    Private Sub cmbField1_AfterUpdate()
    DoSearch
    End Sub

    - Switch back to Access.

    Repeat for the other combo boxes.

  3. #3
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seach Function (2000)

    Thanks Hans, sorry for giving you the run around on finding the previous post.

    Regards Darren.

  4. #4
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seach Function (2000)

    Thaks Hans, i am still having problems with the db I am building at work however, could you please assist with code on my home dummy again? Assuming field 'date' and 'name' are those on which the searches will be based?

    Many thanks Darren
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Seach Function (2000)

    I'm sorry, I don't see any combo boxes in the forms, nor the code I posted. Am I missing something?

  6. #6
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seach Function (2000)

    Sorry, I attached older version, this is the one.

    Thanks.
    Attached Files Attached Files

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Seach Function (2000)

    Darren,

    The code I posted in the other thread is headed

    Private Function DoSearch()

    Private means that the function is only available in the module in which it is defined. Moreover, the code in the function refers to the form as "Me" and it also refers to controls on the form. All this implies that you can't put the function in a general module, it must be in the module behind the form.

    Additional note: the function "as is" will activate the first record if you enter a value or combination of values in the combo boxes that cannot be found. If you want to avoid this, replace

    If Not rs.EOF Then

    by

    If Not rs.NoMatch Then

    And, if you like, you can give the user feedback if the value or combination of values wasn't found:

    If rs.NoMatch Then
    Beep
    Else
    Me.Bookmark = rs.Bookmark
    End If

  8. #8
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seach Function (2000)

    Marvellous, I really am hard work aren't I, you have enlightened me on a few key notions there. Thank you yet again for your help and patience. Is there a book you can recommend?

    Regards Darren.

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Seach Function (2000)

    Many developers like Access 2000 Developer's Handbook: Desktop Edition by Paul Litwin and others (Sybex), and fellow moderator <!profile=WendellB>WendellB<!/profile> recently recommended Beginning Access 2000 VBA by Robert Smith, David Sussman (Wrox Press). Both can be found on Amazon UK.

    Others may have more recommendations.

  10. #10
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seach Function (2000)

    Post deleted by iksotof

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Seach Function (2000)

    Darren,

    In the first place, do yourself some favours:
    <UL><LI>Turn on "Require Variable Declaration" in Tools | Options... (in the Visual Basic Editor). See the thread starting with <post#=301389>post 301389</post#> for a discussion of why it is necessary - the thread is in Excel, but the arguments are valid for all flavours of VBA.
    <LI>Give your controls meaningful names. You may know now what Combo212 and Option74 are meant for, but if you have to debug your database in a year's time, you'll have a hard time.
    <LI>Turn off the Name Autocorrect options in the General tab of Tools | Options... in all your databases.[/list]More specific remarks:
    <UL><LI>The Row Source of the Feedback Type combo box on tblFeedback subform refers to a non-existent table tbl_feed_type.
    <LI>The combo boxes pols, dates and IFAs on the main form have no Row Source. Why not? A combo box without row source is equivalent to a text box. Also, I have no idea what would be meaningful entries for these controls.
    <LI>The filter you create in code refers to fields pol, ifa and date. There are no fields named pol or ifa in the record source tbl_Main. There is a field date, but that is not a very good name for a field, since it is also the name of a VBA function. This can cause confusion. Since Access can't find field names pol or ifa, you get an error message if you enter something in the corresponding combo boxes.[/list]Please clear up these points.

  12. #12
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seach Function (2000)

    Thanks Hans, appears I have crossed over since your most recent response. I realised where I was going wrong and have mostly rectified. The only oustanding problem I have is that my third search is based on a field for names where these names are stored in the underlying table as ID

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Seach Function (2000)

    Set the column width for the first column to 0. The value of the combo box will still be the ID, but the user won't see it.

    And deleting a post with an attached database is not very user-friendly either. My reply has now become meaningless to other Loungers.

  14. #14
    4 Star Lounger
    Join Date
    May 2003
    Location
    Manchester, Gtr Manchester, England
    Posts
    552
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Seach Function (2000)

    Cheers Hans, the attachment is back on. I deleted before I realised you had replied, sorry.

    Your help has been much appreciated. Thank you.
    Attached Files Attached Files

Posting Permissions

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