#Error in form

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!
 
A

Allen Browne

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) )
 
G

Guest

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...
 
A

Allen Browne

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...
 
G

Guest

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

initialize form 5
Edit a table while in form view 2
Form total from subform total 1
combo box value is not shown on the form 2
Main Form Sub Form Has Data Calculation 3
form in a form? 2
Urgent !!! - Values from Subform - #Error if no records in Subform and Only grabs first subform reco 0
Form/Subform “value isn't valid†3

Top