Automating copy of value in Form 1 to Forms 2 & 3

[Guest]

Hi Community

I wanted to enter a value in a form "Form1" in a field "name1" and have that
same name (Example "Jane") to be copied to form "Form2" field "name2" and
form "Form3" field "name3". Each of the forms are created from individual
tables using Access 2003 on a single home computer. All forms are in the same
database. I am happy if I need to add a macro button etc. as a step after the
value is entered etc. Your advice is appreciated.

Thank you - Tom D

 

[Jeff Boyce]

Why? As in "why are you storing the same value in multiple tables?" At
least, I assume that's the case, since you are trying to copy the value into
multiple forms, and in Access, forms are based on tables.

I ask because you are clearly trying to do something (which you haven't yet
described) in this way when there could be an easier way to get your job
done...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Hi Jeff,

Thanks for your quick response.

I am storing all the names that I have in the first form in the other two
forms because they have different information in those forms and I have
queries and reports developed and that I am developing that seem easier to
work with with the names placed in each table and form, including ability to
see the names as I review or edit the tables or forms. Based on my work on
the database so far, it appears that the ID (autonumber) field and name field
values need to be the same in each form and table. All the tables in the
database are connected in Relationships using the ID field as the connection.

Is there a way for me to see the names from the first form in the other
forms and tables without entering them into the respective forms and tables?

I appreciate hearing a different approach or if there is a way to copy an
existing value to other forms and tables.

 

[Jeff Boyce]

Tom

Access tables store data, Access forms (and reports) display data, ... and
queries return sets of data from tables.

It sounds like you started with the reports and forms and worked backwards
toward tables. In a relational database (MS Access, for example), you
really need to start with the data (i.e., tables) and move forward. There
is absolutely no requirement that how the data is stored (in tables) bear
any resemblance to how you add/edit/display it in forms and reports.

If you work backwards, you will find you've painted yourself into a corner
and cannot make the best use of the features and functions that Access
offers.

Step back for a moment and consider your underlying data. What are the
"things" about which you need to capture data? You've already indicated
that Person information is important, so you'd need a table to keep
Person-related information (e.g., firstname, lastname, DOB, ...).

What are some of the other "things" about which you need to know? And how
are they all related to each other? This process, figuring out your
"entities" (things), their "attributes" (info about them), and their
relationships, is really the first step in building a well-designed Access
application.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Greetings Jeff,

I appreciate your good points. I will be sharing this database with others
who know little about the workings of databases, granted that I am likely
novice to intermediate. Each form is related to tables that hold unique
information excepting the ID and name field values which I think should stay
constant throughout. Table1 for Form1, Table2 for Form2, and Table3 for
Form3. Those that I am sharing the database with prefer to see the names
associated with their respective information on each table and form that they
view. Sometimes they want to edit values (other than the name and ID) in the
tables directly.

I did develop the tables first and then used Autoform to create forms bound
to those tables/fields. I wanted to be able to add new names in Form1 once,
then and have these names and ID values appear on the other two forms, either
automatically based on a macro in the "On Exit" property (or other property)
or by clicking a button that initiates a macro, or some other method that
would assure the same ID value and name value appear in the other forms.

I realize that I could create an Autoform based on a query combining Table1
and Table2 and replace the existing Form2 with the new Form2 and use the
"name1" field as the visible field. The main downside is that the value for
"name2" which should be the same as for "name1" will not be copied into
Table2. The other downside is that it would require completely reformatting
the forms, with the drop down value menus etc. I am willing to work, but
always appreciate good counsel.

 

[Jeff Boyce]

Tom

Once you (your database) has recorded a name (e.g., name1) in a table, you
(and your Access database) have no further need to re-store name1, either in
the same table or in any other tables. This is a blanket statement and will
undoubtedly receive valid counter-examples, but ... and I say this in all
respect ... your situation is not one of those.

Including copies of name1 in multiple tables just so it can show in multiple
forms is treating Access as if it were a spreadsheet. You will not get very
good use of Access' features and functions if you feed it 'sheet data.

Being able to share your database/application with others is important.
Preventing them from hurting themselves or the data is MORE important!

First emphatic point! ... Don't let your users use the tables directly.
Tables may look like spreadsheets, but they are not. You have little to no
control over what folks, in their infinite ... creativity ... can do if
allowed to muck-about in the tables. Tables store data, that's it!

Use forms to provide your users a way to add/edit/view data. Access forms
have a very rich Event environment, and you can exert considerable control
over user actions this way.

In your situation, a single table with Person information (e.g., PersonID,
FirstName, LastName) could serve as the source of all person-information
needed in any number of forms.

Instead of basing your forms directly on tables, first create queries that
pull together the information you wish to display. This gives you a way, in
forms based on these queries, to display person info (e.g., name info)
without actually having to store that info in the second (or third, or
fourth, or ...) table.

Which gets to another point. If you are using multiple forms to show person
(and other) information, why?

Again, it is likely that HOW you've chosen to try to do something is causing
the limitations you're encountering. If you provide more information about
WHAT you want to accomplish, in the end, the 'group may be able to offer
alternate approaches that actually make for less work.

I know you've ask what you probably consider a simple "how do I" question.
If you really only want to just press on and not consider these ideas,
please re-post your question to the newsgroup and explain a bit more why
you've done this. Otherwise, you are unlikely to get many "new" eyes on
this thread because it is as long as it is.

Regards, and Good luck!

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff, Your time and expertise was helpful and appreciated!
--
Thanks for sharing - Tom D

Tom

Once you (your database) has recorded a name (e.g., name1) in a table, you
(and your Access database) have no further need to re-store name1, either in
the same table or in any other tables. This is a blanket statement and will
undoubtedly receive valid counter-examples, but ... and I say this in all
respect ... your situation is not one of those.

Including copies of name1 in multiple tables just so it can show in multiple
forms is treating Access as if it were a spreadsheet. You will not get very
good use of Access' features and functions if you feed it 'sheet data.

Being able to share your database/application with others is important.
Preventing them from hurting themselves or the data is MORE important!

First emphatic point! ... Don't let your users use the tables directly.
Tables may look like spreadsheets, but they are not. You have little to no
control over what folks, in their infinite ... creativity ... can do if
allowed to muck-about in the tables. Tables store data, that's it!

Use forms to provide your users a way to add/edit/view data. Access forms
have a very rich Event environment, and you can exert considerable control
over user actions this way.

In your situation, a single table with Person information (e.g., PersonID,
FirstName, LastName) could serve as the source of all person-information
needed in any number of forms.

Instead of basing your forms directly on tables, first create queries that
pull together the information you wish to display. This gives you a way, in
forms based on these queries, to display person info (e.g., name info)
without actually having to store that info in the second (or third, or
fourth, or ...) table.

Which gets to another point. If you are using multiple forms to show person
(and other) information, why?

Again, it is likely that HOW you've chosen to try to do something is causing
the limitations you're encountering. If you provide more information about
WHAT you want to accomplish, in the end, the 'group may be able to offer
alternate approaches that actually make for less work.

I know you've ask what you probably consider a simple "how do I" question.
If you really only want to just press on and not consider these ideas,
please re-post your question to the newsgroup and explain a bit more why
you've done this. Otherwise, you are unlikely to get many "new" eyes on
this thread because it is as long as it is.

Regards, and Good luck!

Jeff Boyce
Microsoft Office/Access MVP