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!frmMainForm

The query used to provide the data (qryCreateInvoiceBuy) 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!frmInvoiceQ.BuyersInvoiceNo = 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.OpenReport()) specifies the filtering to be applied.

[boliches64]

What's the Record Source for the report? I believe the WHERE claus (Forms!frmInvoiceQ.BuyersInvoiceNo = 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(qryCreateInvoiceBuy) which looks for the filter (invoice no) the location of the invoice no being Forms!frmInvoiceQ.BuyersInvoiceNo = 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.OpenReport()) 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!frmInvoiceQ.BuyersInvoice", 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.OpenReport strReport, acPreview, , Forms!frmInvoiceQ.BuyersInvoiceNo = Me.Text29

(Forms!frmInvoiceQ.BuyersInvoiceNo = 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.OpenReport strReport, acPreview, , Forms!frmInvoiceQ.BuyersInvoiceNo = Me.Text29

(Forms!frmInvoiceQ.BuyersInvoiceNo = 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 SomeFieldOnSomeForm 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)

[boliches64]

My solution :

Dim strInvoice As String
Dim strInvoiceNo As String
Dim strReport As String
Dim strReportSell As String
Dim strBuyersInvoiceNo As String
Dim strSQL As String
Dim strSQL1 As String
Dim db      As DAO.Database
Dim rs      As DAO.Recordset
 
strBuyersInvoiceNo = Me.Text296
    Set db = CurrentDb()
strSQL = "SELECT tblFaxedDealsBP.BuyersInvoiceNo FROM tblFaxedDealsBP WHERE tblFaxedDealsBP.BuyersInvoiceNo= " + "'" & strBuyersInvoiceNo & "'"
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
 
On Error GoTo Sell_Invoice  'OnError occurs when no results are found in strSQL'
    BuyInv = rs!BuyersInvoiceNo
        strReportSell = "rptInvoiceSell"
            strReport = "rptInvoice"
                If Me.Text296 = BuyInv Then GoTo Buy_Invoice Else GoTo Exit_Invoice
 
Buy_Invoice:
DoCmd.OpenReport strReport, acPreview, , "[BuyersInvoiceNo] =  " + "'" & strBuyersInvoiceNo & "'"
    Set rs = Nothing
        Set db = Nothing
            Exit Sub
 
Exit_Invoice:
  Set rs = Nothing
    Set db = Nothing
        Exit Sub
 
Sell_Invoice:
  Set db = CurrentDb()
    strSQL1 = "SELECT tblFaxedDealsBP.InvoiceNo FROM tblFaxedDealsBP WHERE tblFaxedDealsBP.InvoiceNo= " + "'" & strBuyersInvoiceNo & "'"
        Set rs = db.OpenRecordset(strSQL1, dbOpenDynaset)
            SellInv = rs!InvoiceNo
                strReportSell = "rptInvoiceSell"
                    DoCmd.OpenReport strReportSell, acPreview, , "[InvoiceNo] =  " + "'" & strBuyersInvoiceNo & "'"
                        Set rs = Nothing
                            Set db = Nothing
                                 Exit Sub

I am sure the script could be cleaner but hey its working!

[NeoPa]

Thanks for that Bruce :)

As you have posted that, may I give you a tip about code indenting :
Generally only ever indent code further in to indicate a change of some form.

That might be the indented code is all within an If statement. EG :

If A = B Then
  'Equals code
Else
  'Not equals code
End If

Other structures are also typically indented. It should help to indicate to the reader the structure of the code. There are different sets of rules that coders follow, but common to all is that it generally helps the reader to follow what's happening where.

Anyway, Welcome to Bytes!

[NeoPa]

I decided it may be easier to understand my point if I reformatted your code in the style I use. See if that makes it easier for you to read. The breaks and the flow are easier to spot.

Private Sub ?????()
  Dim strInvoice As String
  Dim strInvoiceNo As String
  Dim strReport As String
  Dim strReportSell As String
  Dim strBuyersInvoiceNo As String
  Dim strSQL As String
  Dim strSQL1 As String
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
 
  strBuyersInvoiceNo = Me.Text296
  Set db = CurrentDb()
  strSQL = "SELECT tblFaxedDealsBP.BuyersInvoiceNo " & _
           "FROM tblFaxedDealsBP " & _
           "WHERE tblFaxedDealsBP.BuyersInvoiceNo='" & strBuyersInvoiceNo & "'"
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
 
  On Error GoTo Sell_Invoice  'OnError occurs when no results are found in strSQL'
  BuyInv = rs!BuyersInvoiceNo
  strReportSell = "rptInvoiceSell"
  strReport = "rptInvoice"
  If Me.Text296 = BuyInv Then GoTo Buy_Invoice
  GoTo Exit_Invoice
 
Buy_Invoice:
  DoCmd.OpenReport strReport, acPreview, , "[BuyersInvoiceNo]='" & strBuyersInvoiceNo & "'"
  Set rs = Nothing
  Set db = Nothing
  Exit Sub
 
Exit_Invoice:
  Set rs = Nothing
  Set db = Nothing
  Exit Sub
 
Sell_Invoice:
  Set db = CurrentDb()
  strSQL1 = "SELECT tblFaxedDealsBP.InvoiceNo " & _
            "FROM tblFaxedDealsBP " & _
            "WHERE tblFaxedDealsBP.InvoiceNo='" &   strBuyersInvoiceNo & "'"
  Set rs = db.OpenRecordset(strSQL1, dbOpenDynaset)
  SellInv = rs!InvoiceNo
  strReportSell = "rptInvoiceSell"
  DoCmd.OpenReport strReportSell, acPreview, , "[InvoiceNo]='" & strBuyersInvoiceNo & "'"
  Set rs = Nothing
  Set db = Nothing
  Exit Sub
End Sub

[boliches64]

Thanks for the advice, always welcome. Other than that OK?

Regards

[NeoPa]

Other than that - just thanks for posting it :)