Results 1 to 8 of 8
  1. #1
    guitarzycki is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    20

    Filter a datasheet subform by a combobox column 2nd row

    Good morning!

    I have a form that has a subform that I would like to filter using a text box. The column that I wish to filter is a combobox bound to column 1 and displaying column 2 with the widths set to 0";1". I'd like to have the operator able to type a keyword or phrase into a textbox and filter the subform records by the value in the second column of this combobox. The code below works with the value in the first column and I cannot figure out how to point the filter to the second.

    Forms![Edit Tool Assemblies].Inventory_Datasheet.Form.Filter = "[cclassid] Like '" & "*" & [Forms]![Edit Tool Assemblies]![txt_ItemSearch] & "*" & "'"
    Forms![Edit Tool Assemblies].Inventory_Datasheet.Form.FilterOn = True



    Thanks,

  2. #2
    Gina Maylone is offline Always learning
    Windows 7 64bit Access 2013
    Join Date
    Jun 2013
    Location
    Afton, MN
    Posts
    544
    Try this: Forms![Edit Tool Assemblies].Inventory_Datasheet.Form.Filter = "[cclassid] Like '" & "*" & [Forms]![Edit Tool Assemblies]![txt_ItemSearch].column(2) - be sure of your column numbers, BoundColumn property numbering starts with 1, not 0. Column index reference starts with 0.

    Hope this helps.

  3. #3
    guitarzycki is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Aug 2016
    Posts
    20
    Quote Originally Posted by Gina Maylone View Post
    Try this: Forms![Edit Tool Assemblies].Inventory_Datasheet.Form.Filter = "[cclassid] Like '" & "*" & [Forms]![Edit Tool Assemblies]![txt_ItemSearch].column(2) - be sure of your column numbers, BoundColumn property numbering starts with 1, not 0. Column index reference starts with 0.

    Hope this helps.
    Thanks for your reply. Looking back it seems I was not very clear. What I wish to do is to sort the [classid] column (combobox column 2, bound by 1) by the value in the [Forms]![Edit Tool Assemblies]![txt_ItemSearch] (textbox).

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you need to filter on the bound column, you cannot filter on the other columns of a combo

    suggest modify your subform recordsource to something like

    SELECT *
    FROM maintable INNER JOIN LookupTable ON mainTable.column1=lookupTable.primarykeyfield

    rather than using the *, in the query grid, just bring down the fields required - in this respect, fields required from maintable and the description field of the lookuptable you want to filter on

    If you are using lookups in your table design, remove them - they cause issues like this

  5. #5
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I figured you could filter a combo column other than bound but wasn't sure, so I tried it out.
    Click image for larger version. 

Name:	comboFilter1.jpg 
Views:	17 
Size:	6.4 KB 
ID:	25480 Click image for larger version. 

Name:	comboFilter2.jpg 
Views:	17 
Size:	5.4 KB 
ID:	25481

    but I might be confused as to what the poster wants because in some posts we're saying "sort" and in others, "filter". The example I posted is filtered. I didn't try sorting on the textbox value, but I don't see why you couldn't. All that was required was to have the textbox as a reference in the combo row source sql and to requery the combo on textbox afterUpdate. Perhaps I have misinterpreted what Ajax is saying.
    Last edited by Micron; 08-17-2016 at 09:11 AM. Reason: correction
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    My understanding of what the OP wants is to filter a combobox in a form view based on a partial value from a textbox

    e.g.

    source table has a field called classid which is set as a lookup with a rowsource something like 'SELECT classID, classname from tblClasses'

    and he wants to filter (or sort?) based on (partial) classname, which he can't do because the value in the field is numeric.

    Using a combo as you suggest rather than a textbox kind of gets round that - although sorts would be based on the numeric value of classid rather than the text value of classname - but would need to be full names (so you would use = rather than Like), not partial names as could be entered in a textbox and make use of the Like comparator.

    Think I'm confusing myself now!

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    My understanding of what the OP wants is to filter a combobox in a form view based on a partial value from a textbox
    That's what I did. Seems you have misinterpreted my thousand word pictures , so allow me to explain them.
    The images are of the form in form view; the form is bound to a table with 5 records; combo is bound to column 1.
    The combo row source is a sql statement that references the textbox control as criteria (like any normal query that does so).
    Textbox is unbound, but I don't think that's relevant for my purpose, which is to show how I (I think) did what was asked for.
    If you enter 10 in the textbox, the combo is requeried when you click on it because there is an AfterUpdate on the textbox. You get 2 records.
    If you enter 15, you only get one, thus the combo rows are filtered based on the textbox, which happens to be the #2 column of the combo. I didn't hide the first column as in the original post because that would have masked the fact that the filtered values were in the second column. I also did not use a wildcard search since it wasn't relevant as to whether or not it could be done at all. What I have looked for but have not found in the posts is where it says the wildcard search is being used on a number field.
    Hope that helps and that I have not misunderstood the request!
    Last edited by Micron; 08-17-2016 at 11:00 AM. Reason: clarification

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    What I have looked for but have not found in the posts is where it says the wildcard search is being used on a number field.
    an assumption on my part from this line in the original post

    Forms![Edit Tool Assemblies].Inventory_Datasheet.Form.Filter = "[cclassid] Like '" & "*" & [Forms]![Edit Tool Assemblies]![txt_ItemSearch] & "*" & "'"
    fields with ID suffixes are usually numbers - and coupled with the fact it is a combobox with the first column hidden also leads me to suspect it is a primary key - probably an autonumber.

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

Similar Threads

  1. Replies: 1
    Last Post: 01-16-2015, 09:28 AM
  2. Replies: 2
    Last Post: 07-21-2012, 04:06 AM
  3. Replies: 1
    Last Post: 09-06-2011, 01:47 PM
  4. Replies: 0
    Last Post: 08-24-2010, 06:38 PM
  5. Replies: 0
    Last Post: 12-16-2009, 01:14 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