Subform Coding Questions

[Michelle F]

Hi All! I have two subform issues.

1. My main form is an activity entry from where the user can add an activity
for a specific project number. I have added a subform to display document(s)
(if any) associated with the project. The projects and documents are linked
through the documents table (i.e. each document is associated with one
project, but each project can have more than one document). I want the
subform to NOT appear if there are NOT any documents associated with that
project. But, with the way the relationship is defined, I am not figuring
out how to do this. The best I can do is add the documents field to my form,
but then I end up with repeat activity entries for each project associated
with more than one document (i.e. Project A has 2 Documents, so main form
shows 2 entries for Activity A of Project A and 2 entries for Activity B for
Project B). If I don't add the document field to the form, the subform
displays the correct data (and no repeats) but insists on displaying even
when no documents present. I would like code like

Me.sfrmProjectDocuments.Visible = Not IsEmpty(Me.DocumentNumber)

but since I cannot put the DocumentNumber field in my main form without
causing repeats, I am at a loss.

2. Much shorter second question. I would like to make a subform pop-up
instead of being embedded. I set the "pop-up" property to yes, which makes
it pop up if I open it as a subform only, but it remains embedded in the main
form.

Thank you all very much!

 

[n00b]

1. In the Form Current event of the project form, add the following:

sfrmProjectDocuments.Visible =
(sfrmProjectDocuments.Form.RecordsetClone.RecordCount > 0)


That should do it!


2. You can't do what you are thinking with the pop-up property. You
have to open the subform as a dialog like this:

DoCmd.OpenForm "MySubform", acNormal, , , , acDialog

Of course, this is not really a subform at this point, its a modal
dialog. There is no technical difference between a regular form and
subform, they are both forms unless it is being displayed in a subform
control.

If you are popping up a form that is already being displayed as a
subform, you will have to close it first by setting the main form's
subreport control's source object to empty.



DoCmd.OpenForm

 

[Michelle F]

Wow! That worked great! To think I was fiddling with it for at least a
day! For my own edification, could you explain what the code below means?
Specifically, the functions "Form.RecordsetClone.RecordCount."

 

[Michelle F]

As for the second issue, this seems to work good, but it doesn't open the
subform to the equivalent record (project) being displayed in the main form,
but rather to the first record (subform record). But as an embedded subform,
it only displays the info for the record (project) being updated.

Is there a way to tell it, when opening this (sub)form, only display the
record that corresponds to the record being displayed in the main form. For
example, when updating Project A, only show me Project A info and not allow
scrolling through all projects in subform.


Thanks again!!

 

[n00b]

The sfrmProjectDocuments.Form part is referencing the subform object's
form object (which is the actual form that is being displayed.)

The .RecordsetClone.RecordCount is referncing the subform's
recordsetclone, which is the object you use to access the underlying
RecordSource of the form as a recordset. The RecordCount is a
property of a recordset telling you how many records there are.

 

[n00b]

You need to fill out the where clause parameter of the DoCmd.OpenForm
command like so:

DoCmd.OpenForm "MySubform", acNormal, , "DocumentNumber = '" &
Me.DocumentNumber & "'", , acDialog

Just as a side note, the DoCmd.OpenReport has a where clause parameter
as well that makes passing criteria to a report a breeze.