Problems faced with Report

[Evanescent]

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:

Private Sub btnAddRecord_Click()
cName.Value = ""
DoCmd.GoToRecord , , acNewRec
Dim strReportName As String
    Dim strCriteria As String
strReportName = "invoice"
strCriteria = "[InvoiceID1]= '" & Me.txtInvoiceID1 & "'"
DoCmd.OpenReport strReportName, acViewPreview, , strCriteria
If ((Location) = "Overseas") Then
[Reports]![invoice]![lblOverseas2].Visible = True
[Reports]![invoice]![lblOverseas3].Visible = True
[Reports]![invoice]![lblLocal2].Visible = False
Else
If ((Location) = "Local") Then
[Reports]![invoice]![lblOverseas2].Visible = False
[Reports]![invoice]![lblOverseas3].Visible = False
[Reports]![invoice]![lblLocal2].Visible = True
End If
End If
 
Exit_Command67_Click:
    Exit Sub
Err_Command67_Click:
MsgBox Err.Description
Resume Exit_Command67_Click
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

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.

[ChipR]

When you do
DoCmd.GoToRecord , , acNewRec
does it change or clear
[Me.txtInvoiceID1] and [Location]?

[ADezii]

Try a change in Syntax before doing anything else:

 
Dim strReportName As String
Dim strCriteria As String
Dim strLocation As String
Dim strInvoiceID As String
'
cName.Value = ""
'
'Must grab the values contained within these Controls prior
'to advancing to a New Record
strLocation = Me![Location]
strInvoiceID = Me![txtInvoiceID1]
'
DoCmd.GoToRecord , , acNewRec
'
strReportName = "invoice"
strCriteria = "[InvoiceID1]= '" & strInvoiceID & "'"
'
DoCmd.OpenReport strReportName, acViewPreview, , strCriteria
'
If strLocation = "Overseas" Then
  [Reports]![invoice]![lblOverseas2].Visible = True
  [Reports]![invoice]![lblOverseas3].Visible = True
  [Reports]![invoice]![lblLocal2].Visible = False
Else
  [Reports]![invoice]![lblOverseas2].Visible = False
  [Reports]![invoice]![lblOverseas3].Visible = False
  [Reports]![invoice]![lblLocal2].Visible = True
End If
 

[Evanescent]

Thanks!! It worked. =)

[ADezii]

@Evanescent
Glad it all worked out for you. The critical question is, Do you know why it worked, and your Version didn't?

[Evanescent]

I have totally no idea why it doesn't work.

[ADezii]

@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?

DoCmd.GoToRecord , , acNewRec 
'
Dim strReportName As String 
Dim strCriteria As String 
'
strReportName = "invoice" 
strCriteria = "[InvoiceID1]= '" & Me.txtInvoiceID1 & "'" 
 

[Evanescent]

Yep! Thanks for explaining. =)