Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    vha7 is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2018
    Posts
    46

    How to create a link between a Text Box and a Category Combo Box?

    Hi all,



    I have a database that is able to search any keyword within the specified columns that I have chosen.

    However, I want to be even more specific. My goal is to create a keyword search box within a Text Box that will search within the specific Category from the Combo Box that I have chosen.

    i.e Let's say I am working with demographic data. I choose a category of population age from the combo box such as "Adult" (out of newborn, child, teenager, elderly, etc). But then I want to have an additional text box, that when I search a string as a keyword, such as "male", it will display to me on a form of all the results that have locked in the category of Adult but contain the word, "male", across all specified columns/fields that I have chosen.

    So it is a search within a specified category. Any solutions in terms of VBA coding or anything else would be deeply appreciated. Thank you.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    The form is a continuous form.
    then apply the filter depending on the box and/or combo

    Code:
    sub txtBox_afterUpdate()
       ApplyFilt
    end sub
    
    sub cboBox_afterUpdate()
       ApplyFilt
    end sub
    
    Sub applyFilt()
    sWhere ="1=1"
    If  Isnull(txtBox) and IsNull(cboBox) then
        Me.filterOn= false
     else
    
       If not Isnull(cboBox) then sWhere =sWhere & " and [cata]='" & cboBox  & "'"
       If not Isnull(txtBox) then sWhere =sWhere & " and [field] like '*" & txtBox  & "*'"
        Me.filterOn= true
    end if
    
    end sub

  3. #3
    vha7 is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2018
    Posts
    46
    Thanks for the code. Ranman!

    However it doesn't seem to work. I forgot to mention that I have a button to press so when I type in the keyword in the textbox, it will search for the keyword - not sure if that would change anything to the code.

    And for the code you posted, I put each code through the respective fields in the property sheet for each item, but it is a Private Sub and not a Sub, not too sure if they would do anything as well. However it gives me the error of: "Compile error: Variable not defined" in the "Sub applyFilt()..." code/section.

    EDIT1: I have fixed the Error Message by adding "Dim applyFilt as String" - let me know if it should be declared as something else. However, the code still does not work - Let's say without choosing a category, I will search a keyword that will display 32 results by itself. Now let's say I choose a category which displays, lets say 100 results. But then when I enter a keyword and hit search, it still displays 32 results, when technically it should display less than 32 because of it being searched within the 100 results, per se.

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by vha7 View Post
    My goal is to create a keyword search box within a Text Box that will search within the specific Category from the Combo Box that I have chosen.
    This is confusing. You cannot have a text box within a text box.


    Quote Originally Posted by vha7 View Post
    I have fixed the Error Message by adding "Dim applyFilt as String" - let me know if it should be declared as something else.
    It would seem that you have "Option Explicit" at the top of the module. This is good.
    But "applyFilt" is the name of a Sub - you should NOT have "Dim applyFilt as String" anywhere. In ranman256's code you should have
    Code:
    Sub applyFilt()
    "Dim sWhere as String"
    
    sWhere ="1=1"
    .
    .
    .
    Ranman256 used "cboBox" and "txtBox" as the names of the two controls because you didn't specify control names. You would need to change those names to your names.

    You also need 1 additional line:
    Code:
    Me.Filter = sWhere
    Me.filterOn= true

    Your description of the form and controls is a little hard to visualize. Care to post a picture of the form?
    Quote Originally Posted by vha7 View Post
    I forgot to mention that I have a button to press........
    What is the code for the button? That would also help a lot.

  5. #5
    vha7 is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2018
    Posts
    46
    Quote Originally Posted by ssanfu View Post
    This is confusing. You cannot have a text box within a text box.
    Sorry, I meant when I type a word within the textbox, I want it to be bound by the Category that I have chosen through the combo box. So it would be like Category --> Keyword (within category) --> result

    I have also fixed it to Dim sWhere As String - thanks for that.

    And I would add this anywhere?:
    Me.Filter = sWhere
    Me.filterOn= true

    And attached below is the layout of my form. As you can see, it is a subform within a form. I want to be able to click on the Category combo box (that should in itself already display results below (seen with the Total Count box). But then when I search a keyword and press "Search:, it searches within the category that I have chosen, updating the Total Count box.


    Here is the code to my button (I have renamed the columns/source for confidentiality purposes):

    Private Sub btnSearch2_Click()
    Dim SQL2 As String

    SQL2 = "SELECT [Source1].Column1, [Source1].[Column2], [Source1].[Column2], [Source1].[Key Term], [Source1].[Column4], [Source1].[Column5], [Source1].Column6, [Source1].[Column7], [Source1].Column8, [Source1].Column9, [Source1].[Column10] " _
    & "FROM [Source1] " _
    & "WHERE [Column1] LIKE '*" & Me.txtSearchBox & "*' " _
    & " OR [Column2] LIKE '*" & Me.txtSearchBox & "*' " _
    & " OR [Key Term] LIKE '*" & Me.txtSearchBox & "*' " _
    .
    .
    . etc
    & "ORDER BY [Source1].[Column3] DESC; "

    Me.subPublications2.Form.RecordSource = SQL2
    Me.subPublications2.Form.Requery

    End Sub
    Attached Thumbnails Attached Thumbnails layout2.png  

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    If you aren't really changing the source of data for the form, I recommend not setting RecordSource property - just set the form Filter and FilterOn properties.
    Last edited by June7; 07-25-2018 at 07:37 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.

  7. #7
    vha7 is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2018
    Posts
    46
    Do you mind elaborating on that thought? Yeah everything is coming from the same source (i.e Source1).

    The thing is whenever I enter a keyword and press the search button, it will overwrite the category selection I have put for the Category combo box.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Ssanfu describes technique in post 4.

    Controls used for input of search/filter criteria should be UNBOUND.
    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.

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It is difficult to give a good answer when the object names and table relationships are unknown (for whatever reason).
    Feels like I'm one of the 3 blind mice, touching the elephant's foot and trying to describe what the elephant looks like.


    I'm not sure what you want as far as searching, so maybe this will be of help.
    With 2 controls, there are 4 options
    1) Both controls Null,
    2) cboCatagory control filled in, text box control Null
    3) text box control filled in, cboCatagory control Null
    4) Both controls filled in.

    The search code deals with all 4.


    When the form opens, you can see all of the terms I entered.

    Because of the wild card character, if you search for Male, Female is also returned.
    Attached Files Attached Files

  10. #10
    vha7 is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2018
    Posts
    46
    Thanks ssanfu. This is exactly what I was looking for! Especially the combo box drop down menu to display only single terms!

    However, I came across 2 problems.
    1)I am unable to run your file because it gives me the error message: "Your MS Acces db or project contains a missing or borken reference to the file 'Ch18lib.mda'
    2)When I tried to fill in your form/subform/code with my respective file and actual term names, it says "Compile error - unable to find project or library) - the combo box works fine but when I search a term and click Search, thats when the error occurs.

    Any solutions to these problems? Thanks

  11. #11
    vha7 is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2018
    Posts
    46
    Thanks ssanfu. This is exactly what I was looking for! Especially the combo box drop down menu to display only single terms!

    However, I came across 3 problems.
    1)I am unable to run your file because it gives me the error message: "Your MS Acces db or project contains a missing or borken reference to the file 'Ch18lib.mda'
    2)When I tried to fill in your form/subform/code with my respective file and actual term names, it says "Compile error - unable to find project or library
    3) When I enter the code into my own database, this pops up:
    Click image for larger version. 

