docmd.OpenReport

[boliches64]

I am trying to open a report to view an invoice for a specific invoice number. I have looked on the net and todate have not been able to resolve my problem! Help please,

My code is:

DoCmd.OpenReport strReport, acPreview, , 
Forms!frmInvoiceQ.BuyersInvoiceNo = Me.Text296

Text296 holds the required invoice number on Forms!frmMainFo rm

The query used to provide the data (qryCreateInvoi ceBuy) looks for [Forms]![frmInvoiceQ].[BuyersInvoice] to provide the filter (the invoice number), hopes this makes sense.

Thanks in anticipation

[EManning]

What's the Record Source for the report? I believe the WHERE claus (Forms!frmInvoi ceQ.BuyersInvoi ceNo = Me.Text296) has to associate with the reports' record source. And the record source would not be a form....

[NeoPa]

The more usual way of doing this is to design a report that CAN show ALL items. The underlying query would not be filtered in this case.

The report then has a filter applied when it is opened. The WhereCondition parameter (of DoCmd.OpenRepor t()) specifies the filtering to be applied.

[boliches64]

What's the Record Source for the report? I believe the WHERE claus (Forms!frmInvoi ceQ.BuyersInvoi ceNo = Me.Text296) has to associate with the reports' record source. And the record source would not be a form....

The record source is a query(qryCreate InvoiceBuy) which looks for the filter (invoice no) the location of the invoice no being Forms!frmInvoic eQ.BuyersInvoic eNo = Me.Text296.

[boliches64]

The more usual way of doing this is to design a report that CAN show ALL items. The underlying query would not be filtered in this case.

The report then has a filter applied when it is opened. The WhereCondition parameter (of DoCmd.OpenRepor t()) specifies the filtering to be applied.

If the record source of all data for the report comes via a query would your suggestion still be possible? I think that this is pretty much as I have it at the moment

[NeoPa]

It certainly would. It doesn't sound like what you've got to me though.

In your setup you filter within the query itself (with a reference to form control). In the standard way the filter is applied on opening the report object.

[boliches64]

It certainly would. It doesn't sound like what you've got to me though.

In your setup you filter within the query itself (with a reference to form control). In the standard way the filter is applied on opening the report object.

I think I should have mentioned that I am using SQL to run the docmd. When it is run I get a message box "Enter parameter Value" - "Forms!frmInvoi ceQ.BuyersInvoi ce", the script I am using that gets this message is:

strReport = "rptInvoiceBuy"

DoCmd.OpenReport strReport, acPreview

I am struugling to get the relaevant Invoice number that I am asked for in the Message Box vis SQL

[NeoPa]

I think we just found our way back to the beginning again.

I was talking about a version withOUT the reference in the underlying SQL, but with a WhereCondition in the OpenReport line instead.

I'm happy to answer any questions related to that.

[boliches64]

Any possibility that you could give me an idea of the code I should use. I thought my original message was using the "Where" clause ie. DoCmd.OpenRepor t strReport, acPreview, , Forms!frmInvoic eQ.BuyersInvoic eNo = Me.Text29

(Forms!frmInvoi ceQ.BuyersInvoi ceNo = Me.Text29 = the "where" )

Thanks

[NeoPa]

If you look at the first post you will see that the Forms... is a separate line (not a continuation).

The form of your WHERE clause is also invalid as the left-side of the "=" should refer to something from the record of your report's Record Source.

[NeoPa]

Any possibility that you could give me an idea of the code I should use.

To help with this I would need to know the name of the field in your Record Source that you want to filter on.

[GazMathias]

Any possibility that you could give me an idea of the code I should use. I thought my original message was using the "Where" clause ie. DoCmd.OpenRepor t strReport, acPreview, , Forms!frmInvoic eQ.BuyersInvoic eNo = Me.Text29

(Forms!frmInvoi ceQ.BuyersInvoi ceNo = Me.Text29 = the "where" )

Thanks

Absolute simplest way is:

DoCmd.OpenReport "SomeReportName", acViewNormal,, "[FieldToFilerOn]= " & Me![SomeFieldOnSomeForm]

Where FieldToFilterOn is a field on the report, in your case InvoiceID or something and SomeFieldOnSome Form would be your Text29.

Also make sure that there are no filters in the report's SQL Record Source. The bit that says "Criteria" in the Query Designer, where it sounds as if you have typed in a [Forms]![SomeField] statement.

Gaz.

[NeoPa]

To help with this I would need to know the name of the field in your Record Source that you want to filter on.

It's also important for the WhereCondition parameter to know whether your invoice number field is numeric string to determine what, if any, delimiters to use.

[boliches64]

It's also important for the WhereCondition parameter to know whether your invoice number field is numeric string to determine what, if any, delimiters to use.

Thanks Gaz and NeoPa!!

You have yet again pointed me in the right direction, and the problem has now been solved. It turned out to be a little more involved than I first thought as I had to direct the code dependant on the invoice being for the buyer or seller! I know this is of no matter to you guys but if you want me to post the script I will be happy to do so.

Again my thanks.

Bruce (UK)

[NeoPa]

You're welcome Bruce :) Welcome to Bytes!

PS. Posting the solution is encouraged, as it enables others with similar problems to know that it was resolved and how.

-NeoPa (UK too)