Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Subform Help...

Subform Help...

Subform Help...

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.


RE: Subform Help...

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

RE: Subform Help...

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

RE: Subform Help...

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:

     (AutoNumber,Long Int,Increment,Indexed- Yes,Dups OK)
     (Text,Indexed- Yes,Dups OK)

     (Text,Indexed- No)

     (Number,Long Int,Indexed- Yes,Dups OK)
     (Text,Indexed- No)

Where do I go from here?  Thanks.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close