UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Text Box Showing Calculated Total Changes When Row Of New Record Changes, Access 2013    
 
   
saby
post Jan 23 2020, 08:31 AM
Post#1



Posts: 16
Joined: 24-October 19



Hi,

I have created a main form with two sub-forms which is linked to their respective tables. one of the sub-forms contain the following
Sr.No. Element Description Width Height Depth Qty UnitofMeasurement Total

The "Element Description" and "UnitofMeasurement" are combo boxes. The UnitofMeasurement has inch and feet as values with respective id in another table. In all the other fields, the user has to put in the information. Once the user selects the UnitofMeasurement, the textbox has to display calculated total (depending upon the UnitofMeasurement value). I have put in a code in After Update property of UnitofMeasurement and the required total is displayed in the textbox. But once I enter other records in the second row and clicks on UnitofMeasurement, the total for the current record is displayed not only on the second row, but also on the first row.

The said total is just for display purpose and not saved in the table, and hence I want the total display to remain with the actual value without changing.

Looking forward for a response.

- Saby
Go to the top of the page
 
GroverParkGeorge
post Jan 23 2020, 09:32 AM
Post#2


UA Admin
Posts: 36,754
Joined: 20-June 02
From: Newcastle, WA


I think you are seeing the impact of using a Continuous View for your subform. Although you can SEE multiple records displayed, only one of them has focus at a time. And that one record is the one which drives all of the values, including the calculated ones you are seeing.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
saby
post Jan 23 2020, 11:32 PM
Post#3



Posts: 16
Joined: 24-October 19



Thank you George for your kind reply. I am basically new to Access and this is my first project. I am scratching my head in search of an alternative path so that the calculated values are displayed correctly without changing. Would request your input in this regard.
Go to the top of the page
 
saby
post Jan 24 2020, 04:36 AM
Post#4



Posts: 16
Joined: 24-October 19



One of the options I've found is to bound the text box to a field in the table (specially created for this purpose!), which is done so that I am not held up. However, I am searching for the solution where the total is not saved in the table and can be displayed without any changes.
Go to the top of the page
 
cheekybuddha
post Jan 24 2020, 05:21 AM
Post#5


UtterAccess Moderator
Posts: 12,263
Joined: 6-December 03
From: Telegraph Hill


>> I have put in a code in After Update property of UnitofMeasurement and the required total is displayed in the textbox <<

Please post that code here.

--------------------


Regards,

David Marten
Go to the top of the page
 
projecttoday
post Jan 24 2020, 05:56 AM
Post#6


UtterAccess VIP
Posts: 11,588
Joined: 10-February 04
From: South Charleston, WV


I think the code is a calculation, David. Yes, Access forms behave that way. (All types, continuous, datasheet, and single-record.) As you've discovered, the problem is solved by adding the field to the table. Which is not really good permanent table design. But much easier than filling up a temporary table and then having to copy the temporary table into the permanent table to get around the design issue. Is it necessary for you to display multiple records? Can you change it to a single record? Or maybe display all the records but the calculation for just the current record, in which case you could just move the calculating fields onto the form header or footer.

George is recovering from surgery.

--------------------
Robert Crouser
Go to the top of the page
 
cheekybuddha
post Jan 24 2020, 06:00 AM
Post#7


UtterAccess Moderator
Posts: 12,263
Joined: 6-December 03
From: Telegraph Hill


>> I think the code is a calculation, David <<

Yes, I was hoping to take its guts to try and suggest a calculated field in a query, which could be used as the RecordSource of the continuous form, instead of the specially created table.

--------------------


Regards,

David Marten
Go to the top of the page
 
projecttoday
post Jan 24 2020, 06:13 AM
Post#8


UtterAccess VIP
Posts: 11,588
Joined: 10-February 04
From: South Charleston, WV


I think he/she wants to enter new numbers into the form.

--------------------
Robert Crouser
Go to the top of the page
 
cheekybuddha
post Jan 24 2020, 06:24 AM
Post#9


UtterAccess Moderator
Posts: 12,263
Joined: 6-December 03
From: Telegraph Hill


I get that, Robert. The purpose is to have the calculated field auto-update on entry in to the other fields.

--------------------


Regards,

David Marten
Go to the top of the page
 
saby
post Jan 25 2020, 12:00 AM
Post#10



Posts: 16
Joined: 24-October 19



Thank you David for your reply. I am attaching herewith the design view and form view of the forms for your perusal. Herebelow, please find the code used in the AfterUpdate Event:

Private Sub cmbUOMselection_AfterUpdate()
If (txtdepth) = 0 Then
If cmbUOMselection = 1 Then 'in inches
Me.txtTotalQty = (Me.txtWidth * Me.txtHeight * Me.txtQty) / 144
Else
Me.txtTotalQty = (Me.txtWidth * Me.txtHeight * Me.txtQty)
End If
Else
If cmbUOMselection = 1 Then 'in inches
Me.txtTotalQty = (Me.txtWidth * Me.txtHeight * Me.txtdepth * Me.txtQty) / 1728
Else
Me.txtTotalQty = (Me.txtWidth * Me.txtHeight * Me.txtdepth * Me.txtQty)
End If
End If
'DoCmd.GoToRecord , , acNewRec
End Sub

