Results 1 to 7 of 7
  1. #1
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    94

    Can I pull the value in a report's textbox to append to a table field

    Is there a way to get the value from a calculated textbox on a report? I have an end of month account report and the closing balance is a calculated text box.To start a new account for the following month, I want the value from the closing balance control, to put on the brought forward field in the next account table.
    The report has two sub reports on it.
    I've found a way, by calculating the closing balance exactly as the control does in the first account, doing the same for the two sub reports with queries and then using an update query to put the resulting value into the new account table, from which it finds it's way to the next month's account report. It just looks as though there must be a 'neater' way to do it.


    Thanks for any help.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, code can read value from calculated textbox on report and save to table.

    What exactly are you asking for? If you have a setup in place that meets your needs, what do you want to do different?

    Saving aggregate value is usually not necessary. Calculate it when needed. See if this helps http://www.livewarepub.com/ftp/TechD...d%20Report.pdf
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    94
    Hi June7, thanks for your reply. I do already have the account report set up and it works perfectly. What I need is a way to read the data in one of the controls on the report, so that I can insert it into the next account. The idea is similar to the pdf that you pointed me to but I would prefer to do it this way, i.e. create a new table every month and generate a new report from it.
    As I said, I have devised a solution but it's very long-winded. If I could find code to 'copy' the value from that one textbox, I would be sorted I think.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I do NOT advise any method that requires creating a new table every month. Any process that requires routinely modifying design with code is ill-conceived.

    As already confirmed, code can pull value from calculated control. The real trick is figuring out what event to put code in.

    Here is example from my db that runs code in Report Close event. It has reference to report control to pull a value - although it is not a calculated value, the principal is the same:

    Code:
    Private Sub Report_Close()
     'save calculated results to ApprovedFurnaceCalibrations table
     'and open FurnaceCalibrationFactors report
     Dim cn As New ADODB.Connection
     Dim rs As New ADODB.Recordset
     Set cn = CurrentProject.Connection
     Set rs = New ADODB.Recordset
     rs.Open "SELECT Furnace, MixNumber, RRound([Avg12]-[ASPHOPT],2) AS CF " & _
     "FROM 610 RIGHT JOIN FurnaceCalibrationReportData ON [610].LABNUM = FurnaceCalibrationReportData.MixNumber " & _
     "WHERE Not Avg12 Is Null;", cn, adOpenStatic, adLockPessimistic
     If Not IsNull(DLookup("MixDesignNum", "ApprovedFurnaceCalibrations")) Then
         If MsgBox("Correction Factors already recorded. Update all values?", vbYesNo, "CF") = vbYes Then
             cn.Execute "DELETE FROM ApprovedFurnaceCalibrations WHERE MixDesignNum='" & Me.MixNumber & "'"
         End If
     End If
     If IsNull(DLookup("MixDesignNum", "ApprovedFurnaceCalibrations", "FurnaceID='" & rs!Furnace & "' AND MixDesignNum='" & Me.MixNumber & "'")) Then
         While Not rs.EOF
             cn.Execute "INSERT INTO ApprovedFurnaceCalibrations(FurnaceID, MixDesignNum, CF) VALUES ('" & rs!Furnace & "', '" & Me.MixNumber & "', " & rs!cf & ")"
             rs.MoveNext
         Wend
     End If
     Exit_proc:
     rs.Close
     DoCmd.OpenReport "FurnaceCalibrationFactors", acViewPreview
     CurrentDb.Execute "DELETE * FROM FurnaceCalibrationReportData"
     End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    94
    Thank you again, for your code and your advice. I will take a closer look at both the code and the pdf you sent the link for earlier, and see if I think I'm capable of taking your advice.
    Best regards.

  6. #6
    tbjmobile@gmail.com is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    94
    Hello again June7,
    I said I would look at the options for making my account 'everlasting' following your advice. I have done some research and I'm looking at the method of finding all the records for each customer from the previous month, and the the record that has the highest transaction number (the last entry's running total). This will give me the number to carry forward into my next account report. I don't fully understand the sql yet but I have a couple of intermediate questions.
    If I use this table field value as my opening balance forward for the next month's report, what do I do about the two sub-reports that I have to include in the report because they are of course included in the reports closing balance at the moment and I don't know how I would go about including them in the 'everlasting' table.
    I also have two weekly amounts that are calculated with the number of Thursdays in the month. A weekly cleaning cost etc.. It would leave the data open to human error if they had to be
    copied in manually every month.
    Any ideas on how to accommodate these sub-reports etc. please?

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I don't understand your data structure well enough to provide guidelines specific to your situation. I simply do not understand what you described.

    Back to my advice to not save aggregate data but to calculate when needed.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 11-29-2016, 09:29 AM
  2. Replies: 1
    Last Post: 03-30-2015, 10:57 AM
  3. How do I pull address from table onto my report
    By tanyapeila in forum Reports
    Replies: 19
    Last Post: 03-26-2014, 01:53 PM
  4. Replies: 2
    Last Post: 03-07-2013, 04:50 PM
  5. Pull Value from From Using Append Query
    By kedstracker in forum Queries
    Replies: 1
    Last Post: 06-21-2011, 03:12 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums