#Error in form

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

I have a form that uses 3 subforms for it's data. The main
form has the following text boxes:

[DirectWages]
RecordSource: =nnz(IIf([QTDInputDataDirect subform].Form!
Type="Direct",[QTDInputDataDirect subform].Form!
SumOfSumOfPay,0))

[DirectHours]
RecordSource: =nnz(IIf([QTDInputDataDirect subform].Form!
Type="Direct",[QTDInputDataDirect subform].Form!
SumOfHours,0))

[IndirectWages]
RecordSource: =nnz(IIf([QTDInputDataIndirect subform].Form!
Type="Indirect",[QTDInputDataIndirect subform].Form!
SumOfSumOfPay,0))

[IndirectHours]
RecordSource: =nnz(IIf([QTDInputDataIndirect subform].Form!
Type="Indirect",[QTDInputDataIndirect subform].Form!
SumOfHours,0))

This works great as long as each emplyee has data for both
[DirectWages] and [IndirectWages]. If an employee only has
1 type of wage, then the other textbox displays #Error.
This causes a problem with another text box that I use to
total both wages:
[TotalWages]
RecordSource: =nnz([DirectWages]+[IndirectWages])

The subforms get their data from a seperate query:
QTDInputDataDirect
QTDInputDataIndirect

I would like the text boxes to display a zero when an
employee has not earned a type of wage.

The subforms are linked by the EmployeeID to the master
form.
Thanks!
 
If the subform has no records and no new records can be added there, it goes
completely blank. It's like the text box with the total is non-existent, and
so attempting to read its Value generates an error. The non-existent value
is not a Null, so Nz() cannot solve the problem.

You can use IsError() with IIf() to replace the error with zero, or you can
read the RecordCount of the subform's RecordsetClone. This example shows how
to read the value of the subform if there is one, or use zero if there is
not:

=IIf([QTDInputDataDirect subform].[Form].[RecordsetClone].[RecordCount]=0,
0,
Nz([QTDInputDataDirect subform].[Form]![SumOfSumOfPay], 0) )
 
Hi Allen,
I used the code you posted:
=IIf([QTDInputDataDirect subform].[Form].[RecordsetClone].
[RecordCount]=0, nnz([QTDInputDataDirect subform].[Form]!
[SumOfSumOfPay], 0))
and got an error:
"The expression you entered has a function containing the
wrong number of arguments"
-----Original Message-----
If the subform has no records and no new records can be added there, it goes
completely blank. It's like the text box with the total is non-existent, and
so attempting to read its Value generates an error. The non-existent value
is not a Null, so Nz() cannot solve the problem.

You can use IsError() with IIf() to replace the error with zero, or you can
read the RecordCount of the subform's RecordsetClone. This example shows how
to read the value of the subform if there is one, or use zero if there is
not:

=IIf([QTDInputDataDirect subform].[Form].[RecordsetClone]. [RecordCount]=0,
0,
Nz([QTDInputDataDirect subform].[Form]! [SumOfSumOfPay], 0) )

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form that uses 3 subforms for it's data. The main
form has the following text boxes:

[DirectWages]
RecordSource: =nnz(IIf([QTDInputDataDirect subform].Form!
Type="Direct",[QTDInputDataDirect subform].Form!
SumOfSumOfPay,0))

[DirectHours]
RecordSource: =nnz(IIf([QTDInputDataDirect subform].Form!
Type="Direct",[QTDInputDataDirect subform].Form!
SumOfHours,0))

[IndirectWages]
RecordSource: =nnz(IIf([QTDInputDataIndirect subform].Form!
Type="Indirect",[QTDInputDataIndirect subform].Form!
SumOfSumOfPay,0))

[IndirectHours]
RecordSource: =nnz(IIf([QTDInputDataIndirect subform].Form!
Type="Indirect",[QTDInputDataIndirect subform].Form!
SumOfHours,0))

This works great as long as each emplyee has data for both
[DirectWages] and [IndirectWages]. If an employee only has
1 type of wage, then the other textbox displays #Error.
This causes a problem with another text box that I use to
total both wages:
[TotalWages]
RecordSource: =nnz([DirectWages]+[IndirectWages])

The subforms get their data from a seperate query:
QTDInputDataDirect
QTDInputDataIndirect

I would like the text boxes to display a zero when an
employee has not earned a type of wage.

The subforms are linked by the EmployeeID to the master
form.
Thanks!
Click to expand...


.
Click to expand...
 
What's nnz()?

IIf() takes 3 arguments:
- a condition,
- what to do if it's true,
- what to do if it's not true.

You are trying to say:
If the record count is zero,
use zero
else use the text box value in the subform.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Hi Allen,
I used the code you posted:
=IIf([QTDInputDataDirect subform].[Form].[RecordsetClone].
[RecordCount]=0, nnz([QTDInputDataDirect subform].[Form]!
[SumOfSumOfPay], 0))
and got an error:
"The expression you entered has a function containing the
wrong number of arguments"
-----Original Message-----
If the subform has no records and no new records can be added there, it goes
completely blank. It's like the text box with the total is non-existent, and
so attempting to read its Value generates an error. The non-existent value
is not a Null, so Nz() cannot solve the problem.

