Populate 2nd Form w/Data from First

[Janna]

I have two forms: frmDtaEntryMain and frmDtaEntrySE.

FrmDataEntryMain has underlying table: tblDta.
FrmDtaEntrySE has underlying table: tblSE


There is a common field in both forms: ProjectID, which is a text field.

When in frmDtaEntry, I have a button to open frmDtaEntrySE. If a record
with the same ProjectID exists in tblSE, I would like that record displayed
when frmDtaEntrySE opens.

If a record currently does not exist in tblSE with the same ProjectID, I
would like to populate the ProjectID field in frmDtaEntrySE with the value
from the ProjectID field in frmDtaEntryMain

Thanks much for any “Best Practices†recommendations to accomplish the above.

 

[Al Campagna]

Janna,
Your missing the point of a table to table realtionship.

ProjectID should be a unique identifier for each record in tblData.
Usually that's an autonumber, to ensure (within reason) that no Data records
have the same ProjectID.
tblEntry should have a Long Integer numeric field called ProjectID.
In the Realtionship window, you can create a relationship (One to Many)
between tblData (the One) to tblEntry (the Many).

OK, our tables are ready. Now the form...
The Main form will have tblData as it's RecordSource, and show all the
Data fields.
A subform on that Main form will use tblEntry as it's RecordSource, and
show the Entry fields.
The Main form and subform will have a Parent/Child relationship through
the ProjectID, and Access will ensure that any subform (Entry records) will
always be properly associated with it's Parent Data record.

Let's say you create a new Data record with ProjectID 123. If you have a
proper relationship established between tblData, and tblEntry... any subform
record entered will automatically be given a ProjectID of 123. If you enter
10 subform records, each will have a ProjectID of 123... so the sub records
are alwways properly associated with your Data records.
Access does that for you... because of the relationship.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Janna]

The tables would just have a one-to-one relationship--which is why I didn't
initially consider using a subform. There should just be one record in both
tables with a unique ProjectID. I'm trying to keep the number of fields in my
tables manageable. tblData is really the main table that a large number of
fields with demographic data in addition to the ProjectID field

tblSE represents a specific type of project with it's unique fields. There
are additional tables (similar to tblSE) that would contain data regarding
specific types of projects and their unique fields, but each table would have
a ProjectID field to tie back to tblData. Again, only one record in each of
these tables should ever have the same ProjectID.

I suppose I could create one huge flat table, but it would have several
hundred fields and I was trying to avoid that.

Any additional recommendations on design? Thanks again.

 

[Janna]

I'm re-reading my 3rd post and thought I would try to clarify further: tblDta
will have one record for every project.

tbleSE and the other tables that have data specific to different types of
projects will combined, have only one record for every project id. Depending
on the type of project will determine within which table the entry resides.
As mentioned above, I could make a huge flat table, but there will be several
hundred fields and many of the fields in each record entry won't contain data
because it will be specific to the type of project. I thought it would be
more manageable to create separate tables based on the type of projects and
tie them back to the tbleDta based on the unique project id.

 

[Al Campagna]

Janna,
That's quite different, but still should be do-able.
I think that it's a bit odd that you have so many fields in your tables,
but it's certainly not illegal. A One to One relationship can be used. In
fact I have had occaision to do the same.

Make sure you have a relationship between tblProjectID and tblData using
the ProjectID.

In the query behind your form, use both related tables, and then, just
lay out the fields on the form... just as if tblProjectID and tblData were
all fields in one table. No subform needed...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Janna]

That was exactly what I was looking for! Thanks so much for taking the time
to read through my "epic" explanations and responding.