Using decisions in Access design

[Gil]

Hi all,

I want to set up an introductory page for an Access 2007 project. On this
page would be some instructions and links to different pages with the usual
form entries. Each of these pages would be different enough to warrant their
own page and fields, but there would be similarities between all pages that
need to keep them in the same database.

Example:

I have that introductory page with instructions and links to eight new pages.

Fields 1-3 on each of the eight pages would contain identical types of
information (Name, URL, and Password). These would have the same field
lengths defined.
Fields 4-10 on each page would contain different data and field lengths
would vary.

I would prefer to keep all of this in the same database if possible.

Thanks
Gil

 

[Jeff Boyce]

Gil

I'm having a bit of trouble visualizing what you are trying to do.

Your post mentions "pages", but I'm guessing you're referring to what folks
see on a screen, rather than on a printed page.

If you have common info (e.g., name, address, phone), then several different
views still connected to that common info, one approach would be to use the
common fields as controls on a form, plus a tab control, with each "page" of
the tab control containing a different view.

Good luck!

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[andrewmrichards]

Hi all,

I want to set up an introductory page for an Access 2007 project. On this
page would be some instructions and links to different pages with the usual
form entries. Each of these pages would be different enough to warrant their
own page and fields, but there would be similarities between all pages that
need to keep them in the same database.

Example:

I have that introductory page with instructions and links to eight new pages.

Fields 1-3 on each of the eight pages would contain identical types of
information (Name, URL, and Password). These would have the same field
lengths defined.
Fields 4-10 on each page would contain different data and field lengths
would vary.

I would prefer to keep all of this in the same database if possible.

Thanks
Gil

Hi Gil

In addition to Jeff's suggestion, the other way of achieving something
similar would be to have the information residing in different tables
to reflect the differing purposes, but relate them back to the table
containing the "shared" fields.

As an example, if the Name, URL and Password belong to an entity
called StaffMember, then create a table called tblStaffMembers (or
whatever) containin these fields plus the Primary Key (StaffID,
perhaps). Each "page" you refer to could then be a separate form
linked to separate related tables - perhaps tblPositionInformation,
tblProjectAssignments, tblContactInfo and so on. Each of these tables
will include the StaffID field to make the relationship work.

Rather than using a tab control with multiple tabs, which can
sometimes result in a significant number of controls which are bound
in turn to a significant number of tables, I'd consider using a main
form / child form arrangement with a series of buttons (or option
buttons). Each button or option button then runs code along the lines
of:

Me.ChildForm.SourceObject="fsubEmployeesPositionInfo"

where ChildForm is the name of the ChildForm control on the main form.
In this way, you've only ever got one main form and one subform in use
at any one time and the overhead may be minimised.

Does this make sense?

Andrew

 

[Jeff Boyce]

A potential problem with creating a collection of tables with comparable
fields, one for each "role", is that you need to add a new table for each
new role, plus modify queries, forms, reports, code, etc.

Much more maintainable approach would be to use a single table, with a field
for "role", and a "lookup table" for roles. ?Need a new role, add a new
record in the tlkpRole table!

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

Hi all,

I want to set up an introductory page for an Access 2007 project. On this
page would be some instructions and links to different pages with the
usual
form entries. Each of these pages would be different enough to warrant
their
own page and fields, but there would be similarities between all pages
that
need to keep them in the same database.

Example:

I have that introductory page with instructions and links to eight new
pages.

Fields 1-3 on each of the eight pages would contain identical types of
information (Name, URL, and Password). These would have the same field
lengths defined.
Fields 4-10 on each page would contain different data and field lengths
would vary.

I would prefer to keep all of this in the same database if possible.

Thanks
Gil

Hi Gil

In addition to Jeff's suggestion, the other way of achieving something
similar would be to have the information residing in different tables
to reflect the differing purposes, but relate them back to the table
containing the "shared" fields.

As an example, if the Name, URL and Password belong to an entity
called StaffMember, then create a table called tblStaffMembers (or
whatever) containin these fields plus the Primary Key (StaffID,
perhaps). Each "page" you refer to could then be a separate form
linked to separate related tables - perhaps tblPositionInformation,
tblProjectAssignments, tblContactInfo and so on. Each of these tables
will include the StaffID field to make the relationship work.

Rather than using a tab control with multiple tabs, which can
sometimes result in a significant number of controls which are bound
in turn to a significant number of tables, I'd consider using a main
form / child form arrangement with a series of buttons (or option
buttons). Each button or option button then runs code along the lines
of:

Me.ChildForm.SourceObject="fsubEmployeesPositionInfo"

where ChildForm is the name of the ChildForm control on the main form.
In this way, you've only ever got one main form and one subform in use
at any one time and the overhead may be minimised.

Does this make sense?

Andrew

 

[andrewmrichards]

A potential problem with creating a collection of tables with comparable
fields, one for each "role", is that you need to add a new table for each
new role, plus modify queries, forms, reports, code, etc.

Much more maintainable approach would be to use a single table, with a field
for "role", and a "lookup table" for roles.  ?Need a new role, add a new
record in the tlkpRole table!

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentionedin
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.








Hi Gil

In addition to Jeff's suggestion, the other way of achieving something
similar would be to have the information residing in different tables
to reflect the differing purposes, but relate them back to the table
containing the "shared" fields.

As an example, if the Name, URL and Password belong to an entity
called StaffMember, then create a table called tblStaffMembers (or
whatever) containin these fields plus the Primary Key (StaffID,
perhaps). Each "page" you refer to could then be a separate form
linked to separate related tables - perhaps tblPositionInformation,
tblProjectAssignments, tblContactInfo and so on. Each of these tables
will include the StaffID field to make the relationship work.

Rather than using a tab control with multiple tabs, which can
sometimes result in a significant number of controls which are bound
in turn to a significant number of tables, I'd consider using a main
form / child form arrangement with a series of buttons (or option
buttons). Each button or option button then runs code along the lines
of:

Me.ChildForm.SourceObject="fsubEmployeesPositionInfo"

where ChildForm is the name of the ChildForm control on the main form.
In this way, you've only ever got one main form and one subform in use
at any one time and the overhead may be minimised.

Does this make sense?

Andrew

Hmmm... I would agree, except that the original post said that "Fields
4-10 on each page would contain different data and field lengths
would vary. "

As I saw it (and this may be a misreading of the original post) we're
dealing with significantly different kinds of information (such as
contact info or job-related info) in each of the pages. That's why I
suggested that where there is shared info it should go into one common
table, but that the info that is different in structure, data types
and purpose should not be held in a single table...

Am I mis-reading this situation? I'm happy to bow to your greater
experience, but as I see it, once the structure is right, the way I'm
suggesting wouldn't need modifying for new roles, just for whole new
types of data (such as suddenly needing to store Commission payments
info, where it hadn't been needed previously, perhaps?) which surely
SHOULD require an extra table rather than putting it into one "multi-
purpose table"?

Best regards

Andrew