You can use IsError() with IIf() to replace the error with zero, or you can
read the RecordCount of the subform's RecordsetClone. This example shows how
to read the value of the subform if there is one, or use zero if there is
not:

=IIf([QTDInputDataDirect subform].[Form].[RecordsetClone]. [RecordCount]=0,
0,
Nz([QTDInputDataDirect subform].[Form]! [SumOfSumOfPay], 0) )

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form that uses 3 subforms for it's data. The main
form has the following text boxes:

[DirectWages]
RecordSource: =nnz(IIf([QTDInputDataDirect subform].Form!
Type="Direct",[QTDInputDataDirect subform].Form!
SumOfSumOfPay,0))

[DirectHours]
RecordSource: =nnz(IIf([QTDInputDataDirect subform].Form!
Type="Direct",[QTDInputDataDirect subform].Form!
SumOfHours,0))

[IndirectWages]
RecordSource: =nnz(IIf([QTDInputDataIndirect subform].Form!
Type="Indirect",[QTDInputDataIndirect subform].Form!
SumOfSumOfPay,0))

[IndirectHours]
RecordSource: =nnz(IIf([QTDInputDataIndirect subform].Form!
Type="Indirect",[QTDInputDataIndirect subform].Form!
SumOfHours,0))

This works great as long as each emplyee has data for both
[DirectWages] and [IndirectWages]. If an employee only has
1 type of wage, then the other textbox displays #Error.
This causes a problem with another text box that I use to
total both wages:
[TotalWages]
RecordSource: =nnz([DirectWages]+[IndirectWages])

The subforms get their data from a seperate query:
QTDInputDataDirect
QTDInputDataIndirect

I would like the text boxes to display a zero when an
employee has not earned a type of wage.

The subforms are linked by the EmployeeID to the master
form.
Thanks!
Click to expand...


.
Click to expand...
Click to expand...
 
nnz is a module:
Function nnz(TestValue As Variant) As Variant
'Not Numeric return zero
If Not (IsNumeric(TestValue)) Then
nnz = 0
Else
nnz = TestValue
End If
End Function
-----Original Message-----
What's nnz()?

IIf() takes 3 arguments:
- a condition,
- what to do if it's true,
- what to do if it's not true.

You are trying to say:
If the record count is zero,
use zero
else use the text box value in the subform.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Hi Allen,
I used the code you posted:
=IIf([QTDInputDataDirect subform].[Form]. [RecordsetClone].
[RecordCount]=0, nnz([QTDInputDataDirect subform]. [Form]!
[SumOfSumOfPay], 0))
and got an error:
"The expression you entered has a function containing the
wrong number of arguments"
-----Original Message-----
If the subform has no records and no new records can be added there, it goes
completely blank. It's like the text box with the total is non-existent, and
so attempting to read its Value generates an error. The non-existent value
is not a Null, so Nz() cannot solve the problem.

You can use IsError() with IIf() to replace the error with zero, or you can
read the RecordCount of the subform's RecordsetClone. This example shows how
to read the value of the subform if there is one, or
Click to expand...
use
zero if there is
not:

=IIf([QTDInputDataDirect subform].[Form].
Click to expand...
[RecordsetClone].
[RecordCount]=0,
0,
Nz([QTDInputDataDirect subform].[Form]! [SumOfSumOfPay], 0) )

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

I have a form that uses 3 subforms for it's data. The main
form has the following text boxes:

[DirectWages]
RecordSource: =nnz(IIf([QTDInputDataDirect subform].Form!
Type="Direct",[QTDInputDataDirect subform].Form!
SumOfSumOfPay,0))

[DirectHours]
RecordSource: =nnz(IIf([QTDInputDataDirect subform].Form!
Type="Direct",[QTDInputDataDirect subform].Form!
SumOfHours,0))

[IndirectWages]
RecordSource: =nnz(IIf([QTDInputDataIndirect subform].Form!
Type="Indirect",[QTDInputDataIndirect subform].Form!
SumOfSumOfPay,0))

[IndirectHours]
RecordSource: =nnz(IIf([QTDInputDataIndirect subform].Form!
Type="Indirect",[QTDInputDataIndirect subform].Form!
SumOfHours,0))

This works great as long as each emplyee has data for both
[DirectWages] and [IndirectWages]. If an employee
Click to expand...
only
has
1 type of wage, then the other textbox displays #Error.
This causes a problem with another text box that I
Click to expand...
use
to
total both wages:
[TotalWages]
RecordSource: =nnz([DirectWages]+[IndirectWages])

The subforms get their data from a seperate query:
QTDInputDataDirect
QTDInputDataIndirect

I would like the text boxes to display a zero when an
employee has not earned a type of wage.

The subforms are linked by the EmployeeID to the master
form.
Thanks!


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top