Results 1 to 5 of 5
  1. #1
    thyPot is offline Novice
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    3

    How to link ControlSource of say 10 TextBoxes to 10 Fields without writing repetitive code?

    I have a report/form that has many textboxes with the ControlSource of each corresponding to the sum of a Field in a table. Below is the code I have, which works but it is very repetitive and difficult to maintain when I need to make changes, and I have to do similar projects with many more fields. I've read into arrays and loops but I just do not understand it enough to create code that works. Anyway, if you can please show me how to make the code below more efficient. Thank you very much.




    ' Nombre d'enfant ayant frequente le centre au cours du mois
    Reports!rptTest!NoHosp.ControlSource = "=DSum('[No_frequente_Hos]', 'qryFiche', mod1_setMySQL())"
    Reports!rptTest!NoVac.ControlSource = "=DSum('[No_frequente_Vac]', 'qryFiche', mod1_setMySQL())"
    Reports!rptTest!NoCon.ControlSource = "=DSum('[No_frequente_Con]', 'qryFiche', mod1_setMySQL())"
    Reports!rptTest!totFreq.ControlSource = "=DSum('[Tot_frequente]', 'qryFiche', mod1_setMySQL())"

    ' Nombre total depiste au cours du mois
    Reports!rptTest!dep02.ControlSource = "=DSum('[No_depiste_0_2]', 'qryFiche', mod1_setMySQL())"
    Reports!rptTest!dep35.ControlSource = "=DSum('[No_depiste_3_5]', 'qryFiche', mod1_setMySQL())"
    Reports!rptTest!dep614.ControlSource = "=DSum('[No_depiste_6_14]', 'qryFiche', mod1_setMySQL())"
    Reports!rptTest!depHosp.ControlSource = "=DSum('[No_depiste_Hos]', 'qryFiche', mod1_setMySQL())"
    Reports!rptTest!DepVac.ControlSource = "=DSum('[No_depiste_Vac]', 'qryFiche', mod1_setMySQL())"
    Reports!rptTest!depCon.ControlSource = "=DSum('[No_depiste_Con]', 'qryFiche', mod1_setMySQL())"
    Reports!rptTest!totDep.ControlSource = "=DSum('[Tot_depiste]', 'qryFiche', mod1_setMySQL())"

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why do you have to use DSum? What does mod1_setMySQL() function return?

    Why are you using VBA to set ControlSource - why not have the expressions directly in the ControlSource?

    Is the report bound to the table or query with the data you want to sum? Have you looked at using report Sorting & Grouping features with aggregate calcs in footer section?
    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
    thyPot is offline Novice
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    3
    Hi June7, thanks for your response, I will try to make my question clearer. I need to create a dynamic report. What I have is a form with 3 listboxs that allows the user to choose YEAR, MONTH, and REGION. After selecting those criteria, the user can push an APPLY button and a report opens up that contains sums of many indicators (ex. # of children vaccinated, #of mothers tested, etc.). So to answer your questions-

    Why do you have to use DSum? What does mod1_setMySQL() function return?
    DSum is the only way I know with VBA to get the sum of a field. The mod1_setMySQL() is a function I made that returns an SQL statement (based on the users choices for YEAR, MONTH, and REGION) as a parameter to the DSum function.

    Why are you using VBA to set ControlSource - why not have the expressions directly in the ControlSource?
    I'm using VBA instead of entering the expression directly because it needs to be dynamic based on user choices.

    Is the report bound to the table or query with the data you want to sum? Have you looked at using report Sorting & Grouping features with aggregate calcs in footer section?
    The report is not bound a table or query and I do not want to use Sorting or Grouping features because the individual information is not necessary for my report. I just need the sums. I have attached my project, the menu is in french but just click on RAPPORT then APPLIQUER to see what I'm trying to describe.

    My code currently works, but I have to do many more similar projects each with many fields to sum so I would like to make my code more efficient or if there is a better alternative approach, please let me know. Thank you so much.
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If all you want to show on the report is aggregate data, the better alternatives are:

    1. the report as I described and set the Detail section as not visible

    2. an aggregate query as report RecordSource

    Either way, a dynamically filtered report can still be accomplished. I use code to construct filter and apply filter when report is opened. Review http://www.allenbrowne.com/ser-62code.html

    DoCmd.OpenReport "rptTest", acViewPreview, , strWHERE, acWindowNormal


    The qryFiche has circular linking between tblDistricts, tblSites, tblRegions - need to eliminate one link. http://www.codeproject.com/Articles/...atabase-Design

    You have lookups with alias set in table, review http://access.mvps.org/access/lookupfields.htm
    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
    thyPot is offline Novice
    Windows 8 Access 2013
    Join Date
    Dec 2014
    Posts
    3
    I definitely complicated the task, I didn't think to hide the detail section. And also thanks for the additional tips to improve my project and knowledge of database design.

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

Similar Threads

  1. Replies: 17
    Last Post: 06-19-2013, 02:58 PM
  2. Replies: 16
    Last Post: 12-08-2012, 07:44 AM
  3. Writing code on the fly - TransferSpreadsheet
    By IanT in forum Import/Export Data
    Replies: 1
    Last Post: 05-24-2012, 08:28 AM
  4. Replies: 5
    Last Post: 03-15-2012, 09:46 AM
  5. Form Issue (repetitive fields)
    By netchie in forum Access
    Replies: 2
    Last Post: 08-15-2011, 02:39 PM

Tags for this Thread

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