Get record I just appended to a table?

[Tim]

Say I have a table named table1 with the following fields (for simplicity)
ID - autonumber
field1 - text

I have a form that allows the user to enter a value in for field1 and checks
to make sure it is valid. I then append this record to table1 using
INSERT INTO table1 ( field1 ) VALUES ( strvar )
in vba using an adodb.command line to .execute the sql statement. Now, I
want to get the autonumber ID value for this newly added record. Is there a
way to do it without querying the table on the value that was just entered? I
assume that I can't just grab the last record that was just appended because
multiple users could possibly append at the same (almost same) time and I
could grab the wrong ID. Also, there is a possibility that field1 has the
same value in it, so if I matched on that, I could possibly grab the wrong ID
again.

The reason I want to get the autonumber ID is because I have a second table
that has a relationship with this field. So, I have to add this first table
to get the autonumber ID and then add that autonumber ID to the 2nd table
(Along with other values) to create a complete account of what is on the
form.

Or is there an easier way than how I am going about this? I have a form that
has header and footer information in it and then the middle of the form has 4
groups of data that has all the same fields, but either different data or no
data at all. But, at most, there are only 4 groups of data. So, I decided to
create one table that has all the header and footer information in it along
with 4 fields that link to the other table. And the 2nd table has 1 instance
of the 4 groups of data along with the autonumber ID field. So, I want to be
able to check to see if there is data inputted, then I save it and get the
autonumber ID to link it back to the header and footer information. If there
is no data, then I do not add a record to the 2nd table and link that field
in the 1st table to a blank record in the 2nd table. Then, later, if they add
information to a group that was previously blank, I will add that record and
link its autonumber id up with that field. (I hope that made sense.)
Any help or suggestions are much appreciated!
Tim

 

[John W. Vinson]

Say I have a table named table1 with the following fields (for simplicity)
ID - autonumber
field1 - text

I have a form that allows the user to enter a value in for field1 and checks
to make sure it is valid. I then append this record to table1 using
INSERT INTO table1 ( field1 ) VALUES ( strvar )
in vba using an adodb.command line to .execute the sql statement. Now, I
want to get the autonumber ID value for this newly added record. Is there a
way to do it without querying the table on the value that was just entered? I
assume that I can't just grab the last record that was just appended because
multiple users could possibly append at the same (almost same) time and I
could grab the wrong ID. Also, there is a possibility that field1 has the
same value in it, so if I matched on that, I could possibly grab the wrong ID
again.

The reason I want to get the autonumber ID is because I have a second table
that has a relationship with this field. So, I have to add this first table
to get the autonumber ID and then add that autonumber ID to the 2nd table
(Along with other values) to create a complete account of what is on the
form.

Or is there an easier way than how I am going about this? I have a form that
has header and footer information in it and then the middle of the form has 4
groups of data that has all the same fields, but either different data or no
data at all. But, at most, there are only 4 groups of data. So, I decided to
create one table that has all the header and footer information in it along
with 4 fields that link to the other table. And the 2nd table has 1 instance
of the 4 groups of data along with the autonumber ID field. So, I want to be
able to check to see if there is data inputted, then I save it and get the
autonumber ID to link it back to the header and footer information. If there
is no data, then I do not add a record to the 2nd table and link that field
in the 1st table to a blank record in the 2nd table. Then, later, if they add
information to a group that was previously blank, I will add that record and
link its autonumber id up with that field. (I hope that made sense.)
Any help or suggestions are much appreciated!
Tim

No. This makes no sense at all.

If you have a one to many relationship, why not just use a Form for the one
table, with a Subform for the many? You need no code AT ALL, and you certainly
don't need any INSERT INTO statements.

Are you intentionally using unbound forms, and throwing away all of the useful
tools that bound forms provide? If so why?

John W. Vinson [MVP]

 

[KARL DEWEY]

Sounds like what you need is a one-to-many table relationship. Make the
Autonumber field in first table the primary key. Have the number field in
the 2nd table as an integer. Open the Relationship window and add both
tables. The priimary key will be bold. Click on it and drag to the 2nd
table integer field. Select Enforce Referential Integerity and Cascade
Update Related Fields.
Use the two table in a form/subform with the autonumber/integer field as
Master/Child links.

 

[Tim]

John,
I am using an unbound form because initially I was not going to save any of
the data they inputted into this form. Most of the data is prepopulated
(names, dates, etc.). The end user would only be tweaking a few things that
they have to compare to data that is in another application before they
printed this out. I am adding the option to save it because sometimes they
will not be near a printer when they are using it. So, they can then reopen
what they have saved to print when they are back in the office. But, I see
what you are saying. I can bind the form and create a subform for these two
tables (now that I created them) and let Access do all the work in managing
their relationship. I was actually finished with all the functionality of it,
but wanted to add this one "little" thing. I did not take a step back to look
at how much it would change the design of what I had.
Thanks for making me step back and take a different look at this.
Tim