Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello. My second post tonight. (Rough day)

    I have a form that shows records in a table tblpedidos. One of the fields in the tbl is F40

    The form has a combo box cbo42 that is bound to tblpedidos.F40

    The combo box shows much more friendly data in it which stays on the form (drawn from another table/qry on which the cbo box is based)

    Using the Filter by selection button that has a lightening bolt on it... I can filter for for what is SHOWN in the cbo42 field and not just the (unshown) bound column.

    If I select a record with the cursor in that field and press the lightening bold button... And then open the advanced filter a query grid shows that the where criteria is actually the text in the combo box field cbo42 and not the underlying bound column value......sooooo

    How can I go into VBA so I can get something like the following to work

    Me.filter = "cbo42 = 'the text in the combo box unbound column' "

    Any help would be much appreciated.

    Thanks.

    Jason

  2. #2
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello.

    I am now trying this.x on the form

    txt_orderinfo
    its data source is =[cbo42].[Column](1)

    The text box shows column 2 of my cbo42 combo box
    This works properly

    So now txt_orderinfo holds the the data I want to be examined and matched by the filter.

    I then have an unbound textbox in the form footer where you can enter the text you want to filter for
    And a Button you click with this CODE

    Private Sub cmd_ordernum_Click()
    Me.FilterOn = False
    Me.Filter = Me.txt_orderinfo Like "'%" & Me.txt_ordernum & "%'"
    Me.FilterOn = True
    End Sub

    clicking produces
    runtime error '94'
    Invalid use of null
    In debug window...Hovering over it...

    Me.txt_orderinfo resolves as NULL

    Any thought would be greatly appreciated.

    Thank you

    Jason

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Is your intention to use the combo box to display a specific record based on the entry of an order number in the combo box? If so, the combo box wizard will build it for you, but it doesn't use a filter. It uses a RecordsetClone method instead.

    It is possible to use a combo box to apply the filter, but in either case the usual approach is to make the combo box unbound, and to display the selection field in the combo box, and then use the OnChange event for the combo box to apply the filter or to do the RecordsetClote.

    If you continue to have challenges, you might consider uploading a "sanitized" version of your database so Loungers can better understand what you are trying to accomplish.
    Wendell

  4. #4
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Don't know if this will help, but why use a derived control when you can use the direct control? Try using [cbo42].[Column](1) in your expression rather than Me.txt_orderinfo.

  5. #5
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you so much for your replies.

    What I am doing is similar to the "use a combo box to filter records on a form" But I am using typed in text instead of a drop down selection... so I have a button to trigger it instead of the onchange event (although I guess I could use an onenter event or something like that)...

    The idea is similar

    The idea
    1) an unbound control to enter the data to filter FOR

    2) a field in the record list to filter ON

    In the case that seems more nomal and to which you I believe you are refering...

    1) you select what you want to filter FOR in an unbound combo box drop down list (probably in the former header or footer)

    2)you filter ON a certain field in your form to find matches

    In my case

    1) we want to filter FOR what you enter in an unbound text box (in the form footer)

    2) we want to filter ON a certain field in the record list BUT that field happens to be a combo box


    AND not only is it a combo box but I want to inspect (and filter ON) the second column of the combo box for matches. NOT the bound column.


    I hope that helps explain what I am trying to do a little bit more clearly. Thanks!!

    Jason

  6. #6
    Star Lounger
    Join Date
    Dec 2009
    Location
    Bellevue, WA
    Posts
    61
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Did you try my suggestion?

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I think you are making things unnecessarily complicated for yourself, by trying to filter on the displayed text rather than the bound value.

    If you use an unbound combo box in the form header, which has a hidden first column so it displays the same words as the form does, you can use this to filter on the bound value.
    To the user it will look like you are filtering on the displayed value, but it will be really using the hidden bound value.
    Regards
    John



  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    And to add to what John said, you can type in the combo box and get the same result as typing the text into a text box. And that approach solves the problem of a user typing in something that doesn't exist.
    Wendell

  9. #9
    Star Lounger
    Join Date
    May 2002
    Location
    Patagonia Region Chile
    Posts
    78
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks again for your replies.

    Larry.. thank you for your suggestion..I did try it but it did not solve the error message.

    Wendell.. Good idea... I will make the text box a combo box...I had not thought about the fact that you can type in a combo box.. (I was focused on the LONG drop down list and not making someone scroll through it)..Thanks

    John... I probably am making this more complex than it has to be.. but I can not think I can simplify in the way that you have suggested.

    A little more detail to explain why.

    We use this form to
    create a unique job number to fulfill line_items on orders.
    Some line items have a quantity greater than one so we need more than one job number for those line items.

    the form is based on

    tbl_job_Control_number

    Which has many_to_one relationship to tbl_orders_details which of course has a many to one relationship to tbl_orders.

    The combo box I want to filter ON exists to let the us assign a job number to every line_item (order_detail_id) on an order.. sometimes a line item may have a quantity of more than 1.. so that line item would need to have more than one jobs created to fulfill the order.

    This assignment of a job_control_number_id to Order_detail_id ties our fabrication system to our order taking system.

    The bound column is the order_detail_id and the second column is a concatenation of "line" & [oder_detail_id] & " from order " & [order_id]

    So on the form the user selects from a list that reads like this item 8945 from order 387

    I want them to be able to filter the records on the form by an order number.

    So I in the former textbox now to be a combo box that lets call it cboFitler_criteria.. I want to select an order number and have the form return only those records that are jobs being made for that order.
    If the order 317 had 3 line items on it Item A,B and C with quantities 1 of A, 2 of B and 5 of C then the filter would return 8 records.

    Basing the whole for on a querry that included all three tables joined and the right fields included would let me have an order field on my form and then I could just filter on that.... BUT the query will not allow records to be edited . which is why I am trying to do it with the unbound column of the combo box.... (and a LIKE statement with wild cards) like this.

    Me.Filter = [cbo42].[Column](1) Like "'%" & Me.cboFitler_criteria & "%'"

    But that cboFitler_criteria seems to render (or resolve not sure of the proper vocab) to NULL.

    Whew... Hope that makes it more clear of why I am making it so complicated. And why I am trying it this way. Any more thoughts.

    Thanks again so much for your help.

    Jason

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •