Results 1 to 5 of 5
  1. #1
    joshi868b is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2014
    Posts
    4

    Record with blank field not visible after using filter


    i have a form with a subform and few combo box to use as a filter the data. when there is a blank field in the table it is not shown my coding for the filter is
    [

    Function searchcriteria()
    Dim device, vlan As String
    Dim task, strciteria As String


    If IsNull(Me.cbodevice) Then
    device = "[DEVICE NAME] like '*'"
    Else
    device = "[DEVICE NAME]= '" & Me.cbodevice & "'"
    End If


    If IsNull(Me.cbovlan) Then
    vlan = "[VLAN ID] like '*'"
    Else
    vlan = "[VLAN ID]= '" & Me.cbovlan & "'"
    End If
    strcriteria = device & "And" & vlan
    task = "select * from L2PORTDETAILS where " & strcriteria
    Me.L2PORTDETAILS_subform.Form.RecordSource = task
    Me.L2PORTDETAILS_subform.Form.Requery


    End Function]

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    cross posted http://www.access-programmers.co.uk/...d.php?t=288205

    Welcome to the forum.

    When you cross post --post same question on multiple forums -
    tell readers you have done so and include a link to that post.

    Here's why

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    I would set the RecordSource for the form in the AfterUpdate of each combobox based on if there is data in both or either field. (syntax below not right but you get the idea)

    If not isnull(me.cbodevice) And not isnull(me.cbovlan) then Me.L2PORTDETAILS_subform.Form.RecordSource = "Select * From L2PORTDETAILS Where device = me.cbodevice AND vlan = cbovlan"
    else
    If not isnull(me.cbodevice) And isnull(me.cbovlan) then Me.L2PORTDETAILS_subform.Form.RecordSource = "Select * From L2PORTDETAILS Where device = me.cbodevice"
    else
    If isnull(me.cbodevice) And not isnull(me.cbovlan) then Me.L2PORTDETAILS_subform.Form.RecordSource = "Select * From L2PORTDETAILS Where vlan = me.cbovlan"
    else
    Me.L2PORTDETAILS_subform.Form.RecordSource = "Select * From L2PORTDETAILS" -Show all recs
    Endif
    Endif
    Endif
    Me.L2PORTDETAILS_subform.Requery

  4. #4
    joshi868b is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Sep 2014
    Posts
    4
    hello Bulzie
    can you explain it to or may i ask you to write the correct code. Here i have given only two filter if i have many filter then it will be very complicated.I ahve on idea but i dont know the coding for that i need to write a code which will also accept the null value for any field

  5. #5
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    If you have too many fields to search, then you need to build your Where clause to use in the Forms RecordSource (Search internet for Access Build Where Clause)

    Something like:
    If Not Isnull(Me.Combo1) then sWhere = "Where Combo1 = Me.Combo1"
    If Not Isnull(Me.Combo2) then sWhere = sWhere & " AND Combo2 = Me.Combo2"
    If Not Isnull(Me.Combo3) then sWhere = sWhere & " AND Combo3 = Me.Combo3"

    If there Combo field is blank, it will skip it and not include it in the Where clause. Once you are done, append the sWhere to your SQL statement to use for the RecordSource.

    rs1 = "Select * from Table " & sWHere

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

Similar Threads

  1. Replies: 3
    Last Post: 06-30-2015, 08:10 AM
  2. Replies: 1
    Last Post: 03-30-2015, 10:57 AM
  3. Replies: 4
    Last Post: 11-04-2014, 07:35 PM
  4. Replies: 2
    Last Post: 01-06-2011, 04:38 AM
  5. Replies: 3
    Last Post: 09-15-2010, 01:04 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