Hi, Kevin.
I want to be able to produce a form that will display the same info
but allow me to enter actual sizes and inspection date and store them in a
4th table.
If you want RAD (Rapid Application Development), which is what Access is
designed for, then I'd suggest the following:
(BTW, if you haven't already created a lot of queries, forms and records
that rely on the table design you currently have, then I'd recommend changing
names of your identifiers -- tables, queries, fields, procedures, et cetera
-- to avoid a buggy application. Remove spaces from field names (they
should use alphanumeric and underscore characters only), avoid the default
assigned names for objects by using descriptive names, and avoid Reserved
Words, such as Description -- and Desc, in case you wanted to abbreviate it.)
1.) Alter Table 3 by adding an AutoNumber field for the primary key. Name
this new field PDID. If the product/dimension/size combination is not to be
duplicated among these records, then place a unique index on these three
columns (if you haven't already). This alteration shouldn't interfere with
the form you've already created.
2.) Alter Table 4 by removing the Product ID and Dimension ID fields and
replacing them with the PDID field. If you don't have any queries built yet
that depend on this table, then you can make the following changes:
(There's a reason for these changes, as you'll see when the forms are
created by the Form Wizard.)
A. I'm assuming Record No is your primary key. (If not, you may have
problems.) Change the Record No field name to RecNo and make the Caption
Property for this field Record No.
B. Change the PDID field's Caption Property to ProdDimensionID (or
something you can easily read and identify).
C. Change the Inspection Date field name to InspecDate and change the
Caption Property for this field to Inspection Date.
D. Change the Actual Size field name to ActualSize and make the Caption
Property for this field Actual Size.
3.) Assign Relationships.
A. Open the Relationships diagram window and add the four tables. (I
hope you have more descriptive names for them, such as tblProducts,
tblDimensions, tblProdDimensions, and tblProdInspections.)
B. Connect Product ID in Table 1 to the Product ID in Table 3. Enforce
Referential Integrity.
C. Connect Dimension ID in Table 2 to the Dimension ID in Table 3.
Enforce Referential Integrity.
D. Connect PDID in Table 3 to the PDID in Table 4. Enforce Referential
Integrity.
E. Save these Relationships.
4.) Create a new query.
A. Add Tables 1, 2 and 3 to the upper pane of the QBE grid.
1. Add the PDID, Product ID, Dimension ID and Size field from Table 3.
(Yes. Of course it matters which table the field comes from in the query
grid. You want these fields to be updateable.)
2. Add the Description field from Table 1.
3. Add the Description field from Table 2.
4. Arrange the fields in the order you'd like to see them displayed
when the Form Wizard creates the form for you later.
B. Save the query and name it qryProdDimensionsWDesc.
5.) Create another new query.
A. Add Table 4 to the upper pane of the QBE grid.
1. Add all fields.
B. Save the query and name it qryProdInspections.
6.) Create a new subform.
A. Use qryProdInspections as the source where the data comes from.
B. Add all fields, make it a Datasheet, and name the new form
sfmProdInspect.
C. Open the sfmProdInspect form in Form View and right-click on the Title
Bar and select Unhide Columns... from the pop-up menu.
D. Uncheck the RecNo and PDID check boxes and select the "Close" button.
E. Save the subform and close it.
7.) Create a new form.
A. Use qryProdDimensionsWDesc as the source where the data comes from.
B. Add all fields, except Product ID and Dimension ID (unless you want to
display them), accept the default Single Form View, and name the new form
frmProdDimsWDesc.
C. Make sure the Control Wizards button on the Toolbox toolbar is
actiive. (This button looks like a magic wand dropping three, . . . uh,
rocks over three dots (elipses).)
D. Open the frmProdDimsWDesc form in Design View and add a subform
control. Use the sfmProdInspect subform, and name the control
subProdInspect. (You name the control differently from the name of the
subform to prevent bugs -- so that you never accidently write code for this
control as if it were a form.)
E. Open the Properties dialog window and select the label for the subform
control. Change the Caption Property to Inspections.
F. Save the form and close it.
With this form, you'll be able to add, review, and edit inspections, as well
as add new records for products and their dimensions. With referential
integrity enforced, you won't be able to create inspection records for
products or dimensions that don't already exist.
HTH.
Gunny
See
http://www.QBuilt.com for all your database needs.
See
http://www.Access.QBuilt.com for Microsoft Access tips.
(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.
