empty recordset in sub form

[Guest]

I have a form, called frmOrders, for entering data for orders from a
supplier. frmOrders has 2 subforms, one for supplies and one for merchandise
for resale, (subfSupplies & subfMerch). frmOrders is based on a table
tblOrders. subfSupplies & subfMerch are based on tables tblOrderSupplies &
tblOrderMerch respectively. The tables are linked in a one to many
relationship based on Order_No – one side is tblOrders, many side for
tblOrderSupplies & tblOrderMerch.

I use calculated controls in the footer of each subform e.g. for subfSupplies:
=IIf(IsNull(Sum([Cost_Supply]*[Qty_Supply])),0,Sum([Qty_Supply]*[Cost_Supply]))
is the data source for the control SumTotalSupplies in the sub form to total
the supplies. I use the IsNull function in case the order is only for
Merchandise and no supplies were purchased.

To get totals for the main form, a control on the main form (Tot_Supplies)
has the data source: =[subfSupplies].Form!SumTotalSupplies.
All works fine when I open the main form in Add mode from a macro for
entering a new order.

I then tried creating a form, frmViewOrders, which opens in “Read Only†mode
from a macro to be able to review order information. The problem is that if
an order is only for merchandise, then the sub form for supplies is empty (&
vice versa) and I get #error in the main form control. I tried using IFF
(IsNull(…)) in the main form control & it doesn’t work. I then tried adding
code to the “On Current†event of the main form similar to:
Dim recClone As Recordset
Set recClone = “SOMETHINGâ€.RecordsetClone
If recClone.RecordCount = 0 Then
Me.Tot_Supplies = 0
End If
where I tried everything I could think of for “SOMETHINGâ€. Everything I
tried either resulted in a run time error or the same #error in the main form
control.
Maybe a query is the answer???

Any help would be greatly appreciated.

Thanks, Garry Gross

 

[Dirk Goldgar]

I have a form, called frmOrders, for entering data for orders from a
supplier. frmOrders has 2 subforms, one for supplies and one for
merchandise for resale, (subfSupplies & subfMerch). frmOrders is
based on a table tblOrders. subfSupplies & subfMerch are based on
tables tblOrderSupplies & tblOrderMerch respectively. The tables are
linked in a one to many relationship based on Order_No - one side is
tblOrders, many side for tblOrderSupplies & tblOrderMerch.

I use calculated controls in the footer of each subform e.g. for
subfSupplies:
=IIf(IsNull(Sum([Cost_Supply]*[Qty_Supply])),0,Sum([Qty_Supply]*[Cost_Su
pply]))
is the data source for the control SumTotalSupplies in the sub form
to total the supplies. I use the IsNull function in case the order
is only for Merchandise and no supplies were purchased.

To get totals for the main form, a control on the main form
(Tot_Supplies) has the data source:
=[subfSupplies].Form!SumTotalSupplies.
All works fine when I open the main form in Add mode from a macro for
entering a new order.

I then tried creating a form, frmViewOrders, which opens in "Read
Only" mode from a macro to be able to review order information. The
problem is that if an order is only for merchandise, then the sub
form for supplies is empty (& vice versa) and I get #error in the
main form control. I tried using IFF (IsNull(.)) in the main form
control & it doesn't work. I then tried adding code to the "On
Current" event of the main form similar to:
Dim recClone As Recordset
Set recClone = "SOMETHING".RecordsetClone
If recClone.RecordCount = 0 Then
Me.Tot_Supplies = 0
End If
where I tried everything I could think of for "SOMETHING".
Everything I tried either resulted in a run time error or the same
#error in the main form control.
Maybe a query is the answer???

Keri Hardwick has provided a good solution for this problem, along with
an explanation, at:

http://www.mvps.org/access/forms/frm0022.htm
Forms: #Error when the Subform has no records

 

[Guest]

Dirk,
Sorry I didn't reply sooner, but thanks, suggested solution works fine!
Garry

