Results 1 to 7 of 7
  1. #1
    lumajosh is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    4

    Access 2010 filtering datagrid on a subform problem. Please help!

    Hi,



    I’m totally new to MS Access. I have what seems to be a pretty simple thing to accomplish – I need to apply a filter via a VBA call to a datagrid on a subform. I have a form, a list box and a subform with a datagrid on it. When a user selects one or more choices in the list box, I need to click on a Filter command button and filter out the datagrid.
    I was using the following code -

    Private Function AppluFilter()
    Dim strValue As String
    Dim oItem As Variant
    Dim i As Integer

    For Each oItem In lstFilter.ItemsSelected
    If i = 0 Then
    strValue = CStr(lstFilter.ItemData(oItem))
    Else
    strValue = strValue & ", " & CStr(lstFilter.ItemData(oItem))
    End If
    i = i + 1
    Next

    Me.sbfDetails.Form.Filter = "VenNum In ( " & strValue & ")"
    Me.sbfDetails.Form.Filter = True
    End Function

    VenNum is the unique identifier and its numeric. The function does not generate an error, however it does not apply the filter either. What am I missing?
    Thank you

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    You are setting the Filter twice. Second needs to be FilterOn property. Try:
    Me.sbfDetails.Form.FilterOn = True
    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
    lumajosh is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    4
    thank you very much for such a speedy reply! After I've made the change, the filter got applied, however the datagrid became blank - no records shown. I quess I ommited to say in my original email that I have a "parent" grid control on the main form which is tied to a subform control via VenNum field. I don't know if this dependancy creates a problem.

    thanks again!

  4. #4
    lumajosh is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    4
    ok, based on what I have said before, I have a datasheet which dependents on a main control. the subform's datasheet displays only records which are relevant to what is displayed in the main control, so when the filter is applied, there are no records in the dataset for this particular filter. Do I need to re-run the query by adding a filter criteria to it or it somehow can be done by applying a filter?

    Thank you

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    By 'datagrid' you mean a form in DatasheetView? You have a main/sub forms arrangement? VenNum fields are the linking fields of the subform container control? If the subform is synchronized to the mainform by the VenNum fields, why do you need to apply filter to the subform?

    If you want to provide db for analysis, follow instructions at bottom of my post.
    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.

  6. #6
    lumajosh is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Sep 2012
    Posts
    4
    I'm sorry for not responding more promptly. I think I've solved my problem. I was missing an extra step, I needed to remove a filter from a child datasheet and apply new filter definition to a parent. Problem solved and thanks to you! I do, however, have another question. The datasheet on the subform is using a saved query. I’ve made some changes to it and I can see that whne I run it. The total number of records in the result set has decreased (I expect that to be the case), however when I run the form, that change does not get reflected, it still shows the old number of records. How can force it to recognize the change I’ve made?

    Thanks in advance.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Don't know. I would have to examine the objects and their properties.
    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. Datagrid in access?
    By BasilM in forum Forms
    Replies: 6
    Last Post: 09-01-2012, 11:23 AM
  2. Refresh subform in access 2010
    By lkates in forum Access
    Replies: 4
    Last Post: 07-23-2012, 01:00 PM
  3. VBA SQL in Access 2010 problem
    By ruthib4 in forum Programming
    Replies: 1
    Last Post: 03-13-2012, 09:08 AM
  4. Replies: 14
    Last Post: 03-07-2012, 03:46 AM
  5. Access 2010 Problem
    By DSTR3 in forum Access
    Replies: 7
    Last Post: 11-29-2010, 05:11 PM

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