Results 1 to 15 of 15
  1. #1
    82280zx's Avatar
    82280zx is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    82

    Combobox Search

    Re: https://www.accessforums.net/program...orm-41586.html

    I'm doing a similar filter. When I search most of the columns I specified works fine but the columns with combobox's will only allow me to search by the autonumber id in the first column. Is there a way when I specify my filter to have it search by say a column in that combobox?



    I set the filters in the form first like this:
    [Category1] & '|' & [Category2] Like '**' under the Property sheet in Data/Filter

    then in my searchbox I go to properties / Event / On change and do this

    formname.form.filter = "[Category1] & '|' & [Category2] Like '*" & txtSearch.Text & "*'"
    formname.form.filteron = true

    But lets say Category1 is a combobox, when I search using it in my list here I can only search by column 0, can I change it to look at all columns or change the column to search? I hope that makes sense.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, can refer to the combobox column index. Index begins with 0. Column 2 would be index 1.

    comboboxName.Column(x)
    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
    82280zx's Avatar
    82280zx is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    82
    I'll have to try that again, each time I've attempted that it seems to error me out. Do I need to put it in brackets like this ? [comboboxname.Column(x)] or [Comboboxname].Column(x) ? Thanks again. I get a Undefined Function '[Combobox].Column' in expression.

  4. #4
    82280zx's Avatar
    82280zx is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    82
    Quote Originally Posted by 82280zx View Post
    I'll have to try that again, each time I've attempted that it seems to error me out. Do I need to put it in brackets like this ? [comboboxname.Column(x)] or [Comboboxname].Column(x) ? Thanks again. I get a Undefined Function '[Combobox].Column' in expression.
    I just read I can't use Column(x) in a query result =/ thats my problem I bet. Thanks again.

    Figured it out

    revvedmoto


    You can try doing what I just did and see if thats what your looking for. Select WPS Subform Form and go the the property sheet, select the Data Tab, in Filter put [nameofdescriptioncolumn] Like '**'
    then select your wpsearch text box and go to it's property sheet, go to on change and put this in

    Private Sub wpssearch_OnChange()

    WPS Subform.Form.Filter = "[nameofdescriptioncolumn] like '*" & wpssearch.Text & "*'"
    WPS Subform.Form.FilterOn = True

    End Sub

    Thats if your subform is named WPS Subform I'm going off the data from your screenshot above.

    Thanks again June7 btw!
    Last edited by 82280zx; 02-19-2014 at 07:53 PM. Reason: goobered it up had to fix it.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Don't understand what you mean by 'in a query result'. I thought you were referencing combobox in VBA.
    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
    82280zx's Avatar
    82280zx is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    82
    Quote Originally Posted by June7 View Post
    Don't understand what you mean by 'in a query result'. I thought you were referencing combobox in VBA.
    I was designing a search system to filter a query I had made. From what I read if there is a combobox inside the Query you won't be able to use a the .Column(x) to filter a result inside the query. I was using VBA for my filters .

    The forum post I found on google:
    http://www.access-programmers.co.uk/...d.php?t=171484 the reply on #6.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Combobox Column property cannot be referenced within a query. Has nothing to do with whether or not any field is set up as a combobox (dropdown list).

    I also wonder why need to reference other columns and not search on the ID?
    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.

  8. #8
    82280zx's Avatar
    82280zx is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    82
    Quote Originally Posted by June7 View Post
    Combobox Column property cannot be referenced within a query. Has nothing to do with whether or not any field is set up as a combobox (dropdown list).

    I also wonder why need to reference other columns and not search on the ID?
    I don't want my users to see the ID's, it's easier for them to understand if they see what is attached to the ID's, my goal with this database I'm designing is to keep my autonumbers hidden away from the users if possible, there what keeps my data tied together in a neat fashion and less confusing for people that doesn't know much about access.

    Basicly I'm upgrading these guys from a ton of spreadsheets there using, there spreadsheets are getting to big and are becoming hard to read, so I'm simplifying there life by making a access database. I'll have to make a copy of my database and toss some sample stuff in there so you can see what I'm talking about . I'm actually amazed I've even got this far, without your help I'd be stuck in the mud.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    And that is as it should be - autonumbers invisible. User's don't see IDs but code uses ID to search. This presumes the ID is defined as primary/foreign key.
    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.

  10. #10
    82280zx's Avatar
    82280zx is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    82
    Quote Originally Posted by June7 View Post
    And that is as it should be - autonumbers invisible. User's don't see IDs but code uses ID to search. This presumes the ID is defined as primary/foreign key.
    Thats what I normally do, I have the ID hidden and columns set to 2, and width set to 0",1" usually. I created a textbox to search results in my query, but for some reason it wouldn't search the results of combobox's in the query. I had to take out combobox's in the query and point it to the data I wanted to search correctly.

    So I have two combobox's outside the query that filter it based on the selection. Then I have a search box that you can search the fields I specify and it will filter the query down even further.

    Example my Query (is in a form but is a subform) had a combobox named Grid, I had it set to only show Column 2 so the user doesn't see the autonumber. But my search (outside of the query and in a form and a text box) would only work with the autonumber to filter my query. My columns in the combobox inside the query was GridID (AutoNumber) and Grid (short text). If I entered the Grid short text version it would filter incorrectly, but if I entered the GridID the autonumber version it would filter correctly. I tried to attach .column(x) to see if that would fix it. But then I learned I couldn't because my filters are inside a Query and that Column(x) didn't work with Querys. So I improvized and located the information in my other tables and just brought them into my query and got rid of my combobox's inside the query if that makes sense.

    Sorry it's hard for me to explain when I type, I'm trying to make sense for you.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The query can reference combobox, just can't reference the Column property.

    If you don't want to use the GridID as search parameter then why is it even in the combobox RowSource?

    Is the form RecordSource a query that joins tables so the related descriptor field is available?
    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.

  12. #12
    82280zx's Avatar
    82280zx is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    82
    Quote Originally Posted by June7 View Post
    The query can reference combobox, just can't reference the Column property.

    If you don't want to use the GridID as search parameter then why is it even in the combobox RowSource?

    Is the form RecordSource a query that joins tables so the related descriptor field is available?
    The query can reference combobox, just can't reference the Column property.
    I learned this a bit ago :P

    If you don't want to use the GridID as search parameter then why is it even in the combobox RowSource?
    Scratch that, I pulled the combobox from my Asset table when I created my query, it was preconfigured with GridID and stuff, I didn't want to affect my other forms by changing it's roots.

    Is the form RecordSource a query that joins tables so the related descriptor field is available?
    Yes it was but it didn't work the way I originally had it.

    I think I have you confused. The searchbox in question was a text box, but in the query the results that had combox's wouldn't filter properly when I did my search in the textbox, they would only work if I used the ID in the searchbox and wouldn't work if I used the GridID.

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Really confused now.

    Do you have this resolved anyway?
    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.

  14. #14
    82280zx's Avatar
    82280zx is offline Advanced Beginner
    Windows 7 64bit Access 2013
    Join Date
    Jan 2014
    Posts
    82
    Yea I figured it out, now I'm just trying to figure out how to print my subform with my filters attached. Sorry June I tried but I'm still learning the ropes :P

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Print a subform?

    Probably should print a report.
    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. Combobox search
    By azhar2006 in forum Forms
    Replies: 2
    Last Post: 02-12-2014, 03:05 PM
  2. Bottom-up search on a combobox
    By argsemapp in forum Forms
    Replies: 1
    Last Post: 11-14-2013, 05:39 PM
  3. Combobox Filtered Search
    By dgwynn in forum Access
    Replies: 3
    Last Post: 12-12-2012, 03:10 PM
  4. Combobox Search Changing Unit #'s
    By leamas in forum Forms
    Replies: 3
    Last Post: 06-22-2012, 04:30 PM
  5. ComboBox Search - No Results
    By cvansickle in forum Forms
    Replies: 5
    Last Post: 03-27-2011, 03:37 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