Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Mar 2005
    Posts
    55

    Exclamation VBA open filtered query

    I have a form with a drop down box, from which the user has the option to pick a name, once the name has been selected, the user clicks the button that triggers the following code

    DoCmd.OpenReport "rptDriver", , , qryDriver.Driver = "'& cboDriver.Value &'"


    this code returns the error "object required"

    what i am trying to do is, open a report, and filter this report according to the name selected, by passing the selection through the underlying query of the report.

    Driver field is a text field as is the field to be selected from the combo box

    need help urgently

  2. #2
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    In query put Criteria: [Forms]![YourFormName]![FieldName]
    and
    DoCmd.OpenReport "rptDriver"

  3. #3
    Join Date
    Mar 2005
    Posts
    55
    I should have been more clear

    the selection from the combo box is not manadatory, so the user can opt not to choose a driver, in that event placing any paramaters in the query will throw an exception

    i need the filter to run from the form, so that it does not disrupt the query, if the user leaves the driver combo blank

  4. #4
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    In that case in qury Criteria put:
    Like [Forms]![YourFormname]![fieldname] & "*"

  5. #5
    Join Date
    Mar 2005
    Posts
    55
    GENIUS!

    thanks a heap, that works a treat

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Just a tiddler:

    if the field is null the record will never be returned. I tend to use:

    NZ(MyField, "") Like [Forms]![YourFormname]![fieldname] & "*"
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2006
    Posts
    37
    I am hoping someone can help me with this...

    I am attempting to do the same as databaseman has mentioned except the fact that I have four drop down menus that feed criteria in a query that are set-up in an and functionality.

    When I use the solution specified by MStef-ZG it returns all of the data in the underlying table no matter what options are selected in the combo boxes.

    Thanks

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Can you post your SQL?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2006
    Posts
    37
    Sure the code is included below... hope it makes some sense... note also that this is an append query but I can always change the query type if that makes it too complicated to look at.

    Code:
    INSERT INTO tblWorkingData ( GTC, Item, ItemDesc, MfgLoc, DmdQty, OpNum, OpDesc, WorkCenter, WorkCenterDesc, HrsPer100 )
    SELECT tblItems.GTC, tblItems.Item, tblItems.Desc, tblItems.MfgLoc, tblItems.DemandQty, tblRoutings.OpNum, tblRoutings.OpDesc, tblRoutings.WorkCenter, tblWorkCenters.GeneralDesc, tblRoutings.Hours
    FROM (tblItems INNER JOIN tblRoutings ON tblItems.Item = tblRoutings.Item) INNER JOIN tblWorkCenters ON tblRoutings.WorkCenter = tblWorkCenters.WorkCenter
    WHERE (((tblItems.GTC) Like [Forms]![frmDataSelector]![GTCLimit] & "*") AND ((tblItems.MfgLoc) Like [Forms]![frmDataSelector]![MfgLocLimit] & "*") AND ((tblRoutings.WorkCenter) Like [Forms]![frmDataSelector]![WorkCenterLimit] & "*") AND ((tblWorkCenters.GeneralDesc) Like [Forms]![frmDataSelector]![OperationTypeSelector] & "*"));

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    That looks fine to me. Should work. Try removing the top line and run (i.e. make it a select query). Make changes to a combo, tab out of the combo and run. Are you sure it doesn't filter?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2006
    Posts
    37
    I changed it back to a select query and it still behaves the same way. Yes I am as sure as I can be that it doesn't filter... I know for a fact the total number of records in the base table and I get that same record count when I pull the query.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,910
    Is your control bound to a different column than the one displayed?

    What happens if you change your SQL to;
    Code:
    SELECT [Forms]![frmDataSelector]![GTCLimit] AS TheCombo FROM (tblItems INNER JOIN tblRoutings ON tblItems.Item = tblRoutings.Item) INNER JOIN tblWorkCenters ON tblRoutings.WorkCenter = tblWorkCenters.WorkCenter WHERE (((tblItems.GTC) Like [Forms]![frmDataSelector]![GTCLimit] & "*") AND ((tblItems.MfgLoc) Like [Forms]![frmDataSelector]![MfgLocLimit] & "*") AND ((tblRoutings.WorkCenter) Like [Forms]![frmDataSelector]![WorkCenterLimit] & "*") AND ((tblWorkCenters.GeneralDesc) Like [Forms]![frmDataSelector]![OperationTypeSelector] & "*"));
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Feb 2006
    Posts
    37
    All of my selection combo boxes are bound to column one. I am pretty sure this is right seeing as how each combo box only contains either one column or one data column and a limiting where criteria

  14. #14
    Join Date
    Feb 2006
    Posts
    37
    Sorry I didn't see the code you attached in your last reply before I reposted.

    I will try out the change but do I need to change that commans Ascombo? I don't recognize it...

  15. #15
    Join Date
    Feb 2006
    Posts
    37
    On a side note it does filter properly when the Like ___ "*" comments are removed from the filter criteria but I run into the same problem as the previous poster in that I need to allow some of the cmobo boxes to be left blank...

Posting Permissions

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