If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > VBA open filtered query

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Apr 2005
Location: Zagreb - Croatia
Posts: 372
In query put Criteria: [Forms]![YourFormName]![FieldName]
and
DoCmd.OpenReport "rptDriver"
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Apr 2005
Location: Zagreb - Croatia
Posts: 372
In that case in qury Criteria put:
Like [Forms]![YourFormname]![fieldname] & "*"
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Mar 2005
Posts: 55
GENIUS!

thanks a heap, that works a treat
Reply With Quote
  #6 (permalink)  
Old
King of Understatement
 
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:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #8 (permalink)  
Old
King of Understatement
 
Join Date: Feb 2004
Location: One Flump in One Place
Posts: 14,910
Can you post your SQL?
__________________
Testimonial:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
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] & "*"));
Reply With Quote
  #10 (permalink)  
Old
King of Understatement
 
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:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #12 (permalink)  
Old
King of Understatement
 
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:
Quote:
pootle flump
ur codings are working excelent.
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #14 (permalink)  
Old
Registered User
 
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...
Reply With Quote
  #15 (permalink)  
Old
Registered User
 
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...
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On