By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,341 Members | 1,395 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,341 IT Pros & Developers. It's quick & easy.

docmd.OpenReport

P: 17
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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenReport strReport, acPreview, , 
  2. 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
Oct 13 '08 #1
Share this Question
Share on Google+
19 Replies


P: 13
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....
Oct 13 '08 #2

NeoPa
Expert Mod 15k+
P: 31,661
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.
Oct 13 '08 #3

P: 17
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.
Oct 14 '08 #4

P: 17
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
Oct 14 '08 #5

NeoPa
Expert Mod 15k+
P: 31,661
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.
Oct 14 '08 #6

P: 17
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:
Expand|Select|Wrap|Line Numbers
  1. strReport = "rptInvoiceBuy"
  2.  
  3. DoCmd.OpenReport strReport, acPreview
I am struugling to get the relaevant Invoice number that I am asked for in the Message Box vis SQL
Oct 14 '08 #7

NeoPa
Expert Mod 15k+
P: 31,661
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.
Oct 14 '08 #8

P: 17
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
Oct 15 '08 #9

NeoPa
Expert Mod 15k+
P: 31,661
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.
Oct 15 '08 #10

NeoPa
Expert Mod 15k+
P: 31,661
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.
Oct 15 '08 #11

GazMathias
Expert 100+
P: 197
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:

Expand|Select|Wrap|Line Numbers
  1. 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.
Oct 15 '08 #12

NeoPa
Expert Mod 15k+
P: 31,661
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.
Oct 15 '08 #13

P: 17
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)
Oct 16 '08 #14

NeoPa
Expert Mod 15k+
P: 31,661
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)
Oct 16 '08 #15

P: 17
My solution :
Expand|Select|Wrap|Line Numbers
  1. Dim strInvoice As String
  2. Dim strInvoiceNo As String
  3. Dim strReport As String
  4. Dim strReportSell As String
  5. Dim strBuyersInvoiceNo As String
  6. Dim strSQL As String
  7. Dim strSQL1 As String
  8. Dim db      As DAO.Database
  9. Dim rs      As DAO.Recordset
  10.  
  11. strBuyersInvoiceNo = Me.Text296
  12.     Set db = CurrentDb()
  13. strSQL = "SELECT tblFaxedDealsBP.BuyersInvoiceNo FROM tblFaxedDealsBP WHERE tblFaxedDealsBP.BuyersInvoiceNo= " + "'" & strBuyersInvoiceNo & "'"
  14.     Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
  15.  
  16. On Error GoTo Sell_Invoice  'OnError occurs when no results are found in strSQL'
  17.     BuyInv = rs!BuyersInvoiceNo
  18.         strReportSell = "rptInvoiceSell"
  19.             strReport = "rptInvoice"
  20.                 If Me.Text296 = BuyInv Then GoTo Buy_Invoice Else GoTo Exit_Invoice
  21.  
  22. Buy_Invoice:
  23. DoCmd.OpenReport strReport, acPreview, , "[BuyersInvoiceNo] =  " + "'" & strBuyersInvoiceNo & "'"
  24.     Set rs = Nothing
  25.         Set db = Nothing
  26.             Exit Sub
  27.  
  28. Exit_Invoice:
  29.   Set rs = Nothing
  30.     Set db = Nothing
  31.         Exit Sub
  32.  
  33. Sell_Invoice:
  34.   Set db = CurrentDb()
  35.     strSQL1 = "SELECT tblFaxedDealsBP.InvoiceNo FROM tblFaxedDealsBP WHERE tblFaxedDealsBP.InvoiceNo= " + "'" & strBuyersInvoiceNo & "'"
  36.         Set rs = db.OpenRecordset(strSQL1, dbOpenDynaset)
  37.             SellInv = rs!InvoiceNo
  38.                 strReportSell = "rptInvoiceSell"
  39.                     DoCmd.OpenReport strReportSell, acPreview, , "[InvoiceNo] =  " + "'" & strBuyersInvoiceNo & "'"
  40.                         Set rs = Nothing
  41.                             Set db = Nothing
  42.                                  Exit Sub
I am sure the script could be cleaner but hey its working!
Oct 17 '08 #16

NeoPa
Expert Mod 15k+
P: 31,661
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 :
Expand|Select|Wrap|Line Numbers
  1. If A = B Then
  2.   'Equals code
  3. Else
  4.   'Not equals code
  5. 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!
Oct 17 '08 #17

NeoPa
Expert Mod 15k+
P: 31,661
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub ?????()
  2.   Dim strInvoice As String
  3.   Dim strInvoiceNo As String
  4.   Dim strReport As String
  5.   Dim strReportSell As String
  6.   Dim strBuyersInvoiceNo As String
  7.   Dim strSQL As String
  8.   Dim strSQL1 As String
  9.   Dim db As DAO.Database
  10.   Dim rs As DAO.Recordset
  11.  
  12.   strBuyersInvoiceNo = Me.Text296
  13.   Set db = CurrentDb()
  14.   strSQL = "SELECT tblFaxedDealsBP.BuyersInvoiceNo " & _
  15.            "FROM tblFaxedDealsBP " & _
  16.            "WHERE tblFaxedDealsBP.BuyersInvoiceNo='" & strBuyersInvoiceNo & "'"
  17.   Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
  18.  
  19.   On Error GoTo Sell_Invoice  'OnError occurs when no results are found in strSQL'
  20.   BuyInv = rs!BuyersInvoiceNo
  21.   strReportSell = "rptInvoiceSell"
  22.   strReport = "rptInvoice"
  23.   If Me.Text296 = BuyInv Then GoTo Buy_Invoice
  24.   GoTo Exit_Invoice
  25.  
  26. Buy_Invoice:
  27.   DoCmd.OpenReport strReport, acPreview, , "[BuyersInvoiceNo]='" & strBuyersInvoiceNo & "'"
  28.   Set rs = Nothing
  29.   Set db = Nothing
  30.   Exit Sub
  31.  
  32. Exit_Invoice:
  33.   Set rs = Nothing
  34.   Set db = Nothing
  35.   Exit Sub
  36.  
  37. Sell_Invoice:
  38.   Set db = CurrentDb()
  39.   strSQL1 = "SELECT tblFaxedDealsBP.InvoiceNo " & _
  40.             "FROM tblFaxedDealsBP " & _
  41.             "WHERE tblFaxedDealsBP.InvoiceNo='" &   strBuyersInvoiceNo & "'"
  42.   Set rs = db.OpenRecordset(strSQL1, dbOpenDynaset)
  43.   SellInv = rs!InvoiceNo
  44.   strReportSell = "rptInvoiceSell"
  45.   DoCmd.OpenReport strReportSell, acPreview, , "[InvoiceNo]='" & strBuyersInvoiceNo & "'"
  46.   Set rs = Nothing
  47.   Set db = Nothing
  48.   Exit Sub
  49. End Sub
Oct 17 '08 #18

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

Regards
Oct 17 '08 #19

NeoPa
Expert Mod 15k+
P: 31,661
Other than that - just thanks for posting it :)
Oct 17 '08 #20

Post your reply

Sign in to post your reply or Sign up for a free account.