Full Version: Subform inside continuous form not allowed
UtterAccess Forums > Microsoft® Access > Access Forms
Snare
Hello,
tried inserting a subform into a continuous form, but Access will not let me do it. I am wondering if there is another way to address my problem.
I am developing a database to record comments during meetings. Therefore, I have a table for Comments.
I would also like to be able to group these comments by meeting, so that all of the comments which were recorded in a given meeting are grouped together. So, I have another table for Meetings.
Now, each Project can have more than one Meeting. Ideally, I would like to have a form which displays all of the Comments pertaining to a given Project, grouped by Meeting. For example:
---------------------------------------------------------------
Project: System Overhaul
First Quarter Forecast Review Meeting
- Project is proceeding according to plan.
- Everything is good.
Dashboard Review Meeting
- Things are a bit behind.
- We need more money.
Second Quarter Forecast Review Meeting
- Things are bad.
- We're screwed.
---------------------------------------------------------------
Each Meeting can have an arbitrary number of Comments and each Project can have an arbitrary number of Meetings. Is there another way to display the information in this manner?
Thank you.
- Mark
Jack Cowley
The hierarchy is Project > Meetings > Comments, right? If that is the case here is your table setup:
blProjects
ProjectID (PK and auto)
...other necessary fields...
tblMeetings
MeetingsID (PK and auto)
ProjectID (FK)
..other necessary fields..
tblComments
CommentsID (PK and auto)
MeetingsID (FK)
Comments
Set up a form based on tblMeetings. Add to it a subform based on tblComments. Save this form then create a form based on tblProjects and add the Meetings form with the Comments subform.
hth,
Jack
KenSheridan
Mark:
o Show both the meetings and comments in continuous forms you can use two correlated subforms within a single view Projects form. First create a form based on Projects and embed a continuous subform based on Meetings in it in the usual way, linked on ProjectID. Now add an unbound text box to the parent projects form, txtMeetingID say, set its Visible property to False and as its ControlSource reference the primary key of the Meetings subform, e.g.
=sfrMeetings.Form!MeetingID
Note that sfrMeetings here is the name of the subform control in the Projects form, i.e. the control which houses the subform, not the name of the underlying form object (unless they both have the same name).
Now add a continuous view subform based on Comments to the Projects form and as the subform control's LinkMasterFields property put the name of the hidden text box on the parent form, txtMeetingID. Set its LinkChildFields property to the name of the foreign key column in the Comments table, MeetingID. Note that you have to do this directly in the subform control's properties sheet; it can't be done in the dialogue you get if you click the build button (the one with 3 dots to the right of the property in the properties sheet).
HAs you navigate from project to project in the parent form you should get a list of the meetings for the current project in one subform and a list of the comments from the selected meeting in the other subform. As you navigate within the meeting subform the comments in the other subform will change to those for the meeting selected. This does mean that you only see the set of comments for one meeting at a time. To see them all at once grouped hierarchically as in your example it would be better to use a report and open it in print preview. With the form you can of course add/edit the data, which you can't in a report.
Snare
Hello Ken,
Thank you for your response. I think I still like to have the hierarchical structure as in my example so that the user can see all of the comments without having to click and search through the meetings.
I thought of another possible way to do it, but I'm not sure if it will be possible. Have a continuous subform based on a join between tblComments and tblMeetings (so that each comment knows which meeting it belongs to).
Then I could generate something like this:
---------------------------------------------------------------
Project: System Overhaul
First Quarter Forecast Review Meeting
- Project is proceeding according to plan.
First Quarter Forecast Review Meeting
- Everything is good.
Dashboard Review Meeting
- Things are a bit behind.
Dashboard Review Meeting
- We need more money.
---------------------------------------------------------------
Note that the meeting name will appear for each comment. If I could find a way to show the meeting name for only the first comment (and hide it for the rest of them), then I could get something like:
---------------------------------------------------------------
Project: System Overhaul
First Quarter Forecast Review Meeting
- Project is proceeding according to plan.
- Everything is good.
Dashboard Review Meeting
- Things are a bit behind.
- We need more money.
---------------------------------------------------------------
Do you think there is a way to do this? I know there is a way to set a control to be invisible. But I guess the hard part is figuring out how to isolate the first comment of each meeting. Thanks.
- Mark
KenSheridan
Mark:
The use of a query joining the meetings and comments tables had also occurred to me, but I'd not pursued that line of thought due to the repetition of the meetings data. Unfortunately a form cannot be grouped in the same way as a report, in which its possible to eliminate the duplicated data by means of a group header of course.
About the nearest you could get in a form would, I think be to base the subreport on the Meetings table and display all the comments in a single tall text box control by concatenating the values from the Comments table. There is a pretty standard way of doing this with a function which creates a recordset of the matching rows from the Comments table:
CODE
Public Function GetComments(varMeetingID) As String
    Dim rst As ADODB.Recordset
    Dim strSQL As String, strComments As String
    
    If Not IsNull(varMeetingID) Then
        strSQL = "SELECT Comment From tblComments " & _
            "WHERE MeetingID = " & varMeetingID
            
        Set rst = New ADODB.Recordset
        rst.ActiveConnection = CurrentProject.Connection
        rst.Open _
            Source:=strSQL, _
            CursorType:=adOpenForwardOnly, _
            Options:=adCmdText
        
        With rst
            Do While Not .EOF
                strComments = strComments & vbNewLine & vbNewLine & _
                    .Fields("Comment")
                .MoveNext
            Loop
        End With
        
        ' remove leading carriage return/line feeds
        GetComments = Mid$(strComments, 5)
            
        Set rst = Nothing
    End If
    
End Function

You'd call it as the ControlSource property of the unbound text box in the continuous subfrom, passing the MeetingID of the current Meetings record into the function lke so:
=GetComments([MeetingID])
I've assumed in the code that MeetingID, or whatever the primary key of tblMeetings is called, is a number data type rather than text.
The text box will be the same height in each row of the subform of course rather than expanding/shrinking to fit as in a report. BTW the CanShrink property of a control in a form only applies to when the form is printed, so can't be used in this context.
You will probably find that performance is not lightning fast with this solution due to the number of function calls, one for each row of the subform, each of which has to establish the recordset object.
Snare
Hi Ken,
Thank you very much for your solution. I have elected, instead, to display the information using a report instead. It just makes things a bit easier. It's too bad Access doesn't allow you to stick subreports on forms.
Thanks again.
- Mark
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.