Creating Sub-forms

[Zoltar54]

I am trying to creat a databse by which I can track the projects my company
is doing (mainly to creat estinates and invoices).

Currently I have a main form (frmProject) which is based on a table
(tblprojects). This form will allow me to enter the file number (primary
key), date, customer name and details and other pertinent data. What I need
to do now is creat a sub-form. I need this form to allow me to select the
various services we provide from and underlying table (tblservices) and
"attach" those to the file number. For example, customer XYZ has request a
quote for excavation, drilling, site survey, etc. The tblServices contains
all of the essetial information related to each task such as cost and
estimated time. Thus, once I have entered the customer info into the main
form i would like to have the subform allow me to enter the various services
requested.

I have tried to use thr wizard and creat a quesry but cannot get this to
work. I should mention that there is no relationship between tblprjects and
tblservices.

Any help would be greatly appreciated.

 

[BruceM]

Are you entering the customer name into tblProject (via tblForm)? If so,
you may want to rethink your design. A table should contain information
about a single entity such as Customer. I expect a customer can be
associated with several projects, so there should be a Customer table and a
Project table, with a one-to-many relationship between Customer and Project
(one customer, many projects). Services such as you wish to put into the
subform also go into a related table for ProjectDetails or some such. It
would contain information such as the Service, price, etc. There is a
one-to-many relationship between Project and ProjectDetails (one project,
many details).

Once the database's structure is established you can create a form bound to
the ProjectDetails table. Use the toolbox to create a subform control on
the main form; add the ProjectDetails form to the subform control. You can
use the wizard to help guide you through the process.

To put the customer information onto the form, create a combo box on the
main form, with its Row Source the Customer table. Store just the primary
key from the Customer table in the Projects table. You can use a subform to
display the rest of the customer information, or you can base the main form
on a query that includes the Customer table. Once you select the Customer,
text boxes can display the rest of the Customer information.

The general table structure may look something like:

tblProject
ProjectID (primary key, or PK)
CustomerID (foreign key, or FK, to tblCustomer)
Location, etc.

tblCustomer
CustomerID (PK)
CustomerName, etc.

tblProjectDetails
DetailID (PK)
ProjectID (FK)
Description, rate, etc.

 

[kingston via AccessMonster.com]

It sounds like you need another table to store data on customers and services
together. So on your form, you could have a subform listing various services
and checkboxes next to each service. Whenever a box is checked, a record is
made for that customer (from the main form) and that service (on the subform).
In other words, the table will only store CustomerIDs and ServiceIDs that
have been requested. Create a query to join this table and the services
table (left or right join so that all records in the services table are
output). Base your subform on this query and use CustomerID as the
master/child field. hth

 

[Zoltar54]

Thanks very much, this worked great. I assume that the "details" table is
what is know as a junction table, thus creating a many-to-tomany relationship
between projects and services?

Thanks again for your help!.

Z

 

[BruceM]

It is not a junction table in the scenario I outlined, but it could be (and
probably should be) in that each project has many details, and each detail
is associated with many projects. My explanation was somewhat simplified
from a typical situation in which there is a Services table, with a
description, price, and so forth. The junction table might be named
ProjectServices (rather than Details). The structure would look something
like this:

tblProject
ProjectID (primary key, or PK)
CustomerID (foreign key, or FK, to tblCustomer)
Location, etc.

tblCustomer
CustomerID (PK)
CustomerName, etc.

tblService
ServiceID
Description
Rate

tblProjectService
ProjServID (PK)
ProjectID (FK)
ServiceID (FK)
Rate (from tblServices)
ServiceDate

My naming convention in what follows is that a form based on tblProject is
frmProject, etc.

frmProject has a subform frmProjectService. frmProjectService includes a
combo box (cboService) that uses tblService as its row source. That row
source includes ServiceID, Description, and Rate. The combo box bound
column is 1, its column count 3, and its column widths 0";1.5";0" (the 1.5"
can be whatever you need). The combo box is bound to ServiceID in
tblProjectService. The user will see a listing of Services in the combo box
(the visible column), but since the bound column is the first column,
ServiceID will be stored. On the subform, add a text box bound to Rate
(from tblProjectService). In the After Update event for the combo box:
Me.Rate = Me.cboService.Column(2)
Note that the columns are numbered from 0, so Column(2) is actually the
third column (even though it is Column 3 in the combo box properties). It
is a bit confusing at first.
The reason for storing Rate is that it could change over time, in which case
you modify the record in tblServices. You probably want to store (in
tblProjectServices) the rate that is in effect at the time, not the current
rate.
You can add other fields to tblService as needed (maybe crew size or total
hours or something). You probably do not want to store that information it
tblProjectServices, but that would depend on your needs.