Solved Sum the data in a textbox in continous form

T

TMK

New member
Local time
Today, 09:18
Joined
Apr 28, 2025
Messages
25
I have a continous form in that i have a few records, i have added a new textbox to each record, where there is an if statment that gives "1" if yes or "0" is no, now i want to be able to calculate all the number text boxes with 1 in the form footer, but i keep getting an #error.

My guess is the since the if condition is in the control source of the textbox, the textbox with the sum function cant find the text boxes to add since it is not stored in a table, any way i could do it?
 
create a query for your table and add the Calculated column (the 1 and 0).
use the Query as recordsource of your continuous form, then you can sum the Calculated column.
 
TMK said:
I have a continous form in that i have a few records, i have added a new textbox to each record, where there is an if statment that gives "1" if yes or "0" is no, now i want to be able to calculate all the number text boxes with 1 in the form footer, but i keep getting an #error.

My guess is the since the if condition is in the control source of the textbox, the textbox with the sum function cant find the text boxes to add since it is not stored in a table, any way i could do it?
Click to expand...
You could try Sum() the statement from the text box.
=Sum([your statement here])
 
DHookom said:
You could try Sum() the statement from the text box.
=Sum([your statement here])
Click to expand...
i tried that, but you need to hav the data stored in a table for it to happen
 
arnelgp said:
create a query for your table and add the Calculated column (the 1 and 0).
use the Query as recordsource of your continuous form, then you can sum the Calculated column.
Click to expand...
i just tried that too, and it should work, but now, i get an vba error "runtime error '3'", for wht i did in the query i created was, the form record source for the sumform "orderdtl" was already a query, so i amended that itself and added the new fields, but one of the fields involve in bringing a another field from a different query called "stock".

And also when i edit the fordtl query again and remove the stock qry, it works fine again but i need to retrive the stock on hand in order carry out the "if statment"
 
show us your Query SQL string.
 
Private Sub Quantity_AfterUpdate()
Me.Recalc
End Sub

Private Sub Quantity_Click()
Me.Recalc
End Sub

Private Sub Size_AfterUpdate()
Forms![Order_frm]![OrderDtl_frm].Form.ID.Value = Forms![Order_frm]![OrderDtl_frm].Form.Product_ID.Value + "-" + Forms![Order_frm]![OrderDtl_frm].Form.Material.Column(0) + "-" + Forms![Order_frm]![OrderDtl_frm].Form.Size.Column(1)
Forms![Order_frm]![OrderDtl_frm].Form.Recalc
End Sub

Private Sub Material_AfterUpdate()
Forms![Order_frm]![OrderDtl_frm].Form.ID.Value = Forms![Order_frm]![OrderDtl_frm].Form.Product_ID.Value + "-" + Forms![Order_frm]![OrderDtl_frm].Form.Material.Column(0) + "-" + Forms![Order_frm]![OrderDtl_frm].Form.Size.Column(1)
Forms![Order_frm]![OrderDtl_frm].Form.Recalc
End Sub

Private Sub Product_ID_AfterUpdate()
Forms![Order_frm]![OrderDtl_frm].Form.Product_Name.Value = Forms![Order_frm]![OrderDtl_frm].Form.Product_ID.Column(1)
Forms![Order_frm]![OrderDtl_frm].Form.ID.Value = Forms![Order_frm]![OrderDtl_frm].Form.Product_ID.Value + "-" + Forms![Order_frm]![OrderDtl_frm].Form.Material.Column(0) + "-" + Forms![Order_frm]![OrderDtl_frm].Form.Size.Column(1)
Forms![Order_frm]![OrderDtl_frm].Form.Recalc
End Sub

Private Sub Product_Name_AfterUpdate()
Forms![Order_frm]![OrderDtl_frm].Form.Product_ID.Value = Forms![Order_frm]![OrderDtl_frm].Form.Product_Name.Column(1)
Forms![Order_frm]![OrderDtl_frm].Form.ID.Value = Forms![Order_frm]![OrderDtl_frm].Form.Product_ID.Value + "-" + Forms![Order_frm]![OrderDtl_frm].Form.Material.Column(0) + "-" + Forms![Order_frm]![OrderDtl_frm].Form.Size.Column(1)
Forms![Order_frm]![OrderDtl_frm].Form.Recalc
End Sub

Private Sub SetBtn_Click()
Forms![Order_frm]![OrderDtl_frm].Form.Product_Actual_ID.Value = Forms![Order_frm]![OrderDtl_frm].Form.Product_ID.Value + "-" + Forms![Order_frm]![OrderDtl_frm].Form.Material.Column(0) + "-" + Forms![Order_frm]![OrderDtl_frm].Form.Size.Column(1)
Forms![Order_frm]![OrderDtl_frm].Form.Product_Actual_Name.Value = Forms![Order_frm]![OrderDtl_frm].Form.Product_Name.Value + "-" + Forms![Order_frm]![OrderDtl_frm].Form.Material.Column(1) + "-" + Forms![Order_frm]![OrderDtl_frm].Form.Size.Column(1)
Forms![Order_frm]![OrderDtl_frm].Form.Product_Material.Value = Forms![Order_frm]![OrderDtl_frm].Form.Material.Value
Forms![Order_frm]![OrderDtl_frm].Form.Product_Size.Value = Forms![Order_frm]![OrderDtl_frm].Form.Size.Value
Forms![Order_frm]![OrderDtl_frm].Form.Product_UnitPrice.Value = Forms![Order_frm]![OrderDtl_frm].Form.UnitPrice.Value
Forms![Order_frm]![OrderDtl_frm].Form.Recalc
End Sub
 
Option Compare Database

