Results 1 to 13 of 13
  1. #1
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34

    Text Box, Combo Box & Search Button don't work

    Hi,



    I have made a database, the table, the query, the form, I have also made the Country: (search box) to work. So far so good. I have added a combo box named Certification. I sometimes want to search the database by using the search box only, sometimes use the combo box only and sometimes use both (search box and combo box).

    When I made the combo box at first place, I selected the "I want the combo box to get the values from another table or query". When I finished, I run the combo box and I got a huge drop down menu (ABS, DVN-GL and LLOYDS was shown many many times). I deleted it so I made it again by using "I will type in the values I want". I typed the three categories (ABS, DVN-GL and LLOYDS) from Main Certification column. In the combo box I want the 3 categories to appear (one time each).

    I added some code to make it run but nothing!

    Code:
    Private Sub Command131_Click()
    Me.Filter = "[Country] Like '*" & [Text128] & "*' Or [Main Certification] Like 'ABS" & [Combo132] & "ABS' Or [Main Certification] Like 'DNV-GL" & [Combo132] & "DNV-GL' Or [Main Certification] Like 'LLOYDS" & [Combo132] & "LLOYDS'"
        Me.FilterOn = True
    End Sub
    I did some changes to the code but can't find why it's not running. Well, it's the second time I'm using Access and can't figure that out. Somehow I feel that I must link the categories to each row they're being mentioned. For example: ABS is in many rows but I don't want all the ABS rows to appear in the combo box dropdown menu. I only want to show ABS once in the combo box dropdown menu and when I choose it to return all the results that include the ABS. This is the same for the rest 2.

    Ignore the "Other Certification" column.

    Picture attached

    Click image for larger version. 

Name:	Textbox & Combo Box.jpg 
Views:	19 
Size:	151.8 KB 
ID:	26635

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Then you need code to check all boxes to see what is used....
    Code:
    If not IsNull(cboBox) then sWhere = sWhere & " and [field]='" & cboBox & "'"
    If not IsNull(txtBox) then sWhere = sWhere & " and [field2]='" & txtBox & "'"
    
    SWhere= mid(sWhere,5). 'Remove 1st And
    me.filter =sWhere
    me.filterOn=true

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I presume combo32 is your certification combo? It is always better to name them appropriately e.g. cboCertification

    assuming that is the case, all you need is

    Me.Filter = "[Country] Like '*" & [Text128] & "*' Or [Main Certification] Like '" & [Combo132] & "'"

    Also, investigate using the select distinct option rather than using a value list - i.e. your combo rowsource would be something like

    SELECT DISTINCT MainCertification FROM my TABLE

    For your filter, not sure whether you want AND or OR

  4. #4
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    Tried that code but it gives me a Syntax Error in

    Code:
    SWhere= mid(sWhere,5). 'Remove 1st And
    Must I put it on its own or with

    Code:
    Me.Filter = "[Country] Like '*" & [Text128] & "*' Or [Main Certification] Like 'ABS" & [Combo132] & "ABS' Or [Main Certification] Like 'DNV-GL" & [Combo132] & "DNV-GL' Or [Main Certification] Like 'LLOYDS" & [Combo132] & "LLOYDS'
    I don't quite understand what it does though...

    Quote Originally Posted by ranman256 View Post
    Then you need code to check all boxes to see what is used....
    Code:
    If not IsNull(cboBox) then sWhere = sWhere & " and [field]='" & cboBox & "'"
    If not IsNull(txtBox) then sWhere = sWhere & " and [field2]='" & txtBox & "'"
    
    SWhere= mid(sWhere,5). 'Remove 1st And
    me.filter =sWhere
    me.filterOn=true

  5. #5
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    I tried that one as well but nothing!

    When I use the

    Code:
    Me.Filter = "[Country] Like '*" & [Text128] & "*' Or [Main Certification] Like '" & [Combo132] & "'"
    nothing happens. I used it with SELECT DISTINCT Main Certification FROM my TABLE but nothing happened. If I put this in my combo rowsource then the "ABS", "DNV-GL", "LLOYDS" disappear and SELECT DISTINCT Main Certification FROM my TABLE becomes the only choice available. I mean that when I go to form view and click on the dropdown list, only SELECT DISTINCT Main Certification FROM my TABLE appears

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    When I use the

    Code:
    Me.Filter = "[Country] Like '*" & [Text128] & "*' Or [Main Certification] Like '" & [Combo132] & "'"
    nothing happens. I used it with SELECT DISTINCT Main Certification FROM my TABLE but nothing happened. If I put this in my combo rowsource then the "ABS", "DNV-GL", "LLOYDS" disappear and SELECT DISTINCT Main Certification FROM my TABLE becomes the only choice available
    'myTable' is a generic name for example code. I can't read the table name in your uploaded image, substitute it with whatever table has the maincertification field.

    It is also a bad idea to use spaces in field names, so I also don't use them for generic code - if you want the user to see spaces, use the field caption property, that is what it is for

  7. #7
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    Oh OK...

    So where SELECT DISTINCT Main Certification FROM my TABLE goes ?

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    per my post

    i.e. your combo rowsource would be something like

    SELECT DISTINCT MainCertification FROM my TABLE

  9. #9
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    When I put it to my combo rowsource, the 3 choices I have disappear and instead of these that code appears.

    Nevermind, I quit the combo box and playing with Option Button. I created one option button to see how it goes. The option button is the ABS, so there is a textbox and an Option button. I clicked the SEARCH button and on the EVENT tab, On Click proceedure I added this code:

    Code:
    Private Sub Command131_Click()
    Me.Filter = "[Country] Like '*" & [Text128] & "*'"
    Me.Filter = "[Main Certification] Like 'ABS" & [Option144] & "ABS'"
    Me.FilterOn = True
    End Sub
    When I run it, I choose ABS and when I click on SEARCH no results, everything disappears!!!

    Here's a picture

    Click image for larger version. 

Name:	Option Button.jpg 
Views:	16 
Size:	103.6 KB 
ID:	26647

  10. #10
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    clearly my communication skills are not sparkling today, so I'll butt out

    Good luck with your project

  11. #11
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    Maybe I don't write something right but don't know how to write it... Would like some help using the Option Button though

  12. #12
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    Can't figure out what's happening. Using the Option Button is filtering the column but no results!!!

    I tried Option Group as well but can't find a solution!!! It's a bit more complicated than I thought

  13. #13
    ITVader is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2016
    Posts
    34
    It was my mistake, I posted the thread in Macros which is not relevant. I will re-post it and be more specific

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

Similar Threads

  1. Search Button with Text Box won't work
    By lisanoe in forum Forms
    Replies: 4
    Last Post: 12-16-2015, 10:02 PM
  2. Replies: 9
    Last Post: 11-05-2014, 09:23 AM
  3. Replies: 2
    Last Post: 08-27-2014, 08:19 AM
  4. Replies: 4
    Last Post: 08-16-2011, 05:54 PM
  5. Replies: 8
    Last Post: 07-06-2011, 11:20 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