Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34

    Option Button does not work right

    Hi,



    I didn't explain it right in a previous post so I will try to be as specific as I can. I have made a database in which I have imported 901 entries. Each entry includes these fields: ID, Main_Certification, Other_Certifications, Company, Country, Address_City, Telephone, Mobile, Fax, Site, Email, Contact. I added a textbox and a SEARCH button. The textbox is about Country.

    Let's just say I'm looking for a company in Greece, I type Greece in the textbox, press SEARCH and I get 17 results. So far so good, the SEARCH for the Country works. Now, from these 17 results I want to get the ones that have ABS in Main_Certification this is why I added an Option Button named ABS. When I click on the ABS option button everything disappears and I don't get anything! I'm missing something and I don't know what it is.

    This is the code for the textbox and the Option Button

    Textbox
    Code:
    Private Sub btnSearch_Click()
         Me.Filter = "[Country] Like '*" & [txtCountry] & "*'"
        Me.FilterOn = True
    End Sub
    Option Button
    Code:
    Private Sub optABS_Click()
        Me.Filter = "[Main_Certification] Like 'ABS" & [optABS] & "ABS'"
        Me.FilterOn = True
    End Sub
    To sum it up, I want to look for a company using the Country search and after I get the results I want to click on ABS to get the ones with the ABS certification. I have 3 more options to add using the Option Button and they will do the same thing as ABS does.

    Any help would be really appreciated. Plus, what is best to use: Option Button or Option Group?

    Picture shows what happens when ABS is clicked. The Main_Certification field is filtered but no results at all!

    Click image for larger version. 