Private Sub Customer_ID_AfterUpdate()
Me.Customer_Name.Value = Me.Customer_ID.Column(1)
End Sub

Private Sub Customer_Name_AfterUpdate()
Me.Customer_ID.Value = Me.Customer_Name.Column(1)
End Sub

Private Sub Detail_Click()
Me.GrandTotal.Value = Me.FinalTotal.Value
Me.Recalc
DoCmd.RunCommand acCmdSaveRecord
End Sub

Private Sub Disco_AfterUpdate()
Me.GrandTotal.Value = Me.FinalTotal.Value
DoCmd.RunCommand acCmdSaveRecord
End Sub


Private Sub Form_Current()

End Sub

Private Sub Form_Load()

End Sub

Private Sub OrderDtl_frm_Enter()
Me.GrandTotal.Value = Me.FinalTotal.Value
Me.Recalc

End Sub

Private Sub OrderDtl_frm_Exit(Cancel As Integer)
Me.GrandTotal.Value = Me.FinalTotal.Value
Me.Recalc
Forms![Order_frm]!Status.RowSource = "Completed;Returned;"
Me.Recalc
ElseIf Me.Status.Value = "Completed" Then
Forms![Order_frm]!Status.RowSource = "Shipped;Returned;"
Me.Recalc
ElseIf Me.Status.Value = "Returned" Then
Forms![Order_frm]!Status.RowSource = "Pending;Processing;Shipped;Completed;Returned"
Me.Recalc
ElseIf Forms![Order_frm]![OrderDtl_frm].Form.Available.Value < Forms![Order_frm]![OrderDtl_frm].Form.MaxCount.Value Then
Forms![Order_frm]!Status.RowSource = "Pending;Processing;"
Me.Recalc
ElseIf Me.Status.Value = "Returned" And Forms![Order_frm]![OrderDtl_frm].Form.Available.Value < Forms![Order_frm]![OrderDtl_frm].Form.MaxCount.Value Then
Forms![Order_frm]!Status.RowSource = "Pending;Processing;"
Me.Recalc
Else
Forms![Order_frm]!Status.RowSource = "Pending;Processing;Shipped;Completed;Returned"
Me.Recalc
End If
Me.Recalc
End Sub

Private Sub Status_Click()

End Sub
 
Does your code compile? I don't see an "If ... Then" line in Private Sub OrderDtl_frm_Exit(Cancel As Integer).

I hate using ElseIf when it is much easier to use and maintain Select Case.
 
TMK said:
Option Compare Database

Private Sub Customer_ID_AfterUpdate()
Me.Customer_Name.Value = Me.Customer_ID.Column(1)
End Sub

Private Sub Customer_Name_AfterUpdate()
Me.Customer_ID.Value = Me.Customer_Name.Column(1)
End Sub

Private Sub Detail_Click()
Me.GrandTotal.Value = Me.FinalTotal.Value
Me.Recalc
DoCmd.RunCommand acCmdSaveRecord
End Sub

Private Sub Disco_AfterUpdate()
Me.GrandTotal.Value = Me.FinalTotal.Value
DoCmd.RunCommand acCmdSaveRecord
End Sub


Private Sub Form_Current()

End Sub

Private Sub Form_Load()

End Sub

Private Sub OrderDtl_frm_Enter()
Me.GrandTotal.Value = Me.FinalTotal.Value
Me.Recalc

End Sub

Private Sub OrderDtl_frm_Exit(Cancel As Integer)
Me.GrandTotal.Value = Me.FinalTotal.Value
Me.Recalc
Forms![Order_frm]!Status.RowSource = "Completed;Returned;"
Me.Recalc
ElseIf Me.Status.Value = "Completed" Then
Forms![Order_frm]!Status.RowSource = "Shipped;Returned;"
Me.Recalc
ElseIf Me.Status.Value = "Returned" Then
Forms![Order_frm]!Status.RowSource = "Pending;Processing;Shipped;Completed;Returned"
Me.Recalc
ElseIf Forms![Order_frm]![OrderDtl_frm].Form.Available.Value < Forms![Order_frm]![OrderDtl_frm].Form.MaxCount.Value Then
Forms![Order_frm]!Status.RowSource = "Pending;Processing;"
Me.Recalc
ElseIf Me.Status.Value = "Returned" And Forms![Order_frm]![OrderDtl_frm].Form.Available.Value < Forms![Order_frm]![OrderDtl_frm].Form.MaxCount.Value Then
Forms![Order_frm]!Status.RowSource = "Pending;Processing;"
Me.Recalc
Else
Forms![Order_frm]!Status.RowSource = "Pending;Processing;Shipped;Completed;Returned"
Me.Recalc
End If
Me.Recalc
End Sub

Private Sub Status_Click()

End Sub
Click to expand...
Start using code tags!!!! PLEASE
 
I solved it!

Since is get the error when i add the query to the query been used by the form, I added an empty field to the table instead, and in the form i did a dlookup to get if the stock is available form the query and get the value for each record automatically and recalc data when i login, and then and a text box to store the value in the table, and then i can get the count of all records and count of no stock records and carry on forward!
 
You must log in or register to reply here.

Similar threads

D
Textbox looses info when fromis closed
Replies
6
Views
285
DHookom
DHookom
C
Subform Footer Textbox wont update
Replies
11
Views
392
Pat Hartman
P
A
Solved In a continuous form : Disable / lock a textbox on a specific record on clicking a button within that record. Any way it can be done ?
Replies
6
Views
332
alan2013
A
T
Solved Store data from text box to control source
Replies
16
Views
394
Gasman
Gasman
Z
Calendar Control
2
Replies
22
Views
740
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom