#Error msg on form

[Microsoft]

Hi
I have the following
=Nz(DSum("ItemQty * UnitRate","tblItemDetails","EstimateNo ='" &

[txtEstimateNo] & "' AND ItemNo= '" & [txtItemNo] & "'"),0)

I have a form (ItemHeader) with a subform (ItemDetails) - the above formula
displays the total of the ItemDetail records relating to the ItemHeader - no
problem.
however when the form is opened in edit mode it always shows #Error on the
form for the new record because there aren't any ItemDetails entered yet.

Is there a way of surpressing this #Error so that users can't see it or
amending the formula so that 0 appears instead.

Thanks

Alison

 

[dch3]

The problem isn't that there are no ItemDetail records, but rather that
you're using [txtEstimateNo] and [txtItemNo] in a WHERE statement. Depending
on the values in the fields and how the WHERE is crafted, the function will
crap out. (Not just DSum(), but DLookup(), DCount(), etc.)

Are you using the formula on the form or subfrom? Are the controls located

Hi
I have the following
=Nz(DSum("ItemQty * UnitRate","tblItemDetails","EstimateNo ='" &

[txtEstimateNo] & "' AND ItemNo= '" & [txtItemNo] & "'"),0)

I have a form (ItemHeader) with a subform (ItemDetails) - the above formula
displays the total of the ItemDetail records relating to the ItemHeader - no
problem.
however when the form is opened in edit mode it always shows #Error on the
form for the new record because there aren't any ItemDetails entered yet.

Is there a way of surpressing this #Error so that users can't see it or
amending the formula so that 0 appears instead.

Thanks

Alison

 

[Microsoft]

The formulas are on the subform
The control txtEstimateNo is a hidden control on the subform
The value for the txtExtimateNo control is set when the the main form is
opened (in the Form_Current Event of the subform)
The ItemNo is input by the user - The user then opens another form to enter
the components that make up the item.
The field that has #error relates to the sum of component parts.

The problem isn't that there are no ItemDetail records, but rather that
you're using [txtEstimateNo] and [txtItemNo] in a WHERE statement.
Depending
on the values in the fields and how the WHERE is crafted, the function
will
crap out. (Not just DSum(), but DLookup(), DCount(), etc.)

Are you using the formula on the form or subfrom? Are the controls located
on the form? Do you have a default value set for both for when you're
working
with new records?

Hi
I have the following
=Nz(DSum("ItemQty * UnitRate","tblItemDetails","EstimateNo ='" &

[txtEstimateNo] & "' AND ItemNo= '" & [txtItemNo] & "'"),0)

I have a form (ItemHeader) with a subform (ItemDetails) - the above
formula
displays the total of the ItemDetail records relating to the ItemHeader -
no
problem.
however when the form is opened in edit mode it always shows #Error on
the
form for the new record because there aren't any ItemDetails entered yet.

Is there a way of surpressing this #Error so that users can't see it or
amending the formula so that 0 appears instead.

Thanks

Alison

 

[AndrewOfAzotus]

try:

=iif(nz(itemdetails,"")="","",<your expr from below>)

Andrew/

The formulas are on the subform
The control txtEstimateNo is a hidden control on the subform
The value for the txtExtimateNo control is set when the the main form is
opened (in the Form_Current Event of the subform)
The ItemNo is input by the user - The user then opens another form to enter
the components that make up the item.
The field that has #error relates to the sum of component parts.

The problem isn't that there are no ItemDetail records, but rather that
you're using [txtEstimateNo] and [txtItemNo] in a WHERE statement.
Depending
on the values in the fields and how the WHERE is crafted, the function
will
crap out. (Not just DSum(), but DLookup(), DCount(), etc.)

Are you using the formula on the form or subfrom? Are the controls located
on the form? Do you have a default value set for both for when you're
working
with new records?

Hi
I have the following
=Nz(DSum("ItemQty * UnitRate","tblItemDetails","EstimateNo ='" &
[txtEstimateNo] & "' AND ItemNo= '" & [txtItemNo] & "'"),0)

I have a form (ItemHeader) with a subform (ItemDetails) - the above
formula
displays the total of the ItemDetail records relating to the ItemHeader -
no
problem.
however when the form is opened in edit mode it always shows #Error on
the
form for the new record because there aren't any ItemDetails entered yet.

Is there a way of surpressing this #Error so that users can't see it or
amending the formula so that 0 appears instead.

Thanks

Alison

 

[Microsoft]

Thanks but alas this doesn't work

Any other ideas?
Thanks again
Alison

try:

=iif(nz(itemdetails,"")="","",<your expr from below>)

Andrew/

The formulas are on the subform
The control txtEstimateNo is a hidden control on the subform
The value for the txtExtimateNo control is set when the the main form is
opened (in the Form_Current Event of the subform)
The ItemNo is input by the user - The user then opens another form to
enter
the components that make up the item.
The field that has #error relates to the sum of component parts.

