cancelling invoice if user says so..

  • Thread starter Thread starter Jon
  • Start date Start date
J

Jon

Hi I have some code, reproduced below that should prevent an invoice
printing with a 0 rate.

however, if, for example, there are two items with zero rate the code asks
the question twice but carries on printing anyway!

Can anyone advise how I change the code to stop when the user presses yes?

Thanks

Jon

================================== The Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim response As String
If CalculatedCost = "0" Or Len(Trim$(CalculatedCost & vbNullString)) = 0
Then
response = MsgBox("There is an cost of 0 (zero) or empty, the invoice cannot
proceded, would you like to fix that now?", vbYesNo + vbQuestion +
vbDefaultButton2)
If response = vbYes Then ' User chose Yes.
DoCmd.Close acReport, Me.Name
Dim stLinkCriteria As String
stLinkCriteria = "[InvoiceId]=" & Me![InvoiceId]
DoCmd.OpenForm "FrmInvoiceBuilder", , , stLinkCriteria, , acDialog
Exit Sub
Else ' User chose No.
DoCmd.Close acReport, Me.Name
Exit Sub
End If
End If
End Sub
 
Jon said:
Hi I have some code, reproduced below that should prevent an invoice
printing with a 0 rate.

however, if, for example, there are two items with zero rate the code asks
the question twice but carries on printing anyway!

Can anyone advise how I change the code to stop when the user presses yes?

Thanks

Jon

================================== The Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Click to expand...

I'll let you do the testing....since I have not done this....but what happens
iff you set Cancel = True. Also, what is the value of FormatCount on the second
pass.

Is there some way you can check the recordsource prior to printing to exclude
those with a zero rate?
Dim response As String
If CalculatedCost = "0" Or Len(Trim$(CalculatedCost & vbNullString)) = 0
Then
response = MsgBox("There is an cost of 0 (zero) or empty, the invoice cannot
proceded, would you like to fix that now?", vbYesNo + vbQuestion +
vbDefaultButton2)
If response = vbYes Then ' User chose Yes.
DoCmd.Close acReport, Me.Name
Dim stLinkCriteria As String
stLinkCriteria = "[InvoiceId]=" & Me![InvoiceId]
DoCmd.OpenForm "FrmInvoiceBuilder", , , stLinkCriteria, , acDialog
Exit Sub
Else ' User chose No.
DoCmd.Close acReport, Me.Name
Exit Sub
End If
End If
End Sub
Click to expand...
 
Jon,
I'd do this validation earlier in your process. I'd probably write a
validation routine that looked for zero balance invoices and told the user
something like, "Invoices x, y, and z have zero balances and will be
skipped." Then run the report with a change to the WHERE clause of the
SELECT statement that the report is bound to so it includes a phrase like
"BALANCE > 0" and zero balance invoices are not included in the printed
result set. Then only the invoices that have a balance will print and the
user has a chance to go fix the ones that didn't print.
 
Hi I have some code, reproduced below that should prevent an invoice
printing with a 0 rate.

however, if, for example, there are two items with zero rate the code asks
the question twice but carries on printing anyway!

Can anyone advise how I change the code to stop when the user presses yes?
Click to expand...
<sip> Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

I'll let you do the testing....since I have not done this....but what
happens
iff you set Cancel = True. Also, what is the value of FormatCount on the
second
pass.

Is there some way you can check the recordsource prior to printing to
exclude
those with a zero rate?

<SNIP>

the FormatCount was put there 'by access' when I created the Event,

I tried

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim response As String
Set Cancel = True

Is that what you meant - this actually skips the whole function without
prompting the user and goes straight to print preview

Jon
 
Alan,

The balances have values >0, it's some of the individual items that don't
have a value

Jon

Jon,
I'd do this validation earlier in your process. I'd probably write a
validation routine that looked for zero balance invoices and told the user
something like, "Invoices x, y, and z have zero balances and will be
skipped." Then run the report with a change to the WHERE clause of the
SELECT statement that the report is bound to so it includes a phrase like
"BALANCE > 0" and zero balance invoices are not included in the printed
result set. Then only the invoices that have a balance will print and the
user has a chance to go fix the ones that didn't print.
 
I have found that the beet option was indeed to trap the error in code prior
to printing the report - had to use some SQL but it worked.

Jon

Jon said:
Hi I have some code, reproduced below that should prevent an invoice
printing with a 0 rate.

however, if, for example, there are two items with zero rate the code asks
the question twice but carries on printing anyway!

Can anyone advise how I change the code to stop when the user presses yes?

Thanks

Jon

================================== The Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Click to expand...

I'll let you do the testing....since I have not done this....but what
happens
iff you set Cancel = True. Also, what is the value of FormatCount on the
second
pass.

Is there some way you can check the recordsource prior to printing to
exclude
those with a zero rate?
Dim response As String
If CalculatedCost = "0" Or Len(Trim$(CalculatedCost & vbNullString)) = 0
Then
response = MsgBox("There is an cost of 0 (zero) or empty, the invoice cannot
proceded, would you like to fix that now?", vbYesNo + vbQuestion +
vbDefaultButton2)
If response = vbYes Then ' User chose Yes.
DoCmd.Close acReport, Me.Name
Dim stLinkCriteria As String
stLinkCriteria = "[InvoiceId]=" & Me![InvoiceId]
DoCmd.OpenForm "FrmInvoiceBuilder", , , stLinkCriteria, , acDialog
Exit Sub
Else ' User chose No.
DoCmd.Close acReport, Me.Name
Exit Sub
End If
End If
End Sub
Click to expand...
 
