Link Two Tables with AutoNumber fields to One Form

[Guest]

Hi,

I'm really stumped on this one:

Right now I have a form that is linked to my Registration table. I want it
to also be linked to my ID table, because I want to be able to update both
tables using one form. I heard of creating a sub-form. Would this solve the
trick?

Right now, when I create a new record in the form, the AutoID of the
Registration table increases sequentially. I want to be able to also have
the ID table increase its AutoID and create a new record in that table too.
That way I can use that new ID table value to create a value that will
eventually be stored in the Registration table. How can this be done?
Thanks, ruben.

 

[Jeff Boyce]

Ruben

Access Autonumbers are intended to be used as unique row identifiers. They
are not guaranteed to be sequential (they can and will have gaps), and they
are generally unfit for human consumption.

Moreover, Autonumbers in Table1 are TOTALLY INDEPENDENT of Autonumbers in
Table2. Yes, they are both Autonumbers. No, AutonumberID=17 in Table1 is
most definitely not related (except by luck or chance) to AutonumberID=17 in
Table2. Do NOT join the two tables on their Autonumber fields.

What is the underlying data you are working with? What is the relationship
between the data categories?

It sounds like you have "ID"s and "Registrations". What are IDs? Persons?

If Persons are Registering, I'll guess they are registering for something.
What?

I may be reading too much into your description, but in my world, there are
People, there are (say) Classes, and there are Registrations. People
Register for Classes. That implies (at least) three tables in a relational
database like Access.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Jose Ruben Gonzalez-Baird"

 

[Guest]

Jeff,

I'm not necessarily needing the autonumbers to be sequential. I just meant
that that was the typical order of the numbers. on occasion record(s) are
deleted, leaving gaps in the sequence.

In my case, I need to be able to create a new record in my form that creates
one record in each of two tables. I don't much care if the autonumber in one
table is 17 and the autonumber in the other table is 34532, but I will be
using the autonumber in the second table to create a value in the first
table.

The way our registration works for sumps is they undergo a registration
process before they are actually built. So a user will create a new record
for each new registered sump. But we also have a list of existing sumps that
have already been built. we need to keep the two tables separated somewhat
(existing sumps information can't be in with the registration sumps) but we
want to create a new record in the existing sump table for each new
registration sump.

This would go something like this:
1)A new sump is registered
2)The new sump gets a autonumber ID in the registration table
3)the new sump also gets an autonumber well# that is stored in the existing
sump table.
4)The well# ID from the existing table then also gets entered into the new
registration table
Example:
new registration ID = 46
new well#ID (in existing sump table) = 34567
new wellDEQID(in registration table) = 10120-34567 {city code concatenated
w/ well#).

Can you see how I have to create two new records in each table and tie them
into the new registration table using a single form interface?

Thanks so much for your reply. Jose Ruben.

 

[Guest]

please note that for my previous post I stated that i create two new records
in each table. This should be clarified by saying that a total of two new
records are created--one in each of two tables. thanks.

 

[Jeff Boyce]

Let's talk about how you can use Access to help you keep track in your
situation...

You mention needing to create records in separate tables for a
newly-registered sump vs. an existing sump. Why? Why do you believe you
need to keep the data in different tables? Are the tables roughly identical
in structure? If so, you are making your job harder and making Access work
harder than either of you need to!

You mention creating a record in one table, and a related record in another
table. Is that the "registered" and "existing" situation again?

You mention an "autonumber Well#". If I recall correctly, you can only use
one Autonumber field per table.

You have sumps. Some are new, some are existing.

You have ?wells (I clearly don't understand this part of your situation!).

Sumps are (?) related to wells.

Are these reasonable paraphrasings of your situation?

Although you posted in the .forms newsgroup, you are asking the right
questions -- you have to get your underlying data correct before you can use
it.

What is the relationship between new (?registered) and existing sumps?

What is the relationship between sumps (only "existing"?) and wells?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Let's talk about how you can use Access to help you keep track in your
situation...

You mention needing to create records in separate tables for a
newly-registered sump vs. an existing sump. Why? Why do you believe you
need to keep the data in different tables? Are the tables roughly identical
in structure? If so, you are making your job harder and making Access work
harder than either of you need to!

I considered this point as well. It would be nice if I could just keep all
of the data in one place. However, there are two things that are preventing
me from doing that: 1) I inherited the data structure of these tables when I
assumed this position and 2) the table structures are just too different too
incorporate all registration-type data into the existing wells/sumps (for
reference, wells and sumps should be considered the same thing). The
"existing" table is meant to be a simple lookup table with few columns. The
registration table is much more detailed because newly registered sumps need
to meet specific design criteria in order to be built. So for now the the
need is to keep the data separate.

