Total of Subform Problem

[Guest]

I have an unbound text field named txttotal in the footer of my form, which
totals the Line totals of a subform. Below is the expression that I used for
After Update.


Private Sub Form_AfterUpdate()
If Nz(Me.PurchaseOrderID, "") = "" Then
Me.txtTotal = 0
Else
Me.txtTotal = DSum("UnitPrice * Quantity",
"tblPurchaseOrderDetails", "PurchaseOrderID=" & Me.PurchaseOrderID)
End If
End Sub

When I go into the form it is showing my totals in this field correctly.
But, when I go to add a new record, it comes up with this error:

CompileError:
Method or data member not found

Can someone give me some direction as to where I would begin to look to
solve this problem.

Thanks.

 

[Allen Browne]

Sharon, you could get Access to show the total without any code, if you set
the ControlSource of the footer text box to:
=Sum([UnitPrice] * [Quantity])

The code approach should work too if PurchaseOrderId is a Number type field.
If it's a Text field, you need extra quotes in the 3rd argument, i.e.:
"PurchaseOrderID=""" & Me.PurchaseOrderID & """"
You might be able to help Access out by adding square brackets around the
field names in the first argument too:
"[UnitPrice] * [Quantity]"

If you do use code, you need to force it to recalc the total after a
deletion as well.

 

[Guest]

I don't understand why I keep getting the message: CompileError: Method or
data member not found in the code "Me.txtTotal"? Am I not using the correct
code here? Is it because I used a text box? (My Access teacher helped me
with this and now I don't know exactly what he did!) I realize that the
total is actually coming from the sum of the LineTotals of the subform, so is
Me. not the right code? I believe he used the code versus the control source
calculation because I could add items to the subform and it would recalculate
after update of the subform. I have spent way to many hours on this one
field! It is driving me crazy!!! I have gone in circles and now I have
completely confused myself. Am I putting the code in the right place, maybe
instead of referring to this text box it is supposed to be in the subform?
Help!!!
--
S

Sharon, you could get Access to show the total without any code, if you set
the ControlSource of the footer text box to:
=Sum([UnitPrice] * [Quantity])

The code approach should work too if PurchaseOrderId is a Number type field.
If it's a Text field, you need extra quotes in the 3rd argument, i.e.:
"PurchaseOrderID=""" & Me.PurchaseOrderID & """"
You might be able to help Access out by adding square brackets around the
field names in the first argument too:
"[UnitPrice] * [Quantity]"

If you do use code, you need to force it to recalc the total after a
deletion as well.

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

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

I have an unbound text field named txttotal in the footer of my form, which
totals the Line totals of a subform. Below is the expression that I used
for
After Update.


Private Sub Form_AfterUpdate()
If Nz(Me.PurchaseOrderID, "") = "" Then
Me.txtTotal = 0
Else
Me.txtTotal = DSum("UnitPrice * Quantity",
"tblPurchaseOrderDetails", "PurchaseOrderID=" & Me.PurchaseOrderID)
End If
End Sub

When I go into the form it is showing my totals in this field correctly.
But, when I go to add a new record, it comes up with this error:

CompileError:
Method or data member not found

Can someone give me some direction as to where I would begin to look to
solve this problem.

Thanks.

 

[Allen Browne]

Okay, there is a case where Access gives this message. It happens when
txtTotal is in the Recordset of the form, but is not the name of a control
on the form. And it doesn't always happen: for example, it may happen if the
form is based on a query, and txtTotal was added to the query after the form
was created.

The workaround is to use Me!txtTotal for this case. This avoids the compiler
checking it, and it works at runtime, so you have worked around the problem.

If that does not describe your case, you may have a corruption, and Name
AutoCorrect may be part of the problem. More info:
http://allenbrowne.com/bug-03.html


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

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

I don't understand why I keep getting the message: CompileError: Method or
data member not found in the code "Me.txtTotal"? Am I not using the
correct
code here? Is it because I used a text box? (My Access teacher helped me
with this and now I don't know exactly what he did!) I realize that the
total is actually coming from the sum of the LineTotals of the subform, so
is
Me. not the right code? I believe he used the code versus the control
source
calculation because I could add items to the subform and it would
recalculate
after update of the subform. I have spent way to many hours on this one
field! It is driving me crazy!!! I have gone in circles and now I have
completely confused myself. Am I putting the code in the right place,
maybe
instead of referring to this text box it is supposed to be in the subform?
Help!!!
--
S

Sharon, you could get Access to show the total without any code, if you
set
the ControlSource of the footer text box to:
=Sum([UnitPrice] * [Quantity])

The code approach should work too if PurchaseOrderId is a Number type
field.
If it's a Text field, you need extra quotes in the 3rd argument, i.e.:
"PurchaseOrderID=""" & Me.PurchaseOrderID & """"
You might be able to help Access out by adding square brackets around the
field names in the first argument too:
"[UnitPrice] * [Quantity]"

If you do use code, you need to force it to recalc the total after a
deletion as well.

I have an unbound text field named txttotal in the footer of my form,
which
totals the Line totals of a subform. Below is the expression that I
used
for
After Update.


Private Sub Form_AfterUpdate()
If Nz(Me.PurchaseOrderID, "") = "" Then
Me.txtTotal = 0
Else
Me.txtTotal = DSum("UnitPrice * Quantity",
"tblPurchaseOrderDetails", "PurchaseOrderID=" & Me.PurchaseOrderID)
End If
End Sub

When I go into the form it is showing my totals in this field
correctly.
But, when I go to add a new record, it comes up with this error:

CompileError:
Method or data member not found

Can someone give me some direction as to where I would begin to look to
solve this problem.

Thanks.

 

[Guest]

Thank you so much!
--
S

Okay, there is a case where Access gives this message. It happens when
txtTotal is in the Recordset of the form, but is not the name of a control
on the form. And it doesn't always happen: for example, it may happen if the
form is based on a query, and txtTotal was added to the query after the form
was created.

The workaround is to use Me!txtTotal for this case. This avoids the compiler
checking it, and it works at runtime, so you have worked around the problem.

If that does not describe your case, you may have a corruption, and Name
AutoCorrect may be part of the problem. More info:
http://allenbrowne.com/bug-03.html


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

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

I don't understand why I keep getting the message: CompileError: Method or
data member not found in the code "Me.txtTotal"? Am I not using the
correct
code here? Is it because I used a text box? (My Access teacher helped me
with this and now I don't know exactly what he did!) I realize that the
total is actually coming from the sum of the LineTotals of the subform, so
is
Me. not the right code? I believe he used the code versus the control
source
calculation because I could add items to the subform and it would
recalculate
after update of the subform. I have spent way to many hours on this one
field! It is driving me crazy!!! I have gone in circles and now I have
completely confused myself. Am I putting the code in the right place,
maybe
instead of referring to this text box it is supposed to be in the subform?
Help!!!
--
S

Sharon, you could get Access to show the total without any code, if you
set
the ControlSource of the footer text box to:
=Sum([UnitPrice] * [Quantity])

The code approach should work too if PurchaseOrderId is a Number type
field.
If it's a Text field, you need extra quotes in the 3rd argument, i.e.:
"PurchaseOrderID=""" & Me.PurchaseOrderID & """"
You might be able to help Access out by adding square brackets around the
field names in the first argument too:
"[UnitPrice] * [Quantity]"

If you do use code, you need to force it to recalc the total after a
deletion as well.


I have an unbound text field named txttotal in the footer of my form,
which
totals the Line totals of a subform. Below is the expression that I
used
for
After Update.


Private Sub Form_AfterUpdate()
If Nz(Me.PurchaseOrderID, "") = "" Then
Me.txtTotal = 0
Else
Me.txtTotal = DSum("UnitPrice * Quantity",
"tblPurchaseOrderDetails", "PurchaseOrderID=" & Me.PurchaseOrderID)
End If
End Sub

When I go into the form it is showing my totals in this field
correctly.
But, when I go to add a new record, it comes up with this error:

CompileError:
Method or data member not found

Can someone give me some direction as to where I would begin to look to
solve this problem.

Thanks.