Name:	Option Button.jpg 
Views:	21 
Size:	103.6 KB 
ID:	26661

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    is it possible both items filtered create zero records? (using the wrong AND vs OR)

    if you start using multiple filters ,then you either do a lot of programming OR
    dont do any programming and open the data sheet and filter items with a right click

  3. #3
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    I didn't use neither AND nor OR to the above code...

    So multiple filters need more programming to work ? I don't have a clue in VB programming, just try to understand what's happening and find a solution through videos I'm watching but nothing. Do you have any suggestion of how the code should be written in order to work?

    I thought it would be simple

  4. #4
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Can you just show the data sheet,and let users right click ,filter?

  5. #5
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    Yes that can be done. Just want to have it as an Option Button as well, would be interested to learn how this works using VB code

  6. #6
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    option buttons dont really work alone. option buttons work in a group in a FRAME. youd take the frame box, put the options in it ,each has a value.
    then the frame gets the value. So youd filter on the frame.value.

  7. #7
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    I tried Option Group too but didn't work! I was confused as where to put the code and what code.

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    For a single radio button you don't need an option group. This idea should work, but may have syntax problems as I haven't tested the criteria...

    Code:
    Private Sub optABS_Click()
        select case optABS
            Case True
                Me.Filter = "[Country] Like '*" & [txtCountry] & "*'" & " AND Main_Certification= " & 'ABS'
            Case False
                Me.Filter = "[Country] Like '*" & [txtCountry] & "*'"
        End Select
        Me.FilterOn = True
    end sub

  9. #9
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    Thank you very much for the code, it worked!!!

    One small problem though... This is what I wrote

    Code:
    Private Sub optABS_Click()
        Select Case optABS
            Case True
                Me.Filter = "[Country] Like '*" & [txtCountry] & "*' AND [Main_Certification] = 'ABS'"
            Case False
                Me.Filter = "[Country] Like '*" & [txtCountry] & "*'"
        End Select
        Me.FilterOn = True
    End Sub
    When I go to form view a message pops up "Syntax error (missing operator) in query expression "[Country] Like '**' AND [Main_Certification] = 'ABS". I close the pop up and it works... What is an operator ?

  10. #10
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Me.Filter = "[Country] Like '*" & [txtCountry] & "*'" & " AND [Main_Certification] = " & 'ABS'

    An operator in this case is an '=' sign.
    You could put a debug.print Me.Filter
    just after 'Me.Filter =' to see the actual rendering of the string.

  11. #11
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Code:
    Private Sub optABS_Click()
        Select Case optABS
            Case True
                Me.Filter = "[Country] Like '*" & [txtCountry] & "*'" & " AND [Main_Certification] = " & "'ABS'"
                Debug.Print Me.Filter
            Case False
                Me.Filter = "[Country] Like '*" & [txtCountry] & "*'"
        End Select
        Me.FilterOn = True
    End Sub
    This one is tested and works.

  12. #12
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    davegri's code should work, but for clarity, with 2 criteria, you have 4 options.

    I would add a button because if only the country is entered, using the "optABS" click event to apply the filter, the filter for country would never be applied.

    This is for a button named "btnFilterMe":
    Code:
    Private Sub btnFilterMe_Click()
        'neither filter selected
        If Len(Trim(Me.Country)) = 0 And Me.optABS = False Then
            Me.Filter = vbNullString
            Me.FilterOn = False
        End If
    
        'Only ABS
        If Len(Trim(Me.Country)) = 0 And Me.optABS = True Then
            Me.Filter = [Main_Certification] = " & "    'ABS'"
            Me.FilterOn = True
        End If
    
        'Only Country
        If Len(Trim(Me.Country)) > 0 And Me.optABS = False Then
            Me.Filter = "[Country] Like '*" & [txtCountry] & "*'"
            Me.FilterOn = True
        End If
    
        'Both Country AND ABS
        If Len(Trim(Me.Country)) > 0 And Me.optABS = True Then
            Me.Filter = "[Country] Like '*" & [txtCountry] & "*'" & " AND [Main_Certification] = " & "'ABS'"
            Me.FilterOn = True
        End If
    
    End Sub

  13. #13
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    "Syntax error (missing operator) in query expression "[Country] Like '**' AND [Main_Certification] = 'ABS".
    No issue with the suggestions, but this looks like the country field has one or more Nulls ('**') which is why it will work anyway. It just won't be accurate. It also suggests that the data is not normalized, like country is a field in a table where it should not be. In fact, after reviewing the image, it looks like there is only one table supporting all of these likely unrelated fields.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  14. #14
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    Thank you very much!!! It worked like a charm!!!

    I will take one line from the code above to ask: what is the difference between

    Code:
    Me.Filter = "[Country] Like '*" & [txtCountry] & "*'" & " AND [Main_Certification] = " & "'ABS'"
    and

    Code:
    Me.Filter = "[Country] Like '*" & [txtCountry] & "*' AND [Main_Certification] = "'ABS'"
    ?

  15. #15
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    Thank you very much!!! It worked like a charm!!!

    I will take one line from the code above to ask: what is the difference between

    Code:
    Me.Filter = "[Country] Like '*" & [txtCountry] & "*'" & " AND [Main_Certification] = " & "'ABS'"
    and

    Code:
    Me.Filter = "[Country] Like '*" & [txtCountry] & "*' AND [Main_Certification] = 'ABS'"
    ?


    Quote Originally Posted by davegri View Post
    Code:
    Private Sub optABS_Click()
        Select Case optABS
            Case True
                Me.Filter = "[Country] Like '*" & [txtCountry] & "*'" & " AND [Main_Certification] = " & "'ABS'"
                Debug.Print Me.Filter
            Case False
                Me.Filter = "[Country] Like '*" & [txtCountry] & "*'"
        End Select
        Me.FilterOn = True
    End Sub
    This one is tested and works.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 10-03-2014, 06:36 AM
  2. Replies: 3
    Last Post: 12-02-2012, 09:38 AM
  3. Replies: 2
    Last Post: 11-30-2012, 08:03 PM
  4. Replies: 6
    Last Post: 07-20-2011, 11:54 AM
  5. Replies: 3
    Last Post: 11-02-2010, 10:15 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