I have a form, called frmOrders, for entering data for orders from a
supplier. frmOrders has 2 subforms, one for supplies and one for
merchandise for resale, (subfSupplies & subfMerch). frmOrders is
based on a table tblOrders. subfSupplies & subfMerch are based on
tables tblOrderSupplies & tblOrderMerch respectively. The tables are
linked in a one to many relationship based on Order_No - one side is
tblOrders, many side for tblOrderSupplies & tblOrderMerch.

I use calculated controls in the footer of each subform e.g. for
subfSupplies:
=IIf(IsNull(Sum([Cost_Supply]*[Qty_Supply])),0,Sum([Qty_Supply]*[Cost_Su
pply]))
is the data source for the control SumTotalSupplies in the sub form
to total the supplies. I use the IsNull function in case the order
is only for Merchandise and no supplies were purchased.

To get totals for the main form, a control on the main form
(Tot_Supplies) has the data source:
=[subfSupplies].Form!SumTotalSupplies.
All works fine when I open the main form in Add mode from a macro for
entering a new order.

I then tried creating a form, frmViewOrders, which opens in "Read
Only" mode from a macro to be able to review order information. The
problem is that if an order is only for merchandise, then the sub
form for supplies is empty (& vice versa) and I get #error in the
main form control. I tried using IFF (IsNull(.)) in the main form
control & it doesn't work. I then tried adding code to the "On
Current" event of the main form similar to:
Dim recClone As Recordset
Set recClone = "SOMETHING".RecordsetClone
If recClone.RecordCount = 0 Then
Me.Tot_Supplies = 0
End If
where I tried everything I could think of for "SOMETHING".
Everything I tried either resulted in a run time error or the same
#error in the main form control.
Maybe a query is the answer???

Keri Hardwick has provided a good solution for this problem, along with
an explanation, at:

http://www.mvps.org/access/forms/frm0022.htm
Forms: #Error when the Subform has no records

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Guest]

Dick this was great!!! I was struggling with this problem for hours

Thanks

Bashir

I have a form, called frmOrders, for entering data for orders from a
supplier. frmOrders has 2 subforms, one for supplies and one for
merchandise for resale, (subfSupplies & subfMerch). frmOrders is
based on a table tblOrders. subfSupplies & subfMerch are based on
tables tblOrderSupplies & tblOrderMerch respectively. The tables are
linked in a one to many relationship based on Order_No - one side is
tblOrders, many side for tblOrderSupplies & tblOrderMerch.

I use calculated controls in the footer of each subform e.g. for
subfSupplies:
=IIf(IsNull(Sum([Cost_Supply]*[Qty_Supply])),0,Sum([Qty_Supply]*[Cost_Su
pply]))
is the data source for the control SumTotalSupplies in the sub form
to total the supplies. I use the IsNull function in case the order
is only for Merchandise and no supplies were purchased.

To get totals for the main form, a control on the main form
(Tot_Supplies) has the data source:
=[subfSupplies].Form!SumTotalSupplies.
All works fine when I open the main form in Add mode from a macro for
entering a new order.

I then tried creating a form, frmViewOrders, which opens in "Read
Only" mode from a macro to be able to review order information. The
problem is that if an order is only for merchandise, then the sub
form for supplies is empty (& vice versa) and I get #error in the
main form control. I tried using IFF (IsNull(.)) in the main form
control & it doesn't work. I then tried adding code to the "On
Current" event of the main form similar to:
Dim recClone As Recordset
Set recClone = "SOMETHING".RecordsetClone
If recClone.RecordCount = 0 Then
Me.Tot_Supplies = 0
End If
where I tried everything I could think of for "SOMETHING".
Everything I tried either resulted in a run time error or the same
#error in the main form control.
Maybe a query is the answer???

Keri Hardwick has provided a good solution for this problem, along with
an explanation, at:

http://www.mvps.org/access/forms/frm0022.htm
Forms: #Error when the Subform has no records

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)