Results 1 to 5 of 5
  1. #1
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168

    SubForm Data Without the Horrid SubForm Look

    after a couple of hours I finally remembered how to get sub form data to filter based on form controls i.e. Combo Box

    Click image for larger version. 

Name:	Caption1.PNG 
Views:	23 
Size:	39.7 KB 
ID:	42387

    However my ultimate goal is to get away from the ugly sub-form and do something a bit more elegant
    See form design Below

    but I am at a loss for how to get the data into the text boxes for the listed categories based on the filter drop downs for year and month.

    Note about record Dates and filter drop downs

    The original table with the data has actual date and date format i.e. 5/15/2019
    the qry uses the Month/Year Functions to pull the year and month of the transaction i.e. TransYr: Year([TransDate])
    and finally the Form (as it is currently set up) has the filter matching data i.e. January, February, March, listed in the row source property of the Combo Box Field

    Click image for larger version. 

Name:	Capture3.PNG 
Views:	23 
Size:	63.9 KB 
ID:	42388

    any help is always appreciated

    And for the bonus question, Ill be wanting to add a totals box on that form to give me the months total expenses.

    Evans House Account.zip

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    You could change the sub form to a "Tabular" type rather than a "datasheet" type for better control of how it looks. See Attached db for an example.
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Tuckejam is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Jan 2020
    Posts
    168
    Bob Fitz

    Thank you for that! It took me a bit to track down what was going on and how it worked but that is the format I was looking for.

    I do have a few questions just to understand the

    first is the query I had qry_MonthExpFilter which was pulling data from the tables, extracting or formulating the year and month columns and then setting criteria.
    You then added Query2 which pulled the data from qry_MonthExpFilter. I dont understand the need for the intermediary query.
    If anything I would think it would be the other way around where the first qry would just be pulling the data and the intermediary query would be formulating and setting criteria.

    You also created an intermediary Report "FrmExSum" which pulled its data from the "Query2", and then inside of the main form frm_monthlyExpFilter your created a subform based on the data from the intermediary form "FrmExSum"

    What is the purpose of adding these extra steps, the added "query2" and "FrmExSum"?

    at first I thought maybe it couldnt format a subform int the countinous form view unless it was based on an existing form of the same format. but I was able to generate the same outcome of the report you showed without the extra qry or subform.

    Thanks for your help on this

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    What is the purpose of adding these extra steps, the added "query2" and "FrmExSum"?
    .........but I was able to generate the same outcome of the report you showed without the extra qry or subform....
    To be honest I can't remember the reason. Might just have been that I didn't want to change what was already there in case what I was trying didn't work. Whatever, if you can make it work without them then the're not required
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I've just had a quick look at the db.

    Form "FrmExSum" is used as the sub form on "frm_MonthlyExpFilter" so I think you may need to keep that one.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

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

Similar Threads

  1. Replies: 1
    Last Post: 03-14-2017, 05:20 PM
  2. Replies: 2
    Last Post: 11-16-2016, 12:16 AM
  3. Replies: 7
    Last Post: 07-15-2011, 01:58 PM
  4. Replies: 4
    Last Post: 04-07-2011, 03:39 PM
  5. Replies: 1
    Last Post: 12-10-2005, 04:52 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