Sure.
First, design and populate tblReport
tblReport:
ReportID, Autonumber
ReportName, text,100
ReportDescription, text, 100
Once you have that table designed, populate it with the name and
description of every report you want to be selectable as a Customer
Report.
tblCustomerReport
CustomerReportID, Autonumber
CustomerID, Long Integer (Foreign Key on tblCustomer)
ReportID, Long Integer (Foreign Key on tblReport)
CustomerReportNote, text, 250, notes about this report and this
customer
In the Relationships window bring up tables tblCustomer and
tblCustomerReport. Draw a relation from tblCustomer.CustomerID to
tblCustomerReport.CustomerID. Doubleclick the relation line and
establish the one-to-many relationship. Set Referential Integrity on
and enable Cascading Deletes.
Design a new form, frmCustomerReports based on tblCustomer. Make it
easy to find the customer by including a Find Customer combobox in the
header area. Make sure that CustomerID is included in the form's
underlying query but don't show it on the form. Leave plenty of space
at the bottom of the form for the subform you are about to design.
Don't worry about exact size of anything the first time through this
process. It's an iterative process so be patient with yourself.
Before we get into the subform I want to raise a couple of points.
1. The first is that you should adopt a naming convention and stay
with it. On days when you're bored and have nothing else productive
to do, go back through your applications and keep bringing them into
conformity with your chosen naming convention. As you get more used
to the convention, always do it correctly the first time. The one I
use is the Reddick Naming Convention. There is another, similar
convention; the Leszynski convention. When I first knew it, they were
a team. My favorite author, Ken Getz and his cohorts favor Reddick
and so do I. Now, as you may already know re-naming things after you
have already built forms, queries and reports is a risky and tedious
business. To help ease that pain there is Find And Replace, a
shareware utility for helping with that process. It's written by Rick
Fisher and is available at
www.rickworld.com The person you are doing
all these things for is the future YOU. Some day the code you are
working on now won't even be a memory but if you have used the naming
convention with any rigor at all you will eventually be immensely
grateful. That future you thanks you (or not!).
2. I will now assume that you will always name your objects
consistently so all reports will begin with "rpt" followed by the
name: rptReportName. Those names aren't terribly friendly to most
users. In what we are doing you don't have to show the user that
name. You can select your reports based on your description alone.
By selecting the description in a combobox you can then use the stored
ReportID to find the correct report name in the report field. User
friendly and effective...
Design a new form named, for purposes of this discussion,
sufCustomerReports. You can start a rough design based on
tblCustomerReport. Later you'll change the form's data source to a
query based on tblCustomerReport and embedded in the form. The query
will have tables tblCutomerReprt and tblReport. Output fields
tblCustomerReport.CustomerID and tblReport.ReportDescription. Also
sort ascending on ReportDescription. Do not show Header and Footer as
this form will show multiple times in a possibly scrollable subform
control on frmCustomerReports. Keep it down to one line not much
taller than the single control that will be on it. It shouldn't be
much more than 3 inches wide. Put a single combobox on it named
cboRepoortDescription. The row source for the combobox is a query on
tblReport that returns ReportID and ReportDescription. Ascending sort
ReprtDescription. The cboReportDescription has 2 fields. The field
widths are 0";2.5" adjust the 2.5" later to suit your taste. The
bound column is '0'. (The column count is 1 based but the column
naming is 0 based). Close this form.
Open frmCustomerReports and small size it so that the open space at
the bottom of the form is showing. Shift the form so that you can
find sufCustomerReports in the Database|Forms window. Click and drag
suf... into the bottom of frm... and release.
From here on in it's a matter of move, size, redesign, frm,suf, etc.
My day is totally turned around and my head is hitting the keyboard.
I may have been telling you to do it all backwards.
Anyway, I do hope it helps. Post back with your results. If you run
into troubles in the next few hours just post. Someone will probably
pick it up.
--
-Larry-
--
ttbbgg said:
Ok thanks, Larry. Can you just provide some guidance as to how the table
CustomerReport would be setup? I assume the fields in the customer table
would be Customer Name and the affiliate code(s). What relation would I
input? Should I just make an autonumber field and relate it that way?
TG