Solved Txt box on Form to add data (1 Viewer)

Emma35

Registered User.
Local time
Today, 14:21
Joined
Sep 18, 2012
Messages
467
Hi Everyone,
I have a data entry form where one of the fields where info is entered is called Weight. I need to have a text box on the form which adds up the weights as they are entered into the table (it's just a visual aid for the User). However, i also need a command button beside the text box which will clear the current total so the text box can start counting again from zero when required. I can get the text box to count up the weights entered by simply using =Sum([Weight]) in the control source of the text box but when i press the 'Clear' button to reset it i get an error saying "Can't assign a value to this object".
The code i'm using for the 'Clear' button is in the OnClick event Me.Weight = ""

Anyone any idea how to make this work ?

Thanks a lot,
Em
 

GaP42

Active member
Local time
Tomorrow, 07:21
Joined
Apr 27, 2020
Messages
359
The total weight as Sum(Weight]) is a calculated value summing the weights entered for the records in question. The Clear button would need to delete (set to 0) the weight entered for the records that you are summing. This could be done a an update SQL statement of the on click event of the Cear button, or are you initiating a new set of records with weights?
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:21
Joined
Sep 21, 2011
Messages
14,472
You could use Dsum() in the Current even of the form.
The you could set it to zero with your button. Probbaly no need fo a clear button then though?

You cannot modify a control if it has an expression as a control source.
 

Emma35

Registered User.
Local time
Today, 14:21
Joined
Sep 18, 2012
Messages
467
The total weight as Sum(Weight]) is a calculated value summing the weights entered for the records in question. The Clear button would need to delete (set to 0) the weight entered for the records that you are summing. This could be done a an update SQL statement of the on click event of the Cear button, or are you initiating a new set of records with weights?
Hi....no i'm not initiating a new set of records i simply want to reset the counter. What code would i need to change to in the OnClick event ?
 

Emma35

Registered User.
Local time
Today, 14:21
Joined
Sep 18, 2012
Messages
467
You could use Dsum() in the Current even of the form.
The you could set it to zero with your button. Probbaly no need fo a clear button then though?

You cannot modify a control if it has an expression as a control source.
Ok i didn't know that gasman. How do i use the DSum() event ?......my VBA is atrocious. Thanks
 

GaP42

Active member
Local time
Tomorrow, 07:21
Joined
Apr 27, 2020
Messages
359
Emma - your form has a recordsource, to which you may be applying a filter to display the required records. The CLEAR button, as proposed would perform an update query against the records in the form, so that the Weight in each of those records is set to 0. Be sure - each of those records in the set of selected records will be changed so weight is 0. Is that what you want to do? The calculation of sum(weight) will then display 0.

Code:
Dim strSQL as string
Dim StrWhere as string

StrWhere = "Filter criteria used/set in your form"  'eg "[productID] = " & Forms!Myform!txtProductID & " AND [AnotherTXTField] = '" &  Forms!myForm!txtAnotherTXTField & "' )

strSQL = "Update YourtblName " & _   ' the form record source
" SET Weight = 0 " & _
" WHERE " & strWhere

CurrentDb.Execute strSQL, dbFailOnError
 

Emma35

Registered User.
Local time
Today, 14:21
Joined
Sep 18, 2012
Messages
467
Emma - your form has a recordsource, to which you may be applying a filter to display the required records. The CLEAR button, as proposed would perform an update query against the records in the form, so that the Weight in each of those records is set to 0. Be sure - each of those records in the set of selected records will be changed so weight is 0. Is that what you want to do? The calculation of sum(weight) will then display 0.

Code:
Dim strSQL as string
Dim StrWhere as string

StrWhere = "Filter criteria used/set in your form"  'eg "[productID] = " & Forms!Myform!txtProductID & " AND [AnotherTXTField] = '" &  Forms!myForm!txtAnotherTXTField & "' )

strSQL = "Update YourtblName " & _   ' the form record source
" SET Weight = 0 " & _
" WHERE " & strWhere