Name:	Untitled.png 
Views:	24 
Size:	16.1 KB 
ID:	34884

    Any solutions to these problems?

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    1. Uncheck the missing library in VBA editor Tools>References

    2. Can't help without code to analyze

    3. Missing closing paren after the last OR criteria

    this AND (this OR this OR this OR this)

    I don't understand how providing field names violates confidentiality. Data can be sensitve.

    Might want to correct the spelling of category.
    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.

  13. #13
    vha7 is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Jul 2018
    Posts
    46
    OMG IT WORKS!!!!

    The solving of 3 pretty much is everything I have ever needed and dreamed of!!!! Wow thank you so much I cannot express how sincerely thankful I am and how appreciative I am for your will to help me throughout this entire problem of mine I've been stuck on for a couple of weeks.

    Thank you so much.

  14. #14
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by June7 View Post
    Might want to correct the spelling of category.
    My stoopid confuser don't know how to spell none too good.

  15. #15
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @vha7

    Glad it was what you wanted.


    I saw in the error pic that you have spaces in the field names.

    Some suggestions:
    Use only letters and numbers (exception is the underscore) for object names.
    *** Do not use SPACES, punctuation or special characters in object names.
    Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.
    Do not begin object names with a number.


    You will save yourself big headaches if you removed the spaces.

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

Similar Threads

  1. I want to create a link combo box
    By Jeremy Sng in forum Access
    Replies: 4
    Last Post: 10-27-2016, 07:08 AM
  2. Replies: 1
    Last Post: 01-22-2016, 04:36 AM
  3. Replies: 3
    Last Post: 07-15-2015, 03:14 AM
  4. Replies: 5
    Last Post: 05-10-2014, 09:24 AM
  5. Replies: 1
    Last Post: 01-10-2013, 05:59 PM

Tags for this Thread

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