Solved Forms and Reports to Show Parent/Child records (2 Viewers)

[mounty76]

Hi All,

As I understand I cannot put a continuous form within another continuous form.....is there anyway around it to display it as per the attached so it shows multiple parent and associated child records in a continuous form or report? In a perfect world I'd like it like this in both a form and also a report but I'm not sure if it is even possible for either?

Any ideas most welcome!

Thank you

 

[strive4peace]

you can do that using a grouped report

while you can't put a continuous form in the detail section of a continuous form, you can put it in the form header or footer -- I use the form footer

 

[MajP]

Opinions on form design

I see. Thank looks soo much simpler than messing with a tree view. Thanks MajP!

www.access-programmers.co.uk

 

[mounty76]

Thanks guys, I've just gone for the subtable in a form footer as seemed the cleanest way, just need to scroll through the main form records, not the end of the world, I will need to make the report more like the attached picture I did, just need to figure out how to make a grouped report....google is getting some hammer!

 

[strive4peace]

@mounty76 make a query that shows data from all the tables you want in the grouped report. Use the report wizard to make a new report based on that query

 

[mounty76]

@strive4peace thanks for that, I made a report last night and it works all good, just need to tidy it up now. I don't know why Access doesn't allow forms like this but it can make reports like this, maybe a new update for MS

 

[strive4peace]

you're welcome, @mounty76 ~ happy to help

 

[strive4peace]

@strive4peace thanks for that, I made a report last night and it works all good, just need to tidy it up now. I don't know why Access doesn't allow forms like this but it can make reports like this, maybe a new update for MS

ps, @mounty76

adding on, here are a few videos that might be useful for you. You've already gone past some of what's covered in the first video.

Create a Grouped Report using the Wizard in Access, then Modify it (16:53)

for a lesson on making format better

Polish Reports in Microsoft Access (25:01)

It's nice to customize your QAT so it's faster to align controls. Other good things here too.

Quick Access Toolbar (QAT) -- Customize and Share (Import/Export), Microsoft Access (12:52)

Reports are a very powerful feature in Access! By the way, you can show a report on a form in a subform/subreport control! Data can't be changed, but the user can see it -- this gives you a lot more options for displaying data on forms

 

[mounty76]

Thanks very much for these, much appreciated. A grouped report from what I can see is all based on a query, is it possible to make a grouped report based on a filtered form? The filtered form that MajP uses worked great but as it is filtered I don't have a query to grab data for a report, I can do a report based on the form filter results but not sure how I can link this to the subtable records related to the filtered form records? Any ideas welcome

 

[strive4peace]

you're welcome @mounty76. If your report contains the fields that your form is filtered by, you can use the form Filter in the Where parameter of OpenReport

dim sWhere as string
sWhere = me.Filter
docmd.OpenReport  "myReportName",acViewPreview,,sWhere

 

[mounty76]

Sorry no, I mean my report is based on the results of a filtered form, however the form is a parent of a child form, but as it is a continuous form I cannot have the child records in the same form. I'd like my report to be based on the filtered form (parent) but then also to show the child related records for each record on the form.

I can do a grouped report with the child and parent records from a query but as I cannot put a continuous child form in a continuous parent form I'm not sure how I can do this from a filtered form? Hope that makes sense!!! An easy work around around would be if I can make a query based on the results of a filtered form? Is that possible? If so then I can bring in the child related records into that query.

Hope that all makes sense lol

 

[strive4peace]

@mounty76, could you perhaps post a stripped down version of your database with sample data? And specify steps you want to do?

 

[strive4peace]

I cannot put a continuous child form in a continuous parent form I'm not sure how I can do this from a filtered form?

if the child form is in the footer and you're using LinkMasterFields and LinksChildFields to show the data, it shouldn't matter

 

[mounty76]

Hi Strive4peace,

Attached is stripped down copy of db. Basically the ExpenseSearchF runs a great filter (from MajP ) then I have a button to open the related child information in a form.

When the filters are applied to ExpenseSearchF I want to be able to run a report from this form that shows the data as per the attached example, basically parent and child records together in a continuous report.

I can do a report like this but can only make it from a query where the report is grouped by the expenseID, this is fine but I need it to work with the filters applied by the form and not from a query, unless the filters on the form can update a query?

Hope that makes sense!

 

[strive4peace]

thanks, @mounty76 I won't have a chance to look for awhile, maybe someone else will jump in!

 

[MajP]

Do you have a unfiltered report already built? If not it can be done with sorting and grouping.

The things you are filtering on are not in the grouped report so you cannot pass the filter. You need to pass the ExpID that are are found after applying the filter.

When I get time I will show how to do it. But basically you do your filtering then have a button to open the report. Loop the filtered recordset and you create a string with the returned ExpID Assume 1130, 1121 ... where in the filtered set. You make this filter and pass that to the report.

ExpID IN (1130, 1121, .... 1146)

Something like (untested)

dim strOut as string
dim rs as dao.recordset
set rs = me.recordset
do while Not rs.eof
  if strOut = "" then
      strOut = rs!expID
  else
    strOut = strOut & ", " & rs!expID
  end if
  rs.movenext
loop
if strOut <> "" then strOut = "ExpID IN (" & strOut & ")"

now pass the filter in the docmd.openreport

 

[MajP]

See if this is what you mean.

 

[mounty76]

Very sorry for the delayed response here, has been a hectic week. This is amazing, thanks so much, this is exactly what I needed. Never ceases to amaze me how good you guys are at doing this. Much appreciated.

 

[mounty76]

I would like to say that I'm all good now however I just came across another problem....more than likely easy to fix with VBA?

In the DB you sent me back I have added the local cost and currency, I'd like to have these total up and show in the form header, I've done this with the home currency as it is straight forward textbox with =sum function, however as there are different local currencies involved I'm not sure how to do this with the filtered form.....below is a screenshot of what I have done with the total home currency and also a box of what I'm trying to achieve? Do you have any ideas? Also then to take this information into the same search report? Thanks very much, your saving my life right now!!

 

[MajP]

Label should read GBP