Subform - add records v2003

[ironwood9 via AccessMonster.com]

I have a form whose source is a table containing projects (tblProjects), and
several vendors do work on each project - I have a subform that lists the
vendors, which I want to be able to input information for, "on the fly,"
while the form is running. One key field to be input is insurance type for
the insurance liabilty forms needed for the projects. My users are manually
inputting the insurance type in the subform, but I want them to be able to
select from a list, without completely re-designing the form.

My design is basic, like:

tblProjects
=======
ProjectID (PK)
VendorID (FK)
InsTypeID (FK)
ProjectName

tblVendors
========
VendorID (PK)
VendorName

tblInsType
=========
InsTypeID (PK)
InsType

... would a combo box work, or would I need coding, and what would the syntax
be ?

Any help would be greatly appreciated

 

[John W. Vinson]

I have a form whose source is a table containing projects (tblProjects), and
several vendors do work on each project - I have a subform that lists the
vendors, which I want to be able to input information for, "on the fly,"
while the form is running. One key field to be input is insurance type for
the insurance liabilty forms needed for the projects. My users are manually
inputting the insurance type in the subform, but I want them to be able to
select from a list, without completely re-designing the form.

My design is basic, like:

tblProjects
=======
ProjectID (PK)
VendorID (FK)
InsTypeID (FK)
ProjectName

tblVendors
========
VendorID (PK)
VendorName

tblInsType
=========
InsTypeID (PK)
InsType

Your tables aren't quite correctly designed. If each Project can have several
vendors, you can't put a VendorID foreign key into the Projects table - it can
have only one value, so you're stuck with one vendor!

Consider having a ProjectVendors table with fields ProjectID and VendorID, and
remove VendorID from your tblProjects. You'll want a Form based on
tblProjects, with a Subform based on this new ProjectVendors table; the
Master/Child Link properties of the subform would be the ProjectID, and you
would put a combo box on the subform bound to VendorID and based on
tblVendors. To enter information about a new vendor, I'd use the combo box's
NotInList event to "pop up" a vendor information form; this will let you
select an existing vendor or add a new one as needed.

If each Project involves only one InsType, then by all means put a combo box
on the form; it should be based on tblInsType, be bound to InsTypeID, and
should display the InsType. The combo box wizard will do this for you.

John W. Vinson [MVP]

 

[ironwood9 via AccessMonster.com]

John,
Thanks !

I have a form whose source is a table containing projects (tblProjects), and
several vendors do work on each project - I have a subform that lists the

[quoted text clipped - 22 lines]

InsTypeID (PK)
InsType

Your tables aren't quite correctly designed. If each Project can have several
vendors, you can't put a VendorID foreign key into the Projects table - it can
have only one value, so you're stuck with one vendor!

Consider having a ProjectVendors table with fields ProjectID and VendorID, and
remove VendorID from your tblProjects. You'll want a Form based on
tblProjects, with a Subform based on this new ProjectVendors table; the
Master/Child Link properties of the subform would be the ProjectID, and you
would put a combo box on the subform bound to VendorID and based on
tblVendors. To enter information about a new vendor, I'd use the combo box's
NotInList event to "pop up" a vendor information form; this will let you
select an existing vendor or add a new one as needed.

If each Project involves only one InsType, then by all means put a combo box
on the form; it should be based on tblInsType, be bound to InsTypeID, and
should display the InsType. The combo box wizard will do this for you.

John W. Vinson [MVP]