Add new form data to 2 tables

[msam137]

I have seen different ways this question was answered but it seems to
be a different answer based on the way the databases are set up.

I have 2 databases 1 with a list of names and id numbers,the other
database with information about the names.

In tbl1 the names is the primary key and the id number I am using auto-
number
In tbl2 we can say there is information about each name but there can
be multiple entry for each name as of right now no primary key for
this table.

The relation ship is bound by the name.

I have a form which record source is tbl2 which will allow user to
input data for a name, the problem is I can only save the record if
they are putting in information for a name that already exist in tbl1.
I need to know how to add new names with information and get them an
incremented id number.

 

[Erez Mor]

hi
i hope you mean you have 2 tables in the same database (not 2 databases)
first, there shouldnt be a table without a primary key (and it will almost
always come in handy after all) - so add an auto-number field to tbl2
second, consider using the id number (auto number) from tbl1 in tbl2, - not
the name, this is bad practice to use names
but to make it short, and to solve youre problem without too many changes,
you need to add some code to the After_Update event of the name control in
the form for tbl2 (i'll call it "txtName"), and check if this is a new name
then add it to tbl1 before this record is saved

it will look something like this:
If Dcount("[id field name]","tbl1","PersonName='" & txtName & "'")=0 then
docmd.RunSQL "Insert Into Tbl1 (PersonName) Values ('" & txtName & "');"

hope you can understand it all
good luck
Erez

 

[Larry Linson]

I am a little confused. You first state that you have two _databases_, but
everything else in your post talks about two _Tables_. (Unlike, say
dBase)dd, an Access Table (and other Access objects) reside in a Database...
the table itself is not a "database".

If you mean that the two tables are joined on the name of a _person_, that's
probably not a good idea, because even uncommon names are duplicated many
times in samples of the population. If you mean, the name of some object or
thing which you know will not be duplicated in your DB, that would be fine.

If you are not using the Autonumber as a key, what are you using it for?
The AutoNumber would be a good choice as a "surrogate key" in many
instances, but unnecessary in what I understand your enviroment to be.

Yes, if the Names table is the "one" side of a one-to-many Relationship (as
I understand you to describe) and you have specified "referential integrity"
on that Relationship, then your description is exactly how that is supposed
to work. It prevents "orphan" related Records from Accidentally being
entered. Using a Form (for the main table "Names") and a Subform (for the
related table "Tbl2"), you should be able to enter the data and have access
automatically set the "foreign key" that points to the appropriate "Tbl1"
key.

In the long run, it's going to be easier for you, almost certainly, if you
define an Autonumber as the primary key for the related "Tbl2".

I'd suggest a good "starter" book, something like, "Microsoft Access Step by
Step0" to begin to get nomenclature sorted. I don't understand your quesion
about adding names with information and not having a PK on the
information -- that's how Autonumber works... it is an "automatic increment"
(except you can't rely on it being monotonically increasing -- although it
ususally is quite close to that, numbers can be skipped, so it isn't
"strictly increasing by one" each and every time.

If we only knew what you are trying to accomplish, not just technical
details of how you thought you ought to accomplish it, it would be a lot
more likely thatdf

Larry Linson
Microsoft Office Access MVP

 

[msam137]

hi
i hope you mean you have 2 tables in the same database (not 2 databases)
first, there shouldnt be a table without a primary key (and it will almost
always come in handy after all) - so add an auto-number field to tbl2
second, consider using the id number (auto number) from tbl1 in tbl2, - not
the name, this is bad practice to use names
but to make it short, and to solve youre problem without too many changes,
you need to add some code to the After_Update event of the name control in
the form for tbl2 (i'll call it "txtName"), and check if this is a new name
then add it to tbl1 before this record is saved

it will look something like this:
If Dcount("[id field name]","tbl1","PersonName='" & txtName & "'")=0 then
docmd.RunSQL "Insert Into Tbl1 (PersonName) Values ('" & txtName & "');"

hope you can understand it all
good luck
Erez

I have seen different ways this question was answered but it seems to
be a different answer based on the way the databases are set up.

I have 2 databases 1 with a list of names and id numbers,the other
database with information about the names.

In tbl1 the names is the primary key and the id number I am using auto-
number
In tbl2 we can say there is information about each name but there can
be multiple entry for each name as of right now no primary key for
this table.

The relation ship is bound by the name.

I have a form which record source is tbl2 which will allow user to
input data for a name, the problem is I can only save the record if
they are putting in information for a name that already exist in tbl1.
I need to know how to add new names with information and get them an
incremented id number.

Sorry for the confusion but yes I am reffering to two tables in the
same db. Ok if I use the id number as the primary key in tbl1 and in
tbl2 how do I make sure that the id number is only associated with one
name? I am positive that there will be no duplicate names that is the
reason for using the name field as the primary key. If I have to put
any data in for somebody with the same name then I would want to put
that information in tbl2 and have it always associated with the one
name.

Erez having attempted your code but just wanted to give a different
explanation of what I am trying to accomplish.
It started out as an excel spreadsheet full of available names then in
another excel sheet there was information on those names and there
could be multiple entries on one name.
So what I did was made two tables cause ultimately the requirement is
to generate reports with how many entries are on one person, when was
the last time there was an entry made for a person and be able to
acquire that information fast.

So I created 2 tbl one with all available names and another with the
information about the names. Please let me know if any more
information is needed.

 

[msam137]

On Jul 28, 5:54 pm, Erez Mor <[email protected]>
wrote:

hi
i hope you mean you have 2 tables in the same database (not 2 databases)
first, there shouldnt be a table without a primary key (and it will almost
always come in handy after all) - so add an auto-number field to tbl2
second, consider using the id number (auto number) from tbl1 in tbl2, - not
the name, this is bad practice to use names
but to make it short, and to solve youre problem without too many changes,
you need to add some code to the After_Update event of the name control in
the form for tbl2 (i'll call it "txtName"), and check if this is a new name
then add it to tbl1 before this record is saved
it will look something like this:
If Dcount("[id field name]","tbl1","PersonName='" & txtName & "'")=0 then
docmd.RunSQL "Insert Into Tbl1 (PersonName) Values ('" & txtName & "');"
hope you can understand it all
good luck
Erez
:
I have seen different ways this question was answered but it seems to
be a different answer based on the way the databases are set up.
I have 2 databases 1 with a list of names and id numbers,the other
database with information about the names.
In tbl1 the names is the primary key and the id number I am using auto-
number
In tbl2 we can say there is information about each name but there can
be multiple entry for each name as of right now no primary key for
this table.
The relation ship is bound by the name.
I have a form which record source is tbl2 which will allow user to
input data for a name, the problem is I can only save the record if
they are putting in information for a name that already exist in tbl1.
I need to know how to add new names with information and get them an
incremented id number.

Sorry for the confusion but yes I am reffering to two tables in the
same db. Ok if I use the id number as the primary key in tbl1 and in
tbl2 how do I make sure that the id number is only associated with one
name? I am positive that there will be no duplicate names that is the
reason for using the name field as the primary key. If I have to put
any data in for somebody with the same name then I would want to put
that information in tbl2 and have it always associated with the one
name.

Erez having attempted your code but just wanted to give a different
explanation of what I am trying to accomplish.
It started out as an excel spreadsheet full of available names then in
another excel sheet there was information on those names and there
could be multiple entries on one name.
So what I did was made two tables cause ultimately the requirement is
to generate reports with how many entries are on one person, when was
the last time there was an entry made for a person and be able to
acquire that information fast.

So I created 2 tbl one with all available names and another with the
information about the names. Please let me know if any more
information is needed.

Erez I attempted your code making the necessary substitution for my
field names and I still get the same error message.

I hope somebody looks at this one again.

Ok Erez I was able to use part of your code to complete another task
and had a theory on how to use your code to complete my initial
problem any advice would be appreciated. Still not clear if the design
of my database makes sense and still not sure on the reason for
changing my primary keys and I have read several books and I know the
basic logic of all tables should have a pk.
Now on to what I have
add new information form with a record source of tbl2

Private Sub txtBox_AfterUpdate()
If "cboBox = " & txtbox & "" = 0 Then
DoCmd.RunSQL "Insert into tbl1 (employee name) values (txtbox);"
End If

What I am trying to accomplish due to the fact that i was getting
error Not able to add txtbox because it has no matching record in
tbl1. Was to be able to take my txtBox and compare the information
inside and compare it to all available records available in the
cboBox, If record found no matching value then run a SQL statement and
insert the value from txtBox in to the table so when I go to save the
data it will get rid of that error message.

What I did was created a hidden cboBox that had a row source == Select
DISTINCTROW [tbl1].[EmployeeName] FROM tbl1.My reasoning behind this
was since this form has a control source = tbl2 and that is essential
to the other txtFields on the form I did not want to mess that up.