DoCmd.RUNSQL INSERT Queries

[twen]

If I am adding a record to a table "Contacts" and a table "Addresses" from a
form where "[Contact To Address]" is the lookup form and after adding a
record to each of the two tables then adding a record with each of the ID's
to the [Contact To Address] table, is there any other way to retrieve the
ID's from the tables immediately after adding them via DoCmd.RunSQL
statements in code? Currenty I it will add one record to Contacts, use DLast
and retrieve the last record's ID, then same thing for Addresses then I add
the new record to [Contact To Address]. I wanted to know if I could retrieve
the ID and store it another way rather than Dlast because that is prone to
errors in that the last record added in the table may not be the one the user
just added with the form. Any suggestions?

 

[Michel Walsh]

*if* you add only one record in two (or three tables), you can use a query,
and append the record through that query.


Example. Parent (f1, f2), Child( f2*, f3) where child.f2 is the primary
key, and there is an enforced data integrity relation such that Parent.f2
must be present into child.f2.

Then, with the query:

SELECT Child.*, Parent.*
FROM Parent INNER JOIN Child ON Parent.f2 = Child.f2;


you just have to fill child.f2, child.f3 and parent.f1 to append ONE record
into both tables, even if the record is new to both tables. And you don't
need to collect the autonumber that is to be referred... it is automatically
done for you. NOTE that you have to enter the field in the table supplying
the reference, here, in Child: you enter Child.f2, and NOT Parent.f2, since
Jet knows, through the relation, that if you leave it blank, from the query,
it is because Parent.f2 must be equal to the supplied Child.f2.


That is also known as "autolookup" feature, in Jet-queries.



Hoping it may help,
Vanderghast, Access MVP

 

[twen]

Okay I don't think that is what I am looking for because this is going to be
an add form. There are 3 tables Contacts, Addresses, and Contact To Address.
The form and the controls are not bound to anything because it would not
work if I were trying to add to the Contact To Address table if there were no
contact ID or address ID present. When the user clicks Add a function runs
which checks the values of all the controls then builds 3 append queries.
Which are then executed in the following order, Contacts, Address, then
Contact to Address.
After the contacts insert statement is run I do a dlast and retrieve the
contact id and store it in a string, next i run the address insert statement
and do a dlast and retrieve the address id and store it in a separate string.
Then I run the Contact To Address insert statement and append both of the IDs
to it.
What I want is, is there a better way to retrieve the IDs rather than using
DLast because all that does is go to the last record on the table and if by
chance someone else ran the same append at the same time it could take that
record rather than the one that was just added in the form. Like can I put a
control on the form that will store the value of the id once it is added?
I understand the Autolookup to a degree but I do not think that is what I am
after.
Please explain if I am missing something.

*if* you add only one record in two (or three tables), you can use a query,
and append the record through that query.


Example. Parent (f1, f2), Child( f2*, f3) where child.f2 is the primary
key, and there is an enforced data integrity relation such that Parent.f2
must be present into child.f2.

Then, with the query:

SELECT Child.*, Parent.*
FROM Parent INNER JOIN Child ON Parent.f2 = Child.f2;


you just have to fill child.f2, child.f3 and parent.f1 to append ONE record
into both tables, even if the record is new to both tables. And you don't
need to collect the autonumber that is to be referred... it is automatically
done for you. NOTE that you have to enter the field in the table supplying
the reference, here, in Child: you enter Child.f2, and NOT Parent.f2, since
Jet knows, through the relation, that if you leave it blank, from the query,
it is because Parent.f2 must be equal to the supplied Child.f2.


That is also known as "autolookup" feature, in Jet-queries.



Hoping it may help,
Vanderghast, Access MVP

If I am adding a record to a table "Contacts" and a table "Addresses" from
a
form where "[Contact To Address]" is the lookup form and after adding a
record to each of the two tables then adding a record with each of the
ID's
to the [Contact To Address] table, is there any other way to retrieve the
ID's from the tables immediately after adding them via DoCmd.RunSQL
statements in code? Currenty I it will add one record to Contacts, use
DLast
and retrieve the last record's ID, then same thing for Addresses then I
add
the new record to [Contact To Address]. I wanted to know if I could
retrieve
the ID and store it another way rather than Dlast because that is prone to
errors in that the last record added in the table may not be the one the
user
just added with the form. Any suggestions?

 

[Michel Walsh]

DLast won't be reliable, indeed. On the other hand, I assume the 'real name'
is also unique (the end user use real names, not obscure numerical id), so,
you can use a DLookup, rather than DLast, so using DLookup on the name, to
retrieve the id. If you use DAO recordset, the bookmark LastModified will
point to the newly added record.


Vanderghast, Access MVP

Okay I don't think that is what I am looking for because this is going to
be
an add form. There are 3 tables Contacts, Addresses, and Contact To
Address.
The form and the controls are not bound to anything because it would not
work if I were trying to add to the Contact To Address table if there were
no
contact ID or address ID present. When the user clicks Add a function runs
which checks the values of all the controls then builds 3 append queries.
Which are then executed in the following order, Contacts, Address, then
Contact to Address.
After the contacts insert statement is run I do a dlast and retrieve the
contact id and store it in a string, next i run the address insert
statement
and do a dlast and retrieve the address id and store it in a separate
string.
Then I run the Contact To Address insert statement and append both of the
IDs
to it.
What I want is, is there a better way to retrieve the IDs rather than
using
DLast because all that does is go to the last record on the table and if
by
chance someone else ran the same append at the same time it could take
that
record rather than the one that was just added in the form. Like can I put
a
control on the form that will store the value of the id once it is added?
I understand the Autolookup to a degree but I do not think that is what I
am
after.
Please explain if I am missing something.