You mention creating a record in one table, and a related record in another
table. Is that the "registered" and "existing" situation again?

Yes, one new record in the form creates two "actual" records--one record in
each of two tables.

You mention an "autonumber Well#". If I recall correctly, you can only use
one Autonumber field per table.

in the "existing" table, the autonumber Well# is analagous to the autonumber
sump# in the registration table. there are two tables, each should be
receiving one new autonumber per record entered into the form.

You have sumps. Some are new, some are existing.
You have ?wells (I clearly don't understand this part of your situation!).
Sumps are (?) related to wells.
Are these reasonable paraphrasings of your situation?

yes. i have sumps/wells (they are the same thing, only called by a
different name in each table).

Although you posted in the .forms newsgroup, you are asking the right
questions -- you have to get your underlying data correct before you can use
it.
Thanks

What is the relationship between new (?registered) and existing sumps?
What is the relationship between sumps (only "existing"?) and wells?

the new "registered" sumps are created to ensure that newly constructed
facilities meet design criteria. Once they a registered, they should also be
added to the "existing" table because for our purposes they exist from this
point forward. The "existing" sumps/wells have already been registered prior
to the enactment of our registration process. thus, there is no need to
re-register them because they have already been constructed. because we
consider a new "registered" sump to be "existing", we need to create a record
for it in each table. So once it is added to each table, and new autonumber
is generated which most certainly will not be the same, since one table had
about 30 records and the other table has nearly 9000 records. that doesn't
matter. all that matters is that i can get a new record in the form to
populate new records in both tables--that way I can return the autonumberID
in order to assign it the "official" ID that will be used over the facilities
lifespan. I know it's rather convoluted, but rather than restructure the
whole system (and nix quite a bit of work that's been put into this effort
thus far) I thought this to be the best approach. I was able to creat a
subform in my main form window that displayed the autonumberID from the
"existing" table, but I was able to actually get the autonumber field to
populate with the next ID number in the sequence. It remains as
"(AutoNumber)" in the field before the field is populated with the next ID
number.

Thanks very much for your guidance. Ruben

 

[Jeff Boyce]

This may seem somewhat backwards, but if you have 9000 existing records (I
assume in your existing/"well" table) and an AutonumberID field there, why
not start there?

What I am asking/suggesting is that, since every entry in registered will
have a entry in existing, why not start in existing, get a new AutonumberID,
then put that number (as a foreign key value) in the registration table?

If all sumps/wells have some elements in common, and the "new" sumps have
some additional data elements, store the common data in the "existing"
table, adding only what you need to for the "new" record.

One way to do this would be to add a RegistrationID field (go ahead, use an
Autonumber here) and a WellID field (long int, foreign key, pointing back to
the "Existing" table) to your "new" table. Then create a main form based on
the "Existing" data (via a query), and create a form based on the "New" data
(again, via a query). Embed the second form as a subform within the main
form. Relate these two on the WellID field in both for the Parent and Child
properties of the subform control (use design mode on the main form to do
this).

Is this any closer?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Jose Ruben Gonzalez-Baird"