CurrentDb.Execute strSQL, dbFailOnError
GaP42 i definitely don't want to reset the records in the table to zero. I just need a counter which can be reset to zero without affecting the underlying table
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:21
Joined
Sep 21, 2011
Messages
14,472
Look at the link please and the syntax. :(
Also what you are trying to get there is the sum of all the records in that table?
Surely there would be some criteria like the order number?
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:21
Joined
May 21, 2018
Messages
8,609
When using a D functon all the arguments are strings. So you have to enclosed them.
curX = DSum("[Freight]", "Orders", _
"[ShipCountryRegion] = 'UK'")
See how the name of the field, table, and the criteria are enclosed in parentheses.
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:21
Joined
Sep 21, 2011
Messages
14,472
GaP42 i definitely don't want to reset the records in the table to zero. I just need a counter which can be reset to zero without affecting the underlying table
Perhaps explain a little why the counter needs to be set to zero?
Explain your process for this form please.
 

GaP42

Active member
Local time
Tomorrow, 07:21
Joined
Apr 27, 2020
Messages
359
A control which presents a calculated value, will carry out the calculation as specified. It cannot be reset to 0 without overwriting the method of calculation. Why do you need to reset to 0 if the calculation is based on a calculation /algorithm which is accurate? To change the value - alter the constituent weights to be summed. Is there more to this? As Gasman says - explain.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 22:21
Joined
Feb 19, 2013
Messages
16,683
not fully tested but have a hidden textbox to store the 'latest' PK and then sum all weights where the PK is greater than the 'latest' PK- see this example
 

Attachments

  • resetcounter.accdb
    480 KB · Views: 33

KitaYama

Well-known member
Local time
Tomorrow, 06:21
Joined
Jan 6, 2022
Messages
1,582
Is it what you need.
Start typing the weight in text box.
Click reset to reset.
Continue to add weight.

Edit : ooops, a little late. Seems @CJ_London suggested a method. But it seems this is a little bit different.
 

Attachments

  • Database4.accdb
    576 KB · Views: 37

Emma35

Registered User.
Local time
Today, 14:21
Joined
Sep 18, 2012
Messages
467
Look at the link please and the syntax. :(
Also what you are trying to get thesre is the sum of all the records in that table?
Surely there would be some criteria like the order number?
I don't need to sum all the records Gasman. The records are entered into the database using a scanner. The reason for this counter is that the operators need to record exactly how much raw material was used to fill a particular tank. This is only necessary for one tank and the rest of the time the counter is not even used. I hope this provides a little context
 

Emma35

Registered User.
Local time
Today, 14:21
Joined
Sep 18, 2012
Messages
467
When using a D functon all the arguments are strings. So you have to enclosed them.

See how the name of the field, table, and the criteria are enclosed in parentheses.
Thanks MajP. I tried the below but still no luck

=DSum("[Weight]","[tbl_MainRecords]",)
 

Emma35

Registered User.
Local time
Today, 14:21
Joined
Sep 18, 2012
Messages
467
not fully tested but have a hidden textbox to store the 'latest' PK and then sum all weights where the PK is greater than the 'latest' PK- see this example
CJ the boxes are just blank when i try adding new records

Edit : Sorry CJ i had to unblock the file before your code worked. I was wondering if there's a way of doing it without the PK ?. The counter can keep on counting away as long as it restarts when reset to zero ?
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 17:21
Joined
May 21, 2018
Messages
8,609
Code:
=DSum("[Weight]","[tbl_MainRecords]",)
If you really have a comma at the end, that may be a problem when using a function. If no criteria remove the comma.
 

Emma35

Registered User.
Local time
Today, 14:21
Joined
Sep 18, 2012
Messages
467
Is it what you need.
Start typing the weight in text box.
Click reset to reset.
Continue to add weight.

Edit : ooops, a little late. Seems @CJ_London suggested a method. But it seems this is a little bit different.
Thanks KitaYama....there's an error popping up on the AfterUpdate event about communicating with the OLE server ?
 

Users who are viewing this thread

Top Bottom