Jon,
Even better. Your validation would run against the line item table instead
of the invoice header table. But . . . what if a sales person is giving
away something? "I'll throw in a mouse free if you buy the computer today,"
or some such. The invoice should print in spite of the zeroed line item
because it's a legitimate item and a legitimate price. Anyway, pull a query
of any line items with zero amounts and return the invoice header rows that
contain the zeroed line items. Then tell the user these invoices won't
print and add a WHERE clause to your invoice query that leaves out any
invoices with zeroed line items.
Though I'd love to see the face of the sales manager when he figures out
that his sales are stuck in zero balance hell and can't get out and he won't
get his commission this pay period. Sales people don't take it too well
when you tell them they are working for free.

Jon said:
Alan,

The balances have values >0, it's some of the individual items that don't
have a value

Jon

Jon,
I'd do this validation earlier in your process. I'd probably write a
validation routine that looked for zero balance invoices and told the user
something like, "Invoices x, y, and z have zero balances and will be
skipped." Then run the report with a change to the WHERE clause of the
SELECT statement that the report is bound to so it includes a phrase like
"BALANCE > 0" and zero balance invoices are not included in the printed
result set. Then only the invoices that have a balance will print and the
user has a chance to go fix the ones that didn't print.

Jon said:
Hi I have some code, reproduced below that should prevent an invoice
printing with a 0 rate.

however, if, for example, there are two items with zero rate the code asks
the question twice but carries on printing anyway!

Can anyone advise how I change the code to stop when the user presses yes?

Thanks

Jon

================================== The Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim response As String
If CalculatedCost = "0" Or Len(Trim$(CalculatedCost & vbNullString)) = 0
Then
response = MsgBox("There is an cost of 0 (zero) or empty, the invoice cannot
proceded, would you like to fix that now?", vbYesNo + vbQuestion +
vbDefaultButton2)
If response = vbYes Then ' User chose Yes.
DoCmd.Close acReport, Me.Name
Dim stLinkCriteria As String
stLinkCriteria = "[InvoiceId]=" & Me![InvoiceId]
DoCmd.OpenForm "FrmInvoiceBuilder", , , stLinkCriteria, , acDialog
Exit Sub
Else ' User chose No.
DoCmd.Close acReport, Me.Name
Exit Sub
End If
End If
End Sub
Click to expand...
Click to expand...
 
This guy gives nothing away!

Thanks for your help

Jon (Webb!)

Jon,
Even better. Your validation would run against the line item table instead
of the invoice header table. But . . . what if a sales person is giving
away something? "I'll throw in a mouse free if you buy the computer today,"
or some such. The invoice should print in spite of the zeroed line item
because it's a legitimate item and a legitimate price. Anyway, pull a query
of any line items with zero amounts and return the invoice header rows that
contain the zeroed line items. Then tell the user these invoices won't
print and add a WHERE clause to your invoice query that leaves out any
invoices with zeroed line items.
Though I'd love to see the face of the sales manager when he figures out
that his sales are stuck in zero balance hell and can't get out and he won't
get his commission this pay period. Sales people don't take it too well
when you tell them they are working for free.

Jon said:
Alan,

The balances have values >0, it's some of the individual items that don't
have a value

Jon

Jon,
I'd do this validation earlier in your process. I'd probably write a
validation routine that looked for zero balance invoices and told the user
something like, "Invoices x, y, and z have zero balances and will be
skipped." Then run the report with a change to the WHERE clause of the
SELECT statement that the report is bound to so it includes a phrase like
"BALANCE > 0" and zero balance invoices are not included in the printed
result set. Then only the invoices that have a balance will print and the
user has a chance to go fix the ones that didn't print.

Jon said:
Hi I have some code, reproduced below that should prevent an invoice
printing with a 0 rate.

however, if, for example, there are two items with zero rate the code asks
the question twice but carries on printing anyway!

Can anyone advise how I change the code to stop when the user presses yes?

Thanks

Jon

================================== The Code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim response As String
If CalculatedCost = "0" Or Len(Trim$(CalculatedCost & vbNullString)) = 0
Then
response = MsgBox("There is an cost of 0 (zero) or empty, the invoice cannot
proceded, would you like to fix that now?", vbYesNo + vbQuestion +
vbDefaultButton2)
If response = vbYes Then ' User chose Yes.
DoCmd.Close acReport, Me.Name
Dim stLinkCriteria As String
stLinkCriteria = "[InvoiceId]=" & Me![InvoiceId]
DoCmd.OpenForm "FrmInvoiceBuilder", , , stLinkCriteria, , acDialog
Exit Sub
Else ' User chose No.
DoCmd.Close acReport, Me.Name
Exit Sub
End If
End If
End Sub
Click to expand...
Click to expand...
 
Back
Top