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

Problems faced with Report

P: 9
Hi guys, as the title suggests, I'm facing some problems with the report.

I have a form (createInvoiceForm) whereby the user can enter the invoice's details and then click on the Add New Record button (btnAddRecord). The data will be saved into the table (invoiceTable) and the user will be brought to the print preview of the report (invoice), showing the record which he has just added.

In the createInvoiceForm, there is a combobox (Location) which allows the user to select "Local" or "Overseas". The selection will affect the footer of the report for that particular record. The Footer consists of lblLocal1, lblLocal2, lblOverseas2 and lblOverseas3. So when a user selects "Local", in the report, lblOverseas2 and lblOverseas 3 should be hidden. Likewise for "Overseas", lblLocal1 and lblLocal2 should be hidden.

The report is based on a query (report) and is sorted by the Invoice Running Number in Ascending order.

The invoiceTable has a unique field (InvoiceID1) which is of a text datatype.

Here is my code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnAddRecord_Click()
  2. cName.Value = ""
  3. DoCmd.GoToRecord , , acNewRec
  4. Dim strReportName As String
  5.     Dim strCriteria As String
  6. strReportName = "invoice"
  7. strCriteria = "[InvoiceID1]= '" & Me.txtInvoiceID1 & "'"
  8. DoCmd.OpenReport strReportName, acViewPreview, , strCriteria
  9. If ((Location) = "Overseas") Then
  10. [Reports]![invoice]![lblOverseas2].Visible = True
  11. [Reports]![invoice]![lblOverseas3].Visible = True
  12. [Reports]![invoice]![lblLocal2].Visible = False
  13. Else
  14. If ((Location) = "Local") Then
  15. [Reports]![invoice]![lblOverseas2].Visible = False
  16. [Reports]![invoice]![lblOverseas3].Visible = False
  17. [Reports]![invoice]![lblLocal2].Visible = True
  18. End If
  19. End If
  20.  
  21. Exit_Command67_Click:
  22.     Exit Sub
  23. Err_Command67_Click:
  24. MsgBox Err.Description
  25. Resume Exit_Command67_Click
  26. End Sub
The first problem faced is that when the user clicks on btnAddRecord, a blank print preview is shown. However, when i removed this line of code
Expand|Select|Wrap|Line Numbers
  1. strCriteria = "[InvoiceID1]= '" & Me.txtInvoiceID1 & "'"
, the user can view all the records in the invoiceTable. It's just that the first record he sees is the invoice with the Running Number 001 and not the record he has just added.

The second problem is that the footer does not reflect the selection in Location. For example, for invoice A, the location selected is "Overseas" and its running number is 120. When the user adds this record, in the print preview, the invoice with the running number 119 will show lblOverseas2 and lblOverseas3 instead of invoiceA, even though its location selected is "Local". Likewise for all the other records, the previous running number's record will display the footer according to the selection made in the current record.
Feb 4 '09 #1
Share this Question
Share on Google+
7 Replies


Expert 100+
P: 1,287
When you do
DoCmd.GoToRecord , , acNewRec
does it change or clear
[Me.txtInvoiceID1] and [Location]?
Feb 4 '09 #2

ADezii
Expert 5K+
P: 8,638
Try a change in Syntax before doing anything else:
Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim strReportName As String
  3. Dim strCriteria As String
  4. Dim strLocation As String
  5. Dim strInvoiceID As String
  6. '
  7. cName.Value = ""
  8. '
  9. 'Must grab the values contained within these Controls prior
  10. 'to advancing to a New Record
  11. strLocation = Me![Location]
  12. strInvoiceID = Me![txtInvoiceID1]
  13. '
  14. DoCmd.GoToRecord , , acNewRec
  15. '
  16. strReportName = "invoice"
  17. strCriteria = "[InvoiceID1]= '" & strInvoiceID & "'"
  18. '
  19. DoCmd.OpenReport strReportName, acViewPreview, , strCriteria
  20. '
  21. If strLocation = "Overseas" Then
  22.   [Reports]![invoice]![lblOverseas2].Visible = True
  23.   [Reports]![invoice]![lblOverseas3].Visible = True
  24.   [Reports]![invoice]![lblLocal2].Visible = False
  25. Else
  26.   [Reports]![invoice]![lblOverseas2].Visible = False
  27.   [Reports]![invoice]![lblOverseas3].Visible = False
  28.   [Reports]![invoice]![lblLocal2].Visible = True
  29. End If
  30.  
Feb 4 '09 #3

P: 9
Thanks!! It worked. =)
Feb 5 '09 #4

ADezii
Expert 5K+
P: 8,638
@Evanescent
Glad it all worked out for you. The critical question is, Do you know why it worked, and your Version didn't?
Feb 5 '09 #5

P: 9
I have totally no idea why it doesn't work.
Feb 5 '09 #6

ADezii
Expert 5K+
P: 8,638
@Evanescent
In Lines 3 to 7 in your Original Post, you are trying to grab Values in Fields after having advanced to a New Record. Naturally, these Fields do not contain any Values, so you must assign these Field Values to Variables prior to advancing to a New Record, where they then can be used. Make sense?
Expand|Select|Wrap|Line Numbers
  1. DoCmd.GoToRecord , , acNewRec 
  2. '
  3. Dim strReportName As String 
  4. Dim strCriteria As String 
  5. '
  6. strReportName = "invoice" 
  7. strCriteria = "[InvoiceID1]= '" & Me.txtInvoiceID1 & "'" 
  8.  
Feb 5 '09 #7

P: 9
Yep! Thanks for explaining. =)
Feb 5 '09 #8

Post your reply

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