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

[Emma35]

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]

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]

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]

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]

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

 

[Gasman]

Use Dsum() with the relevant criteria.

DSum Function - Microsoft Support

support.microsoft.com

 

[GaP42]

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.

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]

Use Dsum() with the relevant criteria.

DSum Function - Microsoft Support

support.microsoft.com

My VBA is not good Gasman. I tried the code below but it just turns red as soon as i type it

DSum ( Weight, tbl_MainRecords)

 

[Emma35]

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.

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]

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?

 

[MajP]

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]

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]

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]

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

 

[KitaYama]

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.

 

[Emma35]

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]

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]

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 ?

 

[MajP]

=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]

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 ?