How to get form to populate

  • Thread starter Thread starter Tod
  • Start date Start date
T

Tod

I'm soo glad there's a GettingStarted NG for Access,
because this is most certainly a 'getting started' kind of
question.

Okay, I created a new database. It's more or less an
inventory and maintainence kind of database. I created two
tables. One contains attributes about the items in
inventory (Date Purchased, Brand, Model, etc.) The other
table contains info about maintainence on these items
(Date Serviced, Cost of Service, etc.) I created a
relationship that joins the two tables by the Product_ID,
which is also the primary key. Then I used the form wizard
to create a form for displaying this info.

NOW. Not to bad for a new guy. But what I want to do next
is have a way for the user to 'call up' the product in the
form, see the related data, and then enter updated
maintainence info. I can probably figure the rest out if
someone will just tell me how to get the form to show the
Product_Name. Better yet, how to select from a list.

tod
 
sounds like you have the right idea on these two tables. to clarify one
point: Product_ID as the primary key of the products table sounds correct,
but it should be the *foreign* key of the product maintenance table - not
its' primary key. example as follows:

tblProducts (parent table; "one" side of one-to-many relationship)
ProductID (primary key)
DatePurchased
Brand
Model
etc

tblProductMaintenance (child table; "many" side of one-to-many relationship)
MaintID (primary key, probably autonumber)
ProductID (foreign key from tblProducts)
DateServiced
CostOfService
etc

suggest you
1) create a form for tblProducts, with DefaultView set to Single Form.
2) add a combo box (to the form header section) using the Control Wizard,
and choose the option "Find a record on my form based on the value I
selected in my combo box". the wizard will walk you thru the setup.
3) create a form for tblProductMaintenance. you may want to set the
DefaultView as Continuous Forms or as Datasheet.
4) back in frmProducts design view, add a subform control to the form. set
the control's SourceObject property to frmProductMaintenance. set the
LinkChildFields property to the ProductID field from frmProductMaintenance.
set the LinkMasterFields property to the ProductID field from frmProducts.
5) you'll have to substitute your actual form and field names for those
given above, of course.

now you can open frmProducts, find a record from the combobox or enter a new
record, then go to the subform and enter maintenance records for that
product record.

hth
 
Back
Top