Subform Help...

[Rjc8513]

I'm a beginner-to-intermediate user but this is the first time I've had to use subforms and I am confused. Can someone help?

OVERVIEW: I have a table [REPORTS] with the fields [ID](AutoNumber), and [REPORT NAME] (Text). A second table [RECIPIENTS] has the field [RECIPIENT NAME] (Text).

OBJECTIVE: I want to use a form with a listbox or drop-down displaying the reports from [REPORTS].[REPORT NAME]. Using a subform I want to assign one or more of the names from [RECIPIENTS].[RECIPIENT NAME] to each report name.

I've read the Help files in Access, read some documentation, but I'm at a loss on how to accomplish this. Any help would be appreciated. Thanks.

Richard...

 

[jgoodman00]

I think what you are saying is that you want to be able to assign several recipients to any given report. Therefore it would be best to create a recipient ID (again autonumber) in tblRecipient. You will then need to duplicate this field in tblReport. At this point you should set the properties for tblRecipient!RecipientID to Indexed (No Dups) & also as the primary key. In tblReport!RecipientID set it as indexed (Dups OK). Now create a relationship between the two & enforce referential integrity. Next create your mainform with tblReport as the Recordsource. Then create your subform with tblRecipient as the recordsource. You should now be able to add a combo-box (or list box) & the wizard should show the option for using it to select records from a table. Obviously you want it to select records from tblReport, which should already be set as the recordsource. After this, create a subform, & point the wizard to subformRecipient. Because you have created a relationship it should automatically fill in the LinkMasterField & LinkChildField properties. At this point hey presto......you should be able to assign multiple people to individual reports........ James Goodman
j.goodman00@btinternet.com

 

[RickSpr]

No, I'm afraid that won't work. A linked subform only displays records that are already related to the record in the main form.

Besides, I strongly suspect that you want each report sent to several recipients, and each recipient to receive several reports. That's a many-to-many relationship. For that, you'll need a third table. Call it DISTRIBUTION, and give it fields ReportID and RecipientName. While still in design view, click the Lookup tab at the bottom, and set the properties as follows.
For ReportID:
Display Control: Combo Box
Row Source: REPORTS
Column Count: 2 (or 'n' if ReportName is the nth column)
Column Widths: 0 (or "0,0,..." n-1 times)
Limit to List: Yes
For RecipientName:
Display Control: Combo Box
Row Source: Recipient Name
Column Count: 1 (or 'n' if Recipient Name is the nth column)
Column Widths: empty (or "0,0,..." n-1 times)
Limit to List: Yes

Save the table, then open the Relationships window. Create a relationship from REPORTS.ReportID to DISTRIBUTION.ReportID and from RECIPIENTS.[Recipient Name] to DISTRIBUTION.RecipientName. Click the Enforce Referential Integrity check box for each of these.

Now if you open the DISTRIBUTION table in datasheet view, you'll have a pair of combo boxes for each row that let you select report names and recipient names.

To make a form/subform as you described, base the main form on REPORTS and the subform on DISTRIBUTION. Create the subform first, and drag Recipient Name and ReportID from the field list to the form. You'll automatically get combo boxes for them, because of what you did on the Lookup tab. You'll want to make the ReportID invisible. It duplicates what the main form will already show, but you have to have it there in order to add recipients to the underlying DISTRIBUTION table. Make the form as short as possible, with just enough room for the Recipient Name combo box, and set the form's Default View to Continuous Forms.

When you create the main form, drag the Report Name from the field list to the form, and drag the subform from the database window to the form. Make the subform control long enough to show many users.

When you save the main form and switch to Form View, the subform will display recipients for the selected report in the main form. You should also get a "*" line at the end of the subform list, in which you can add a new recipient. Each new recipient will add a row with the ReportID and RecipientName to the DISTRIBUTION table. Rick Sprague

 

[Rjc8513]

Sorry guys, still confused. James, thanks for the help but I think Rick has correctly guessed what I want to do. Rick, can you back up to the beginning (a little dense!)? I guess I combined yours and James' reply. This is where I am right now:

REPORTS.ReportID
(AutoNumber,Long Int,Increment,Indexed- Yes,Dups OK)
REPORTS.ReportName
(Text,Indexed- Yes,Dups OK)

RECIPIENT.RecipientName
(Text,Indexed- No)

DISTRIBUTION.ReportID
(Number,Long Int,Indexed- Yes,Dups OK)
DISTRIBUTION.RecipientName
(Text,Indexed- No)

Where do I go from here? Thanks.

Richard...