*if* you add only one record in two (or three tables), you can use a
query,
and append the record through that query.


Example. Parent (f1, f2), Child( f2*, f3) where child.f2 is the
primary
key, and there is an enforced data integrity relation such that Parent.f2
must be present into child.f2.

Then, with the query:

SELECT Child.*, Parent.*
FROM Parent INNER JOIN Child ON Parent.f2 = Child.f2;


you just have to fill child.f2, child.f3 and parent.f1 to append ONE
record
into both tables, even if the record is new to both tables. And you don't
need to collect the autonumber that is to be referred... it is
automatically
done for you. NOTE that you have to enter the field in the table
supplying
the reference, here, in Child: you enter Child.f2, and NOT Parent.f2,
since
Jet knows, through the relation, that if you leave it blank, from the
query,
it is because Parent.f2 must be equal to the supplied Child.f2.


That is also known as "autolookup" feature, in Jet-queries.



Hoping it may help,
Vanderghast, Access MVP

If I am adding a record to a table "Contacts" and a table "Addresses"
from
a
form where "[Contact To Address]" is the lookup form and after adding a
record to each of the two tables then adding a record with each of the
ID's
to the [Contact To Address] table, is there any other way to retrieve
the
ID's from the tables immediately after adding them via DoCmd.RunSQL
statements in code? Currenty I it will add one record to Contacts, use
DLast
and retrieve the last record's ID, then same thing for Addresses then I
add
the new record to [Contact To Address]. I wanted to know if I could
retrieve
the ID and store it another way rather than Dlast because that is prone
to
errors in that the last record added in the table may not be the one
the
user
just added with the form. Any suggestions?

 

[Michel Walsh]

Also note that inserting the record through a join, with the autolookup
feature, will create the record in the implied tables, if required, while
carrying over the 'autonumber-id' values of the new record, in the other
tables. So, adding the data through the query, rather than through the
tables, one by one, is just a matter to ... try it, and see how easy it is.
It works only with Jet, right out of the box. With MS SQL Server, you have
to use "Instead Of Trigger" to be able to add records in more than one
table, through a view, in the same way that does Jet, automatically.


Hoping it may help,
Vanderghast, Access MVP

DLast won't be reliable, indeed. On the other hand, I assume the 'real
name' is also unique (the end user use real names, not obscure numerical
id), so, you can use a DLookup, rather than DLast, so using DLookup on the
name, to retrieve the id. If you use DAO recordset, the bookmark
LastModified will point to the newly added record.


Vanderghast, Access MVP

Okay I don't think that is what I am looking for because this is going to
be
an add form. There are 3 tables Contacts, Addresses, and Contact To
Address.
The form and the controls are not bound to anything because it would not
work if I were trying to add to the Contact To Address table if there
were no
contact ID or address ID present. When the user clicks Add a function
runs
which checks the values of all the controls then builds 3 append queries.
Which are then executed in the following order, Contacts, Address, then
Contact to Address.
After the contacts insert statement is run I do a dlast and retrieve the
contact id and store it in a string, next i run the address insert
statement
and do a dlast and retrieve the address id and store it in a separate
string.
Then I run the Contact To Address insert statement and append both of the
IDs
to it.
What I want is, is there a better way to retrieve the IDs rather than
using
DLast because all that does is go to the last record on the table and if
by
chance someone else ran the same append at the same time it could take
that
record rather than the one that was just added in the form. Like can I
put a
control on the form that will store the value of the id once it is added?
I understand the Autolookup to a degree but I do not think that is what I
am
after.
Please explain if I am missing something.

*if* you add only one record in two (or three tables), you can use a
query,
and append the record through that query.


Example. Parent (f1, f2), Child( f2*, f3) where child.f2 is the
primary
key, and there is an enforced data integrity relation such that
Parent.f2
must be present into child.f2.

Then, with the query:

SELECT Child.*, Parent.*
FROM Parent INNER JOIN Child ON Parent.f2 = Child.f2;


you just have to fill child.f2, child.f3 and parent.f1 to append ONE
record
into both tables, even if the record is new to both tables. And you
don't
need to collect the autonumber that is to be referred... it is
automatically
done for you. NOTE that you have to enter the field in the table
supplying
the reference, here, in Child: you enter Child.f2, and NOT Parent.f2,
since
Jet knows, through the relation, that if you leave it blank, from the
query,
it is because Parent.f2 must be equal to the supplied Child.f2.


That is also known as "autolookup" feature, in Jet-queries.



Hoping it may help,
Vanderghast, Access MVP




If I am adding a record to a table "Contacts" and a table "Addresses"
from
a
form where "[Contact To Address]" is the lookup form and after adding
a
record to each of the two tables then adding a record with each of the
ID's
to the [Contact To Address] table, is there any other way to retrieve
the
ID's from the tables immediately after adding them via DoCmd.RunSQL
statements in code? Currenty I it will add one record to Contacts, use
DLast
and retrieve the last record's ID, then same thing for Addresses then
I
add
the new record to [Contact To Address]. I wanted to know if I could
retrieve
the ID and store it another way rather than Dlast because that is
prone to
errors in that the last record added in the table may not be the one
the
user
just added with the form. Any suggestions?