Main form -subform keeping count

Not getting an error. the result goes up and down as uncheck and recheck the
[contactedwork]field. And refresh. However the answer is incorrect. 7
loads-5 checked = 0 Loads left to fill Answer goes up and down 1 count as
check and uncheck -can it see true maybe if not checked???????

thanks for helping

"Al Camp" wrote:

Try...
= [trkloads]-DCount("[contactedwork]","dispatchdetailsquery","dispatchid = [dispatchid]
And [contactedwork] = True")
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"babs" wrote in message
...

I put it in yes-dispatchid is an autonumber and contactedwork a yes/no field
Still getting error# in the textbox
Put in like you said ???

What I put in.
=[trkloads]-DCount("[contactedwork]","dispatchdetailsquery","dispatchid='" &
[dispatchid] & "' And [contactedwork] = True")

"Al Camp" wrote:

If dispatchdetailsquery contains DispatchID (Numeric) and ContactedWork (Boolean)
I don't have time to test, but try this... (all on one line)

=[trkloads]-DCount("[contactedwork]","dispatchdetailsquery","dispatchid = ' " &
[dispatchid] &" ' And [contactedwork] = True")

(I've left spaces between the " ' and ' " quotes... for clarity... remove those spaces)
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"babs" wrote in message
...
the original calc on the main form does not refresh when adding the code(but
no error) you just suggested. don't know if since it is on the mainform and
not the subform if that might be the reason. Does refresh when press F9 but
again would prefer not to have to do that ?? any more ideas of the needed
code. ALSO the DCOUNT would like to modify see below


However. realized not best to count dispatching id - some include truck
contacted and NO work field check off whick means the order is not filled .
Wanted to tie it to the dcount of the field in the subform call [contacted
work] when it is check yes.
Put in this to Handle that???


=[trkloads]-DCount("[contactedwork]","dispatchdetailsquery","dispatchid=forms!frmdisp atchloc!dispatchid"
And "[contactedwork]"="Yes")

don't want to Count where [contactedwork] is null or no

The calculation that i put in above to do that is not coming out with the
right answer. do you see the problem in the calculation??

thanks for your continued patience and help


"Al Camp" wrote:

Babs,
Try just...
Private Sub cbotruck_AfterUpdate()
Refresh
End Sub
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"babs" wrote in message
...
Yes it WAS working fine without adding the code for REfresh - see above. Yes
if hit F9 worked fine. with code below
=[trkloads]-DCount("[dispatchingid]","dispatchdetailsquery","dispatchid=forms!frmdisp atchloc!dispatchid")

However. realized not best to count dispatching id - some include truck
contacted and NO work field check off whick means the order is not filled .
Wanted to tie it to the dcount of the field in the subform call [contacted
work] when it is check yes.
Put in this to Handle that???


=[trkloads]-DCount("[contacted
work]","dispatchdetailsquery","dispatchid=forms!frmdisp atchloc!dispatchid"
And "[contacted work]"="Yes")

don't want to include dispatchid where contacted work is null or no

The calculation that i put in above to do that is not coming out with the
right #. do you see an error in the code

AS to the REFRESH error - from previous reply

Private Sub cbotruck_AfterUpdate()
totLoadsRemaining.Refresh
End Sub

- added code to the after update event of the truck combo box - the exact
error I get is error 424: Object required. Used the Name of the calc. text
box - not sure what need to add here.



"Al Camp" wrote:

Babs,
You have to provide meaningful information...
I am getting an error
What is the error?

You said you were going to stick with the Datasheet subform, so I suggested
that
you
drop the subform totaling and go with an aggregate function on the main form... a
calculated field with something like this.... (use your own object names)

= TotalOrders - DCount("[DispatchingID]", "tblYourTableName", "OrderID =
Forms!frmOvenLoc!OrderID")

Do you have that working yet? (even if you have to hit the F9 key manually
to
recalc)
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions



"babs" wrote in message
...
Al,

below is the code I attached to the after update of the truck combo box. I
am getting an error. totloadsremaining is the NAME of the calcuation text
box. Not sure of the correct syntax.

Thanks,
Barb

Private Sub cbotruck_AfterUpdate()
totLoadsRemaining.Refresh
End Sub

"Al Camp" wrote:

Babs,
Since we don't know the exact names of all your controls... we can only use
example
names like YourTableName or YourFormName. You have to translate your names
into
the
code
template.
Since your calculation in the subform utilizes the DispatcherID, put a
Refresh
on
the
AfterUpdate of that field.
OR...
A Refresh on the AfterUpdate event of some field in your subform records
that
will
always be entered.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


"babs" wrote in message
...
Got it - had wrong table name. Calc. only refreshes when Press f9 would
like
to to do it when move to new record on datasheet(subform)?? What would the
code be and where would I attach it to??

THANKS AGAIN

"Al Camp" wrote:

Babs,
Datasheet view does not have a Header or Footer...
If you have your subform set to FormView, AND set to Single Form, you'll
only
see
one
record at a time. All the records are really there, but you need to
PageDown
in
order
to
see them.

If you use a Form View subform set to "Continuous" View, you will see
all
your
records... and your footer.

If you must stay with a Datasheet subform, then you'll have to use a
DCount
aggregate
function to count the DispatchingIDs related to that specific order, and
subtract
it
from
TotalOrders on your main form.
For example, if The Main form is related to the subform by some field
like
OrderID..
On the main form, a calculated field with...
= TotalOrders - DCount("[DispatchingID]", "tblYourTableName", "OrderID =
Forms!frmOvenLoc!OrderID")

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"babs" wrote in message
...
The calc isn't right- and I can see it on the subform only when I am in
Form
view- really want the subform in Datasheet view so can see all the ovens
assigned to that order. Any way to see it - should I put it on the main
form??? I cut and pasted the code below shows up as Name?.

=Forms!frmOVENLoc!totalorders-Count([dispatchingid]) & "Orders/s
Remaining"


Just total orders is on the Main form and using the Count of product name
on
the subform.

To get order remaining

Any reason why getting Name?- and where best place to put calc to see
suform
in datasheet view on with main form

??

thanks for your help,
barb

"Al Camp" wrote:

Is the Footer Visible property set to Yes?
Are you using Form View or Datasheet view?

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"babs" wrote in message
...
I think I have the formula in correctly but when I change the view of
the
subform to form view the calculation does not show up- can not see the
footer
of the subform???????? Even in main/subform can't see footer of
subform.
See
it in design view though????

Thanks,
Barb

"Al Camp" wrote:

Babs,
If you have a field on your Main form ([TotalOrders-Count])
and the Count of ProductName in the subform must be subtracted from
that...
then your unbound text control in your subform Footer should be...
(use your own control names...)

= [Forms]![frmYourMainFormName]![TotalOrders-Count] -
Count(Product_Name) &
"
Order/s
Remaining"

--
hth
Al Camp
Candia Computer Consulting - Candia NH

Babs,
I'm out of ideas. I ran a test using a value on a form, minus a DCount against a
TrueFalse/YesNo field in a separate table, and it worked fine.

=[MyFormQty]-DCount("[MyTFField]","tblTFTest","CustID = [CustID] and TrueFalse = True")

Try a brand new post. No need to start over... just describe your main form fields and
the subfrom fields, what you expect vs. what your getting for the calculation.
There's probably something amiss here, but I can't figure out what it might be.
Someone else may have some other ideas... this thread is pretty well "buried".
--
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


"babs" wrote in message
...
Not getting an error. the result goes up and down as uncheck and recheck the
[contactedwork]field. And refresh. However the answer is incorrect. 7
loads-5 checked = 0 Loads left to fill Answer goes up and down 1 count as
check and uncheck -can it see true maybe if not checked???????

thanks for helping

"Al Camp" wrote:

Try...
= [trkloads]-DCount("[contactedwork]","dispatchdetailsquery","dispatchid = [dispatchid]
And [contactedwork] = True")
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"babs" wrote in message
...

I put it in yes-dispatchid is an autonumber and contactedwork a yes/no field
Still getting error# in the textbox
Put in like you said ???

What I put in.
=[trkloads]-DCount("[contactedwork]","dispatchdetailsquery","dispatchid='" &
[dispatchid] & "' And [contactedwork] = True")

"Al Camp" wrote:

If dispatchdetailsquery contains DispatchID (Numeric) and ContactedWork (Boolean)
I don't have time to test, but try this... (all on one line)

=[trkloads]-DCount("[contactedwork]","dispatchdetailsquery","dispatchid = ' " &
[dispatchid] &" ' And [contactedwork] = True")

(I've left spaces between the " ' and ' " quotes... for clarity... remove those
spaces)
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"babs" wrote in message
...
the original calc on the main form does not refresh when adding the code(but
no error) you just suggested. don't know if since it is on the mainform and
not the subform if that might be the reason. Does refresh when press F9 but
again would prefer not to have to do that ?? any more ideas of the needed
code. ALSO the DCOUNT would like to modify see below


However. realized not best to count dispatching id - some include truck
contacted and NO work field check off whick means the order is not filled .
Wanted to tie it to the dcount of the field in the subform call [contacted
work] when it is check yes.
Put in this to Handle that???


=[trkloads]-DCount("[contactedwork]","dispatchdetailsquery","dispatchid=forms!frmdisp atchloc!dispatchid"
And "[contactedwork]"="Yes")

don't want to Count where [contactedwork] is null or no

The calculation that i put in above to do that is not coming out with the
right answer. do you see the problem in the calculation??

thanks for your continued patience and help


"Al Camp" wrote:

Babs,
Try just...
Private Sub cbotruck_AfterUpdate()
Refresh
End Sub
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"babs" wrote in message
...
Yes it WAS working fine without adding the code for REfresh - see above. Yes
if hit F9 worked fine. with code below
=[trkloads]-DCount("[dispatchingid]","dispatchdetailsquery","dispatchid=forms!frmdisp atchloc!dispatchid")

However. realized not best to count dispatching id - some include truck
contacted and NO work field check off whick means the order is not filled .
Wanted to tie it to the dcount of the field in the subform call [contacted
work] when it is check yes.
Put in this to Handle that???


=[trkloads]-DCount("[contacted
work]","dispatchdetailsquery","dispatchid=forms!frmdisp atchloc!dispatchid"
And "[contacted work]"="Yes")

don't want to include dispatchid where contacted work is null or no

The calculation that i put in above to do that is not coming out with the
right #. do you see an error in the code

AS to the REFRESH error - from previous reply

Private Sub cbotruck_AfterUpdate()
totLoadsRemaining.Refresh
End Sub

- added code to the after update event of the truck combo box - the exact
error I get is error 424: Object required. Used the Name of the calc. text
box - not sure what need to add here.



"Al Camp" wrote:

Babs,
You have to provide meaningful information...
I am getting an error
What is the error?

You said you were going to stick with the Datasheet subform, so I
suggested
that
you
drop the subform totaling and go with an aggregate function on the main
form... a
calculated field with something like this.... (use your own object names)

= TotalOrders - DCount("[DispatchingID]", "tblYourTableName", "OrderID =
Forms!frmOvenLoc!OrderID")

Do you have that working yet? (even if you have to hit the F9 key
manually
to
recalc)
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions



"babs" wrote in message
...
Al,

below is the code I attached to the after update of the truck combo box. I
am getting an error. totloadsremaining is the NAME of the calcuation text
box. Not sure of the correct syntax.

Thanks,
Barb

Private Sub cbotruck_AfterUpdate()
totLoadsRemaining.Refresh
End Sub

"Al Camp" wrote:

Babs,
Since we don't know the exact names of all your controls... we can only
use
example
names like YourTableName or YourFormName. You have to translate your names
into
the
code
template.
Since your calculation in the subform utilizes the DispatcherID, put a
Refresh
on
the
AfterUpdate of that field.
OR...
A Refresh on the AfterUpdate event of some field in your subform records
that
will
always be entered.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


"babs" wrote in message
...
Got it - had wrong table name. Calc. only refreshes when Press f9 would
like
to to do it when move to new record on datasheet(subform)?? What would
the
code be and where would I attach it to??

THANKS AGAIN

"Al Camp" wrote:

Babs,
Datasheet view does not have a Header or Footer...
If you have your subform set to FormView, AND set to Single Form,
you'll
only
see
one
record at a time. All the records are really there, but you need to
PageDown
in
order
to
see them.

If you use a Form View subform set to "Continuous" View, you will see
all
your
records... and your footer.

If you must stay with a Datasheet subform, then you'll have to use a
DCount
aggregate
function to count the DispatchingIDs related to that specific order, and
subtract
it
from
TotalOrders on your main form.
For example, if The Main form is related to the subform by some field
like
OrderID..
On the main form, a calculated field with...
= TotalOrders - DCount("[DispatchingID]", "tblYourTableName", "OrderID =
Forms!frmOvenLoc!OrderID")

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"babs" wrote in message
...
The calc isn't right- and I can see it on the subform only when I am
in
Form
view- really want the subform in Datasheet view so can see all the
ovens
assigned to that order. Any way to see it - should I put it on the
main
form??? I cut and pasted the code below shows up as Name?.

=Forms!frmOVENLoc!totalorders-Count([dispatchingid]) & "Orders/s
Remaining"


Just total orders is on the Main form and using the Count of product
name
on
the subform.

To get order remaining

Any reason why getting Name?- and where best place to put calc to see
suform
in datasheet view on with main form

??

thanks for your help,
barb

"Al Camp" wrote:

Is the Footer Visible property set to Yes?
Are you using Form View or Datasheet view?

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"babs" wrote in message
...
I think I have the formula in correctly but when I change the view
of
the
subform to form view the calculation does not show up- can not see
the
footer
of the subform???????? Even in main/subform can't see footer of
subform.
See
it in design view though????

Thanks,
Barb

"Al Camp" wrote:

Babs,
If you have a field on your Main form ([TotalOrders-Count])
and the Count of ProductName in the subform must be subtracted
from
that...
then your unbound text control in your subform Footer should be...
(use your own control names...)

= [Forms]![frmYourMainFormName]![TotalOrders-Count] -
Count(Product_Name) &
"
Order/s
Remaining"

--
hth
Al Camp
Candia Computer Consulting - Candia NH

babs wrote:
Say -I have a total number of items listed on the main form for example of 5.

In the subform if I have 5 detailed records. I would like a field in the
details table to say 0 items left to fill. If I only have 4 detailed
records obviously would like it to say 1 item to fill and so on. How can I
do the calc. between main and subform????

Thanks

Babs,

Put a textbox on the mainform called RecNo, which will hold the no of
records in the subform shown as datasheet view.

Dim rscount as Integer 'to hold the recordcount
Dim ToFill as Integer 'to hold the final number to be filled

With Me.Form!subformcontrolname.Form.RecordsetClone
If .BOF And .EOF Then
Me.RecNo = 5 'if no records then 5 to fill

Else
.MoveLast 'go to the last record
rscount = .RecordCount 'fill the variable with the recordcount
ToFill = (5 - rscount) ' do the sums
Me.RecNo = ToFill 'populate the textbox with the final sum
End If
End With

This should work with a textbox on the mainform and subform records
shown as datasheet. I have a similar setup and it works fine.

Hope it helps,
Jeff Cox