My Assistant
Site Message
(Message will auto close in 2 seconds)
  |
 Is It Possible To Move A Variable From Form To Form
|
|
 Mar 18 2012, 12:49 AM
Post
#1
|
|
|
UtterAccess Veteran Posts: 318 From: London UK  |
Hi Guys
1 thought I might try you before I went to work today to see if the following is possible. (form shown below) I have a form with 3 subforms (the 2 that a relative are "selected driver outstanding amounts"(RHS) and "Expense input form"(Bottom) On the "selected driver outstanding amounts" subform I have a unbound control that reads SELECTED DRIVER - TOTAL OUTSTANDING NOW ...£286.60 ------ TOTAL TO TRANSFER TO EXPENSES ...£356.60 This is continually calculated everytime I enter a date in the "driver paid" control via the following code. What would be useful is if somecode could be added somewhere so that the "total to transfer to expenses" amount is transfered to the "expense input form" "Chargeablecost" control automatically (at the moment I enter the fugure manually). CODE Private Sub DriverPaidDate_AfterUpdate() ' Kindly written for me by Larry Larsen (UA) 08/11/2011 ' Loops thru the records that have had the datepaid populated and totals the amounts for money1 field and at 'the same time totals the amounts for money2 field (records that the datepaid field is null. ' It displays the money1 & money2 results in an unbound text box that is created in the form footer. ' Used in conjuction with code below that is situated in the forms OnControl (event Procedure). Dim RST As DAO.Recordset Dim Money1 As Double Dim Money2 As Double Set RST = Me.RecordsetClone If RST.RecordCount = 0 Then MsgBox "There were No Records Returned.", vbInformation, " No Records Returned" End If RST.MoveFirst Do Until RST.EOF If Not IsNull(RST!PAIDOUTDATE) Then Money1 = Money1 + RST!PaidOut Else Money2 = Money2 + RST!PaidOut End If RST.MoveNext Loop Me.Txtresult = "SELECTED DRIVER - TOTAL OUTSTANDING NOW " & "..." & Format(Money2, "Currency") & " ------" & " TOTAL TO TRANSFER TO EXPENSES " & "..." & Format(Money1, "Currency") End Sub Private Sub Form_Current() ' See above code for explanation. Extra bit of code added (error handler) this is needed in this particular 'instance because of the way I select the drivers name for the underlying query. ie the drivers name is ' selected after the form is loaded and as this code is loaded previously it produces the 3021 error code. Dim RST As DAO.Recordset Dim Money1 As Double Dim Money2 As Double Set RST = Me.RecordsetClone On Error Resume Next RST.MoveFirst If Err = 3021 Then Err.Clear Do Until RST.EOF If Not IsNull(RST!PAIDOUTDATE) Then Money1 = Money1 + RST!PaidOut Else Money2 = Money2 + RST!PaidOut End If RST.MoveNext Loop Me.Txtresult = "SELECTED DRIVER - TOTAL OUTSTANDING NOW " & "..." & Format(Money2, "Currency") & " ------" & " TOTAL TO TRANSFER TO EXPENSES " & "..." & Format(Money1, "Currency") End Sub
Attached File(s)
|
 |
 
|
|
Mar 18 2012, 01:01 AM
Post
#2
|
|
|
Access Wiki and Forums Moderator Posts: 47,918 From: SoCal, USA |
Hi Ian,
What version of Access are you using? Knowing the Access version may help us determine a more appropriate response to your question. Try adding this at the end of your code: Me.SubformControlName.Form!ChargeableCostControlName = Money1 Just my 2 cents... (IMG:style_emoticons/default/2cents.gif) |
|
|
|
|
Mar 18 2012, 01:13 AM
Post
#3
|
|
|
UtterAccess Ruler Posts: 1,090 |
That would be easy to do but the problem is after inserting the amount to your Expense Input Sub form, the subform would be in either edit or new record always "always" (always replacing your Chargeable Cost field). And definitely you do not want that when you only want to view the form.
|
|
|
|
|
Mar 18 2012, 01:41 AM
Post
#4
|
|
|
UtterAccess Veteran Posts: 318 From: London UK |
Hi DBGuy
sorry 2007 Hope you are well I put the following in at the end of the Private Sub DriverPaidDate_AfterUpdate() Forms![F DRV PAIDDATE MASTER]![DRIVER PAIDDATE EXPENSES].Form![Chargeable] = Money1 what then happens is as follows I enter a paid date next to the £96.60 and a new record appears (which it should) in the expense input form but no £96.60 appears I enter a paid date next to the £356.60 and the figure of £96.60 appears in the expense input form I enter a paid date next to the £100.00 and the figure of £453.20 appears in the expense input form I enter a paid date next to the £90.00 and the figure of £553.20 appears in the expense input form Ian |
|
|
|
|
Mar 18 2012, 02:20 AM
Post
#5
|
|
|
UtterAccess Veteran Posts: 318 From: London UK |
Hi anrelgp
Thanks for the reply which I only saw after posting the reply to DBGUY Could you then just hold the figure "somewhere" and say post the amount on acceptance of a message box?? or when you close the main form ian |
|
|
|
|
Mar 18 2012, 04:07 AM
Post
#6
|
|
|
UtterAccess Ruler Posts: 1,090 |
That's a definite yes!
Create a TemVars before your procedure ends and assign the value of your private variable Money1 to it (both in DriverPaidDate_AfterUpdate and Form_Current). CODE Me.Txtresult = "SELECTED DRIVER - TOTAL OUTSTANDING NOW " & "..." & Format(Money2, "Currency") & " ------" & " TOTAL TO TRANSFER TO EXPENSES " & "..." & Format(Money1, "Currency") Tempvars("varTotalTransferAmount")=Money1 End Sub the create a On Before Insert event on your Expense Input subform: CODE Private Sub Form_BeforeInsert(Cancel As Integer)
Me.Chargeable = Nz( Tempvars("varTotalTransferAmount"), 0 ) End Sub |
|
|
|
|
Mar 18 2012, 07:19 AM
Post
#7
|
|
|
UtterAccess Veteran Posts: 318 From: London UK |
Hi arnelgp
Much appreciated Will try when I get home from work today Thanks Ian |
|
|
|
|
Mar 18 2012, 07:39 AM
Post
#8
|
|
|
UtterAccess Ruler Posts: 1,090 |
You're welcome.
|
|
|
|
|
Mar 18 2012, 10:43 AM
Post
#9
|
|
|
Access Wiki and Forums Moderator Posts: 47,918 From: SoCal, USA |
Hi Ian,
Sorry, guess I didn't read your form correctly. Good luck with your project. |
|
|
|
|
Mar 18 2012, 11:55 AM
Post
#10
|
|
|
UdderAccess Admin + UA Ruler Posts: 15,649 From: Upper MI |
arnelgp -
QUOTE Create a TemVars before your procedure ends I do believe you meant to spell it: "TempVars" (available only in Access 2007 and up) And to add a bit more detail about 'TempVars', the syntax for 'TempVars' is thus . . . TempVars.Add "Name", Value (note that "name" can be substituted for another variable name, so if you have variable that storeed the name of something you've already ensconced in a variable, you can use that without the dbl-quotes) Here are some examples: TempVars.Add "curTotalTrans", 356.60 TempVars.Add "lngCustID", 4455 TempVars.Add "bolIsActive", True TempVars.Add myFieldVar, "New User Name" (where "myFieldVar" might be a variable you've used to store the name of a field or control for the user name and "New User Name" is the value of that user name field/control) Once the values are assigned you can use them in your queries and code with various syntaxing: TempVars!curTotalTrans TempVars.Item("curTotalTrans") TempVars.Item(0) All three methods above will return "356.60" Use the "Remove" method to remove the variable or use RemoveAll to get rid of all of them. TempVars.Remove "curTotalTrans" An advantage of using TempVars is that they can be used in queries without using a function and can be created in any module and used throughout the entire Access session. The coolest thing about tempvars is that if there is a code error, the tempvars will not dump their values like Global Variables do when an error occurs. hope this helps |
|
|
|
|
Mar 18 2012, 12:30 PM
Post
#11
|
|
|
UtterAccess Veteran Posts: 318 From: London UK |
Thanks Cybercow
Very helpful Ian |
|
|
|
|
Mar 18 2012, 12:31 PM
Post
#12
|
|
|
UtterAccess Veteran Posts: 318 From: London UK |
Hi Arnelgp
Works a treat Thanks Ian |
|
|
|
|
Mar 18 2012, 02:05 PM
Post
#13
|
|
|
UtterAccess Ruler Posts: 1,090 |
ooooopppsss, thanks for the correction CyberCow (moo)!
|
|
|
|
|
Mar 21 2012, 12:03 PM
Post
#14
|
|
|
UtterAccess Veteran Posts: 318 From: London UK |
Further to this thread I have still a small problem with the updating of the Total to transfer amount in a much as that
I have normally gone into the Top rhs form and updated the PAIDDATE individually and therefore me going from record to record the Total to tranfer figure always gave me a running total. Now I have 2 other ways of updating the PAIDDATE (situated below the above form. I can block update via the Invoice number box and also via a date range box. (These are located on the main form). But what I have found to be not happening is the Total to transfer figure stays at £0.00 until I click on a record that has a PAIDDATE populated. I have shown the form again and also the coding for the Total to transfer figure below Any help would be appreciated. CODE Private Sub DriverPaidDate_AfterUpdate()
' Kindly written for me by Larry Larsen (UA) 08/11/2011 ' Loops thru the records that have had the datepaid populated ' and totals the amounts for money1 field and at the same time ' totals the amounts for money2 field (records that the datepaid ' field is null. ' It displays the money1 & money2 results in an unbound text box ' that is created in the form footer. ' Used in conjuction with code below that is situated in the forms ' OnControl (event Procedure) Dim RST As DAO.Recordset Dim Money1 As Double Dim Money2 As Double Set RST = Me.RecordsetClone If RST.RecordCount = 0 Then MsgBox "There were No Records Returned.", vbInformation, " No Records Returned" End If RST.MoveFirst Do Until RST.EOF If Not IsNull(RST!PAIDOUTDATE) Then Money1 = Money1 + RST!PaidOut Else Money2 = Money2 + RST!PaidOut End If RST.MoveNext Loop Me.Txtresult = "SELECTED DRIVER - TOTAL OUTSTANDING NOW " & "..." & Format(Money2, "Currency") & " ------" & " TOTAL TO TRANSFER TO EXPENSES " & "..." & Format(Money1, "Currency") ' The following creates a TempVar (thanks "arnelgp") which is used to update the Expenses subform control called chargeable ' in the before insert event procedure. The money1 figure is what should be paid to the driver. TempVars("varTotalTransferAmount") = Money1 End Sub Private Sub Form_Current() ' See above code for explanation ' Extra bit of code added (error handler) this is needed ' in this particular instance because of the way I select ' the drivers name for the underlying query. ie the drivers name is selected after ' form is loaded and as this code is loaded previously it produces ' the 3021 error code Dim RST As DAO.Recordset Dim Money1 As Double Dim Money2 As Double Set RST = Me.RecordsetClone On Error Resume Next RST.MoveFirst If Err = 3021 Then Err.Clear Do Until RST.EOF If Not IsNull(RST!PAIDOUTDATE) Then Money1 = Money1 + RST!PaidOut Else Money2 = Money2 + RST!PaidOut End If RST.MoveNext Loop Me.Txtresult = "SELECTED DRIVER - TOTAL OUTSTANDING NOW " & "..." & Format(Money2, "Currency") & " ------" & " TOTAL TO TRANSFER TO EXPENSES " & "..." & Format(Money1, "Currency") ' The following creates a TempVar (Thanks "arnelgp") which is used to update the Expenses subform control called chargeable ' in the before insert event procedure. The money1 figure is what should be paid to the driver. TempVars("varTotalTransferAmount") = Money1 End Sub
Attached File(s)
|
|
|
|
 |
|
Jump To Forum:
|
Go to Top · Lo-Fi Version | Time is now: 19th May 2013 - 04:59 PM |