POPULATING FIELDS FROM ONE FORM TO OTHERS

[Jan S via AccessMonster.com]

I am creating a tooling quote db. I want to have one table(form) with
specific project information which will be update for each new project. I
have multiple vendors but will not have all vendors quote for all projects.
(2 for one project, 3 for another, etc.) I am trying to design the tables
and forms so that the user viewing a particular Tooling Quote Project can
click on any one of the vendor buttons and only the current Tooling Project
information populate the top of the Vendor Tooling Quote form.

Thank you for your help.

 

[John Vinson]

I am creating a tooling quote db. I want to have one table(form)

MAJOR warning here.

A Table is NOT a Form.
A Form is NOT a Table.

THEY ARE DIFFERENT; data is stored in Tables (and only in tables); a
form is just a tool, a window which lets you view and edit the data in
Tables.

with specific project information which will be update for each new project. I
have multiple vendors but will not have all vendors quote for all projects.
(2 for one project, 3 for another, etc.) I am trying to design the tables
and forms so that the user viewing a particular Tooling Quote Project can
click on any one of the vendor buttons and only the current Tooling Project
information populate the top of the Vendor Tooling Quote form.

Get your table structures correct first (it appears that they are not)
before you start worrying about forms!

If each Project can have several Vendors, and each Vendor might sell
to multiple projects, you need at least three tables:

Projects
ProjectID
<information about the project as an entity>
<NOTHING in this table about vendors>

Vendors
VendorID
<information about the vendor, e.g. name, contact info>

ProjectVendors
ProjectID <link to Projects>
VendorID <link to Vendors>
<any information about THIS vendor on THIS project>

Rather than having "buttons" (command buttons? one for each vendor!!?)
you should consider a Subform based on ProjectVendors, with one row
per vendor. You can pick the vendors from a listbox or combo box. If
you want to go to a LOT of rather complex extra work, you can
dynamically create Command Button controls on a form, but I'd really
suggest that it's not necessary!

John W. Vinson[MVP]

 

[Jan S via AccessMonster.com]

MAJOR warning here.

A Table is NOT a Form.
A Form is NOT a Table.

THEY ARE DIFFERENT; data is stored in Tables (and only in tables); a
form is just a tool, a window which lets you view and edit the data in
Tables.


Get your table structures correct first (it appears that they are not)
before you start worrying about forms!

If each Project can have several Vendors, and each Vendor might sell
to multiple projects, you need at least three tables:

Projects
ProjectID
<information about the project as an entity>
<NOTHING in this table about vendors>

Vendors
VendorID
<information about the vendor, e.g. name, contact info>

ProjectVendors
ProjectID <link to Projects>
VendorID <link to Vendors>
<any information about THIS vendor on THIS project>

Rather than having "buttons" (command buttons? one for each vendor!!?)
you should consider a Subform based on ProjectVendors, with one row
per vendor. You can pick the vendors from a listbox or combo box. If
you want to go to a LOT of rather complex extra work, you can
dynamically create Command Button controls on a form, but I'd really
suggest that it's not necessary!

John W. Vinson[MVP]

John - thank you for your help. The db I am working with was created by
someone else who is not that familar with Access. I was attempting to work
with what he had. He created tables for each vendor with duplicated fields
for all of the information in the "Project" table. I just needed assistance
in direction which is what you gave me. After receiving your reply I
restructed his tables and created the subform as you advised. I am now just
awaiting his approval.

 

[John Vinson]

John - thank you for your help. The db I am working with was created by
someone else who is not that familar with Access. I was attempting to work
with what he had. He created tables for each vendor with duplicated fields
for all of the information in the "Project" table. I just needed assistance
in direction which is what you gave me. After receiving your reply I
restructed his tables and created the subform as you advised. I am now just
awaiting his approval.

Glad to have steered you into a better direction - good luck!

John W. Vinson[MVP]