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.