The problem isn't that there are no ItemDetail records, but rather that
you're using [txtEstimateNo] and [txtItemNo] in a WHERE statement.
Depending
on the values in the fields and how the WHERE is crafted, the function
will
crap out. (Not just DSum(), but DLookup(), DCount(), etc.)

Are you using the formula on the form or subfrom? Are the controls
located
on the form? Do you have a default value set for both for when you're
working
with new records?

:

Hi
I have the following
=Nz(DSum("ItemQty * UnitRate","tblItemDetails","EstimateNo ='" &
[txtEstimateNo] & "' AND ItemNo= '" & [txtItemNo] & "'"),0)

I have a form (ItemHeader) with a subform (ItemDetails) - the above
formula
displays the total of the ItemDetail records relating to the
ItemHeader -
no
problem.
however when the form is opened in edit mode it always shows #Error on
the
form for the new record because there aren't any ItemDetails entered
yet.

Is there a way of surpressing this #Error so that users can't see it
or
amending the formula so that 0 appears instead.

Thanks

Alison

 

[AndrewOfAzotus]

Sorry, try placing txtEstimateno or txtitemno in the Nz(<...> , 0)

it is, I think, causing an error because the two items in your where clause
are not there.

Andrew/

Thanks but alas this doesn't work

Any other ideas?
Thanks again
Alison

try:

=iif(nz(itemdetails,"")="","",<your expr from below>)

Andrew/

The formulas are on the subform
The control txtEstimateNo is a hidden control on the subform
The value for the txtExtimateNo control is set when the the main form is
opened (in the Form_Current Event of the subform)
The ItemNo is input by the user - The user then opens another form to
enter
the components that make up the item.
The field that has #error relates to the sum of component parts.

The problem isn't that there are no ItemDetail records, but rather that
you're using [txtEstimateNo] and [txtItemNo] in a WHERE statement.
Depending
on the values in the fields and how the WHERE is crafted, the function
will
crap out. (Not just DSum(), but DLookup(), DCount(), etc.)

Are you using the formula on the form or subfrom? Are the controls
located
on the form? Do you have a default value set for both for when you're
working
with new records?

:

Hi
I have the following
=Nz(DSum("ItemQty * UnitRate","tblItemDetails","EstimateNo ='" &
[txtEstimateNo] & "' AND ItemNo= '" & [txtItemNo] & "'"),0)

I have a form (ItemHeader) with a subform (ItemDetails) - the above
formula
displays the total of the ItemDetail records relating to the
ItemHeader -
no
problem.
however when the form is opened in edit mode it always shows #Error on
the
form for the new record because there aren't any ItemDetails entered
yet.

Is there a way of surpressing this #Error so that users can't see it
or
amending the formula so that 0 appears instead.

Thanks

Alison

 

[Microsoft]

I'll give it a go but isn't this doing the same as the Nz(...) that I
already have in the formula?
Alison

Sorry, try placing txtEstimateno or txtitemno in the Nz(<...> , 0)

it is, I think, causing an error because the two items in your where
clause
are not there.

Andrew/

Thanks but alas this doesn't work

Any other ideas?
Thanks again
Alison
message

try:

=iif(nz(itemdetails,"")="","",<your expr from below>)

Andrew/

:

The formulas are on the subform
The control txtEstimateNo is a hidden control on the subform
The value for the txtExtimateNo control is set when the the main form
is
opened (in the Form_Current Event of the subform)
The ItemNo is input by the user - The user then opens another form to
enter
the components that make up the item.
The field that has #error relates to the sum of component parts.

The problem isn't that there are no ItemDetail records, but rather
that
you're using [txtEstimateNo] and [txtItemNo] in a WHERE statement.
Depending
on the values in the fields and how the WHERE is crafted, the
function
will
crap out. (Not just DSum(), but DLookup(), DCount(), etc.)

Are you using the formula on the form or subfrom? Are the controls
located
on the form? Do you have a default value set for both for when
you're
working
with new records?

:

Hi
I have the following
=Nz(DSum("ItemQty * UnitRate","tblItemDetails","EstimateNo ='" &
[txtEstimateNo] & "' AND ItemNo= '" & [txtItemNo] & "'"),0)

I have a form (ItemHeader) with a subform (ItemDetails) - the
above
formula
displays the total of the ItemDetail records relating to the
ItemHeader -
no
problem.
however when the form is opened in edit mode it always shows #Error
on
the
form for the new record because there aren't any ItemDetails
entered
yet.

Is there a way of surpressing this #Error so that users can't see
it
or
amending the formula so that 0 appears instead.

Thanks

Alison

 

[AndrewOfAzotus]