Also, you will observe in the form that the Sr.No. does not change while I am in the second row. The following is the code used:

Option Compare Database
Option Explicit

Function SerialNumber(ByVal sourceForm As Form) As Variant
On Error GoTo Catch

SerialNumber = Null

With sourceForm.RecordsetClone
.Bookmark = sourceForm.Bookmark
SerialNumber = .AbsolutePosition + 1
End With

Done:
Exit Function

Catch:
If Err.Number <> 3021 Then MsgBox Err.Number & ":" & Err.Description, vbExclamation, "SerialNumber"
Resume Done
End Function

I have Set in the Control Source property of the textbox =SerialNumber([Form])

The above code is taken from the website.


My prayers are with George and wish him a speedy recovery.
This post has been edited by saby: Jan 25 2020, 12:03 AM
Attached File(s)
Attached File  DesignView_of_Forms__RecceDoneDetails.png ( 81.67K )Number of downloads: 6
Attached File  FormView_of_RecceDoneDetails.png ( 66.58K )Number of downloads: 7
 
Go to the top of the page
 
saby
post Jan 25 2020, 12:43 AM
Post#11



Posts: 16
Joined: 24-October 19



Dear all,

Once this process is complete , the program moves to the next process; i.e. it pulls the data (width, height, depth, qty) from the table through query and asks the user to input rates for each element - the total is then multiplied by taxes (percent to be input by user) to arrive at the grand total. In all these the UOMeasurement is needed for calculation (as you can see from the calculation code I have posted). Moreover, there are two different taxes to be input (another calculation), and hence the sub-total and grand total has to be displayed without changing while on the second or third row. If no alternative is found, then I will have to create sub-total and total fields in the table, which is not a good practice. Request you help in this regard.

Many thanks,
Saby
Go to the top of the page
 
projecttoday
post Jan 25 2020, 04:57 AM
Post#12


UtterAccess VIP
Posts: 11,588
Joined: 10-February 04
From: South Charleston, WV


For clarification, I believe I'd better point out that it was minor surgery.

--------------------
Robert Crouser
Go to the top of the page
 
saby
post Jan 27 2020, 04:45 AM
Post#13



Posts: 16
Joined: 24-October 19



Hi David, Hope you've seen my post where I have put the code and two jpgs showing design view and form view. Is there any way where the total is not saved in the table and can be displayed without any changes on the screen.

Go to the top of the page
 
cheekybuddha
post Jan 27 2020, 06:39 AM
Post#14


UtterAccess Moderator
Posts: 12,263
Joined: 6-December 03
From: Telegraph Hill


Hi Saby,

Apologies, haven't really had a chance to look in on UA over the last few days.

Let's try and sort the original problem before looking at the Serial Number problem.

What is the (original) RowSource of the subform, not your specially crafted table, but just the original table or query?

If it is just a table or query name, then what are the fields within, and which field names map to txtWidth, txtHeight, txtDepth, txtQty and cmbUOMselection

I'm guessing:
txtWidth -> ProjWidth
txtHeight -> ProjHeight
txtDepth -> ProjDepth
txtQty -> Qty (??)
cmbUOMselection -> ??

Are ElementIDFK and HSNSACode fields in the same table/query?

What are the LinkMasterFields/LinkChildFields for the subform?

--------------------


Regards,

David Marten
Go to the top of the page
 
cheekybuddha
post Jan 27 2020, 07:14 AM
Post#15


UtterAccess Moderator
Posts: 12,263
Joined: 6-December 03
From: Telegraph Hill


CODE
SELECT
  ElementIDFK,
  HSNSACode,
  ProjWidth,
  ProjHeight,
  ProjDepth,
  Qty,
  UOMTUnit,
  IIf(IsNumeric([ProjWidth]) And IsNumeric([ProjHeight]) And IsNumeric([Qty]), ([ProjWidth] * [ProjHeight] * [Qty]) * Nz([ProjDepth], 1) / IIf([UOMTUnit] = 1, 144 * IIf(IsNumeric([ProjDepth]), 12, 1), 1), Null) AS TotalQty
FROM YourTable;


Here's how you could try and do it all in one query for the RowSource - obviously, field/table names will have to be adjusted as necessary.

It's a bit unwieldy like that, so alternatively you can create a UDF in VBA, and call it in the query. Something like:
CODE
Function GetTotalQty(w As Variant, h AS Variant, d As Variant, qty As Variant, unit As Integer) As Variant

  Dim vRet AS Variant, inchesDivisor As Integer

  If IsNumeric(w) And IsNumeric(h) And IsNumeric(Qty) Then
    vRet = w * h * qty * Nz(d, 1)
    If unit = 1 Then
      vRet = vRet / (144 * IIf(IsNumeric(d), 12, 1))
    End If
  End If
  GetTotalQty = vRet

End Function


Then your query would look like:
CODE
SELECT
  ElementIDFK,
  HSNSACode,
  ProjWidth,
  ProjHeight,
  ProjDepth,
  Qty,
  UOMTUnit,
  GetTotalQty([ProjWidth], [ProjHeight], [ProjDepth], [Qty], [UOMTUnit]) AS TotalQty
FROM YourTable;



hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th February 2020 - 06:19 AM