"Filter By Form" does not open Report

[oakleaf8]

I have a form that the Users filter on by using "Filter By Form". On the form are command buttons to open reports using the "Filter By Form" filter. Simple code is:

DoCmd.OpenReport "<Report>", , , Me.Filter

However, there are cbo's on the form with lookups that mess this up. Are there any ways around this, or do I have to convert fields in my main table from the primary key integer from the lookup table to the actual text name?

 

[JoeAtWork]

The query for your report needs to include the fields that are in the form's Filter.

 

[oakleaf8]

Does not work. I'll try to simplify...

Let's say I have a form, "frmOrders", for entry into table "tblOrders". Form "frmOrders" has a cbo field for "tblOrders.CustID" which allows me to choose Customer Name from dropdown list with lookup to "tblCustomer". Table "tblCustomer" has two fields, "CustID" and "CustName".

Now let's say I have a simple report with two fields, "OrderNumber" and "CustName". Form "frmOrders" has a command button to open this report from the filter generated after I do a "Filter By Form". The error looks like this:

Enter Parameter Value
Lookup_CustID.CustName

What do I need to do to prevent this error?

 

[JoeAtWork]

The standard way is to include CustID in the query of the report (you don't have to make it visible on the report).

Then you should simply be able to open the report based on the current customer on the form:

DoCmd.OpenReport "<Report>", , , cboCustomer

I am assuming here that the combo's bound field is CustID.

 

[Remou]

Do you mean:


DoCmd.OpenReport "<Report>", , , "CustID=" & Me.cboCustomer

Where CustID is used in the report query and is numeric.

You may wish to consider acViewPreview.

 

[JoeAtWork]

Yes, I meant to use what Remou wrote for the OpenReport method.

 

[oakleaf8]

Thanks guys, but the example I used was for trying to explain my situation simply. I'd like passing the form's filter to the chosen report to be a given. (The reports use the same table that the form uses.) That way I'm using Access's "Filter By Form" capability to generate filter criteria rather than creating a new form. I actually have about 40 fields on the form - seventeen of these being lookup (cbo) fields.

Let's say I filter on one of those lookup fields and a couple others and run:
DoCmd.OpenReport "<Report>", acViewPreview, , Me.filter

The filter that gets passed looks like this:

Me.filter = ((tblOrders.OrderID Like "*R100*") AND (Lookup_CustID.CustName="PCTech") AND (tblOrders.[ShipToState] Like "MI"))

And the error I get is:
Enter Parameter Value
Lookup_CustID.CustName

even when CustID is used in the report query and is numeric. If I do a "Filter By Form" without filtering on any of the lookup (cbo) fields it works great!

Now I could run code to look for "Lookup_" and delete this and up to the first period following in the filter string and include CustName in the Report Query. It just seems this way should work.

Am I making sense?

 

[Remou]

The report would have to have a table in the query on which it is based called Lookup_CustID, which apparently it does not.