What you (we or I) are trying to do is find out which field is causing the
error, check if the field causing the error will cause an error then display
"" when it will cause an error.

It would be easier (for me) if I knew what data was in what fields.

Andrew/

I'll give it a go but isn't this doing the same as the Nz(...) that I
already have in the formula?
Alison

Sorry, try placing txtEstimateno or txtitemno in the Nz(<...> , 0)

it is, I think, causing an error because the two items in your where
clause
are not there.

Andrew/

Thanks but alas this doesn't work

Any other ideas?
Thanks again
Alison
message
try:

=iif(nz(itemdetails,"")="","",<your expr from below>)

Andrew/

:

The formulas are on the subform
The control txtEstimateNo is a hidden control on the subform
The value for the txtExtimateNo control is set when the the main form
is
opened (in the Form_Current Event of the subform)
The ItemNo is input by the user - The user then opens another form to
enter
the components that make up the item.
The field that has #error relates to the sum of component parts.

The problem isn't that there are no ItemDetail records, but rather
that
you're using [txtEstimateNo] and [txtItemNo] in a WHERE statement.
Depending
on the values in the fields and how the WHERE is crafted, the
function
will
crap out. (Not just DSum(), but DLookup(), DCount(), etc.)

Are you using the formula on the form or subfrom? Are the controls
located
on the form? Do you have a default value set for both for when
you're
working
with new records?

:

Hi
I have the following
=Nz(DSum("ItemQty * UnitRate","tblItemDetails","EstimateNo ='" &
[txtEstimateNo] & "' AND ItemNo= '" & [txtItemNo] & "'"),0)

I have a form (ItemHeader) with a subform (ItemDetails) - the
above
formula
displays the total of the ItemDetail records relating to the
ItemHeader -
no
problem.
however when the form is opened in edit mode it always shows #Error
on
the
form for the new record because there aren't any ItemDetails
entered
yet.

Is there a way of surpressing this #Error so that users can't see
it
or
amending the formula so that 0 appears instead.

Thanks

Alison

 

[Microsoft]

The error is definitely caused because there is no data in the table
tblItemDetails that matches the EstimateNo and ItemNo of the item I am
entering. Once records are placed in this table, zero or the sum of the
records appears as required.

So it is how to suppress the #Error at the beginning of a new record.

A new record consists of:

Step 1) entering an Item No and Qty on frmEstimateItems (this is where
the DSum formulae is and this sums records in tblItemDetails) - once this is
done #Error appears in the DSum fields.

Step 2) entering item details on frmItemDetails (this is bound to the
table tblItemDetails - once this part is done the DSum formulae works
perfectly

how can I suppress the #Error when only step 1 has been done?

Hope this explains it better....?

Thanks for you help

Alison

What you (we or I) are trying to do is find out which field is causing the
error, check if the field causing the error will cause an error then
display
"" when it will cause an error.

It would be easier (for me) if I knew what data was in what fields.

Andrew/

I'll give it a go but isn't this doing the same as the Nz(...) that I
already have in the formula?
Alison
message

Sorry, try placing txtEstimateno or txtitemno in the Nz(<...> , 0)

it is, I think, causing an error because the two items in your where
clause
are not there.

Andrew/

:

Thanks but alas this doesn't work

Any other ideas?
Thanks again
Alison
message
try:

=iif(nz(itemdetails,"")="","",<your expr from below>)

Andrew/

:

The formulas are on the subform
The control txtEstimateNo is a hidden control on the subform
The value for the txtExtimateNo control is set when the the main
form
is
opened (in the Form_Current Event of the subform)
The ItemNo is input by the user - The user then opens another form
to
enter
the components that make up the item.
The field that has #error relates to the sum of component parts.

The problem isn't that there are no ItemDetail records, but
rather
that
you're using [txtEstimateNo] and [txtItemNo] in a WHERE
statement.
Depending
on the values in the fields and how the WHERE is crafted, the
function
will
crap out. (Not just DSum(), but DLookup(), DCount(), etc.)

Are you using the formula on the form or subfrom? Are the
controls
located
on the form? Do you have a default value set for both for when
you're
working
with new records?

:

Hi
I have the following
=Nz(DSum("ItemQty * UnitRate","tblItemDetails","EstimateNo ='" &
[txtEstimateNo] & "' AND ItemNo= '" & [txtItemNo] & "'"),0)

I have a form (ItemHeader) with a subform (ItemDetails) - the
above
formula
displays the total of the ItemDetail records relating to the
ItemHeader -
no
problem.
however when the form is opened in edit mode it always shows
#Error
on
the
form for the new record because there aren't any ItemDetails
entered
yet.

Is there a way of surpressing this #Error so that users can't
see
it
or
amending the formula so that 0 appears instead.

Thanks

Alison