Results 1 to 10 of 10
  1. #1
    Seiquo's Avatar
    Seiquo is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    May 2017
    Location
    Quebec
    Posts
    44

    How to replace an empty sub-report control with 0 value?

    Hi all,

    First of all please forgive my English since it is not my mother tongue. I will try to do my best to explain my problem...

    I have a main report (statement) based on a query, with two subreports: one for commissions and the other for expenses. Each of them is based on a different query.
    The links between the main report and the subreports are the broker id and the statement number.

    My problem is that sometimes I have commissions only, or expenses only, for a given broker and statement number. In this case, my report shows data for either the commissions or the expenses, but I get an empty rectangle for the other. Due to the relative query that has nothing to return for this given broker and statement.

    I would like to see the content of my subreport with a single row and 0 values in each field.

    Is there a way to do this?

    Thank you so much in advance for your 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
    Basically, can't. There are no records and therefore the report will not display. Otherwise, need a dummy record in the RecordSource with all zero values.

    My solution in this situation was to conditionally display label with caption like: "No Commissions to report". Code behind the report:

    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Me.lblOU.Visible = Not Me.ctrOU.Report.HasData
    Me.lblCO.Visible = Not Me.ctrCO.Report.HasData
    Me.lblRC.Visible = Not Me.ctrRC.Report.HasData
    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.

  3. #3
    Seiquo's Avatar
    Seiquo is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    May 2017
    Location
    Quebec
    Posts
    44
    Thank you June7 even if you are the bearer of bad news...

    I have another question though, and maybe this thread should be moved to the VBA Code forum:

    The statement is issued after the user opens a form and checks each row they want to appear on the statement, in the Commission subform and/or the Expense subform.

    Then they hit the Statement button that triggers the following code:

    Code:
    PrivateSub BtnReleve_Click()
    DoCmd.RunSQL"UPDATE RqSelect_Dep SET RqSelect_Dep.DateDeduit =[Formulaires]![FmDate]![TxtDateRel], RqSelect_Dep.RefReleve =[Formulaires]![FmDate]![TxtNoRel] WHERE (((RqSelect_Dep.SelectDep)=Yes));"
    DoCmd.RunSQL"UPDATE RqSelect_Com SET RqSelect_Com.DateReleve =[Formulaires]![FmDate]![TxtDateRel], RqSelect_Com.RefReleve =[Formulaires]![FmDate]![TxtNoRel] WHERE (((RqSelect_Com.SelectCom)=Yes));"
    DoCmd.RunSQL"INSERT INTO TbReleve ( NoCourtier, DateRel, NoRel )SELECT[Formulaires]![FmDate]![TxtIDCourt] AS IDCourt, [Formulaires]![FmDate]![TxtDateRel]AS DateRel, [Formulaires]![FmDate]![TxtNoRel] AS NoRel;"
    DoCmd.Close
    End Sub
    Is there a way to improve it and add something that could read: "If no row is checked in Commission subform, then add a dummy record for the same broker and with the same number in the Commission table, and if no row is checked in Expense subform, then add a dummy record for the same broker and with the same number to the Expense table".

    My knowledge of VBA doesn't allow me to assess and type my "wish", so I'm just asking...

    If it is too complicated, then I will go with the conditional display (and face the end users and their complaints).

    Thank you again!

  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
    Anything can be done with enough code, even if it means writing records to a 'temp' table (table is permanent, records are temporary) or using a UNION query to assure the dummy record.

    None of the options I considered appealed to me - too much coding. What can be better than a simple statement? No need to scan each field to see if there is a value.


    Users select records by checking a field? Do you have multiple users trying to do this at the same time?
    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
    Seiquo's Avatar
    Seiquo is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Location
    Quebec
    Posts
    44
    Thank you June7.

    I have thought about a Union Query and may explore this avenue.

    Yes there are multiple users using the Dbase (and therefore checking boxes) at the same time but the Selection table is a local table in the front file. So they do not step on each other's toes.

    What can be better than a simple statement?
    Do you mean one statement for the Commissions and one for the Expenses?

    Have a very good day.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, as shown by my example code, I have a report that has 3 subreports. Any of them might not have data.
    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.

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    An alternative. Though it doesn't address the displaying of a "0" record like June's method does, it may be simpler if you don't need that:

    http://theaccessweb/forms/frm0022.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Paul, the link appears to be broken.
    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.

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Sorry, fubared the copy/paste when I updated to their new domain:

    http://theaccessweb.com/forms/frm0022.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    Seiquo's Avatar
    Seiquo is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    May 2017
    Location
    Quebec
    Posts
    44
    Thank you June7 and Pbaldy. I will work on both suggestions and pick the most suitable for my DB project.

    I check "resolved" (even if I have to give up on my dream solution!) and thank you very much for your help.

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

Similar Threads

  1. Replies: 5
    Last Post: 01-20-2014, 08:51 AM
  2. Replies: 7
    Last Post: 10-28-2012, 02:55 PM
  3. Replies: 3
    Last Post: 06-07-2012, 07:05 AM
  4. Replies: 1
    Last Post: 04-18-2012, 11:06 AM
  5. replace a empty field with the word "none" how??
    By techexpressinc in forum Queries
    Replies: 1
    Last Post: 01-15-2010, 11:02 AM

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