Results 1 to 6 of 6
  1. #1
    desireemm1 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2009
    Posts
    50

    Searching a Subform

    hi all I have an access database with a main form which is the people_tbl and a subform which is called the TanfActivity_tbl and the childrens_tbl. The users and myself are unable to do a search in the subforms, is there some way I can make it so that the users can search fields in the subforms as well as the main form. For instance the TanfActivity_tbl has dates that they need to search a persons Activity by. Is there some kind of code I can use to do that??

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Yes, it can be coded. Or can use the intrinsic Access tools for search and filter. Click in any field and select Sort or Filter from the ribbon or right click to get sort/filer options or if in Datasheet view click on the field heading to get dropdown.

    Coding filter or goto a record on subform is a little trickier than for a main form. But basic concept is same. Use an unbound data control to enter search criteria and set filter property of form. Only for a subform have to go through the subform container, like:
    Me.subformcontainername.Form.FilterOn = False
    Me.subformcontainername.Form.Filter = "fieldname=" & Me.controlname
    Me.subformcontainername.Form.FilterOn = True
    Last edited by June7; 10-02-2011 at 09:29 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    desireemm1 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2009
    Posts
    50
    Quote Originally Posted by June7 View Post
    Yes, it can be coded. Or can use the intrinsic Access tools for search and filter. Click in any field and select Sort or Filter from the ribbon or right click to get sort/filer options or if in Datasheet view click on the field heading to get dropdown.

    Coding filter or goto a record on subform is a little trickier than for a main form. But basic concept is same. Use an unboud data control to enter search criteria and set filter property of form. Only for a subform have to go through the subform container, like:
    Me.subformcontainername.Form.FilterOn = False
    Me.subformcontainername.Form.Filter = "fieldname=" & Me.controlname
    Me.subformcontainername.Form.FilterOn = True
    What I have on the subform is a command button called search
    The code that is in the form propety is
    Code:
    Private Sub SearchCmd_Click()
    On Error GoTo Err_SearchCmd_Click
    
        Screen.PreviousControl.SetFocus
        DoCmd.RunCommand acCmdFind
    Exit_SearchCmd_Click:
        Exit Sub
    Err_SearchCmd_Click:
        MsgBox Err.Description
        Resume Exit_SearchCmd_Click
        
    End Sub
    Would I integrate the code you have with that code. (sorry I dont know Visual basic)

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Correction on my earlier post. I was assuming the unbound search criteria control would be on the main form.

    I have never used the code you show but it looks okay. What happens when you execute it?

    If you have only the one criteria for the search, could use the AfterUpdate event of the unbound combobox instead of button Click.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    desireemm1 is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Aug 2009
    Posts
    50
    Quote Originally Posted by June7 View Post
    Correction on my earlier post. I was assuming the unbound search criteria control would be on the main form.

    I have never used the code you show but it looks okay. What happens when you execute it?

    If you have only the one criteria for the search, could use the AfterUpdate event of the unbound combobox instead of button Click.
    I used a command button on the design tab when I put it on the main form a wizard comes up and gives me options, I picked the one where find a record. the only problem is you can find one on the main form not the subform. So do I put the command button on the main form and put this code in the property event tab of the button

    Me.subformcontainername.Form.FilterOn = False
    Me.subformcontainername.Form.Filter = "EventDate=" & Me.controlname
    Me.subformcontainername.Form.FilterOn = True

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    It is technique I use. Try it and let us know how it goes.

    The code doesn't go in the property. Select [Event Procedure] in the event property then click the ellipses (...) to go to the procedure in the VBA editor. Put code there. I don't use macros, only VBA.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Searching through a subform on a form
    By HarryScofs in forum Access
    Replies: 10
    Last Post: 08-01-2011, 03:37 PM
  2. Searching Question
    By Jbelle7435 in forum Programming
    Replies: 5
    Last Post: 05-15-2011, 07:48 AM
  3. Replies: 1
    Last Post: 12-22-2010, 01:28 AM
  4. Searching Dates
    By knightjp in forum Database Design
    Replies: 2
    Last Post: 08-16-2008, 11:39 PM
  5. Help with Searching Dates
    By rededdie in forum Access
    Replies: 1
    Last Post: 11-02-2007, 08:34 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums