How to get Autonumber key thru code

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I understand that if you have a main/subform, you can capture what the main
form's ID is if there is a master/child link field. However, for a variety
of reasons, I have to do my own manual insertion of data, which doesn't make
use of the main/subform design.

Here is the scenario:

tblStatus with Autonumber as primary key

Some but not all of the records in tblStatus will have additional details
that is stored in tblDetail.
I would like to store the corresponding ID from tblStatus in tblDetail to
establish the relationship.

But since I am inserting the record in tblStatus manually using INSERT INTO
statement, how do I know what the ID is when I create the record in tblDetail?

Can anyone make a suggestion? Should I change the structural design of
either tables? Main/subform might be the easiest solution but I am hoping
there are other ways around it.
 
Not sure what your code is now but I am going to assume that a user is enter
a record and then you want to add detail to it with the ID. If so I would do
this.

rstMain.Add
rstMain![Field1] = Me.Field1
rstMain![Field2] = Me.Field2
rstMain.Update
rstMain.Close
intID = rstMain.Open "Select Max([ID]) From tblMain"

rstDetail.Add
rstDetail![Field3] = Me.Field3
rstDetail![Field4] = Me.Field4
rstDetail.Update
rstDetail.Close

It's not pretty but it works.
 
Are you entering the new record in the main table *directly* into the
table or indirectly via Form?
 
This seems like it would work. I will try it.

Related question - there shouldn't be a situation where between the time I
do an INSERT INTO statement and when I do the SELECT Max that another record
could have been added to the main table, could it? What's the likelihood of
that? It is a multiple user environment over a WAN. I would be doing INSERT
in the main table and INSERT in the tblDetail back to back in the same
procedure.


Conrad said:
Not sure what your code is now but I am going to assume that a user is enter
a record and then you want to add detail to it with the ID. If so I would do
this.

rstMain.Add
rstMain![Field1] = Me.Field1
rstMain![Field2] = Me.Field2
rstMain.Update
rstMain.Close
intID = rstMain.Open "Select Max([ID]) From tblMain"

rstDetail.Add
rstDetail![Field3] = Me.Field3
rstDetail![Field4] = Me.Field4
rstDetail.Update
rstDetail.Close

It's not pretty but it works.

kdw said:
I understand that if you have a main/subform, you can capture what the main
form's ID is if there is a master/child link field. However, for a variety
of reasons, I have to do my own manual insertion of data, which doesn't make
use of the main/subform design.

Here is the scenario:

tblStatus with Autonumber as primary key

Some but not all of the records in tblStatus will have additional details
that is stored in tblDetail.
I would like to store the corresponding ID from tblStatus in tblDetail to
establish the relationship.

But since I am inserting the record in tblStatus manually using INSERT INTO
statement, how do I know what the ID is when I create the record in tblDetail?

Can anyone make a suggestion? Should I change the structural design of
either tables? Main/subform might be the easiest solution but I am hoping
there are other ways around it.
Click to expand...
Click to expand...
 
No. All the data are entered into unbound fields via a form. Some of the
fields get posted to the main table (tblStatus) and others get posted to the
related tblDetail table, depending on certain conditions.
 
OK its the 'unbound' bit that I missed. In a situation where the
Autonumber field is on the main form and used for the linkMasterFields
for the subform control, the subform will automatically grab the value.
Which leads me to the next question...

Why are you using unbound fields?
 
I am using unbound fields because I am implementing a "Save" button feature
instead of the main/subform approach. I post the data via code when the user
click Save. Yes, I know it is more work to program. This approach has
worked great for the rest of the app has just this portion is giving me some
headache. Thanks for looking at this with me.
 
But *WHY* did you choose to use unbound controls? What were the
*specific* reasons behind the decision to go this route?
 
kdw said:
No. All the data are entered into unbound fields via a form. Some
of the fields get posted to the main table (tblStatus) and others get
posted to the related tblDetail table, depending on certain
conditions.
Click to expand...

Probably the easiest thing to do is to add the record to the main form
via a recordset, at which point you can pick up the autonumber that is
generated for that record. Then insert the record in the detail table,
supplying that autonumber value for the foreign key field. It would
look something like this:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim lngMainID As Long

Set db = CurrentDb

Set rs = db.OpenRecordset( _
"SELECT * FROM tblMain WHERE False" )

With rs
.AddNew
!Field1 = Me!txtField1
!Field2 = Me!txtField2
.Update
.Bookmark = .LastModified
lngMainID = !ID ' this is the autonumber field
.Close
End With

If <we need to add a detail record> Then
db.Execute _
"INSERT INTO tblDetail (ID, NumField, TextField) " & _
"VALUES (" & lngID & _
", " & Me!txtNumField & ", '" & Me!txtCharField & "')",
_
dbFailOnError
End If

Set rs = Nothing
Set db = Nothing
 
Dirk Goldgar said:
Probably the easiest thing to do is to add the record to the main form
via a recordset
Click to expand...

Sorry, I meant to say, "add the record to the main TABLE via a
recordset". I hope that didn't confuse anybody.
 
Dirk,
I made some modifications to your example because I am using ADO. But there
is no LastModified property? Is there an equivalent? Other than that this
works great!
Thank you.

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim lStatusID as Long

Set cnn = Application.CurrentProject.Connection
Set rst = New ADODB.Recordset

rst.Open "SELECT * FROM tblChangeStatus WHERE False", cnn, adOpenDynamic,
adLockOptimistic

With rst

.AddNew
!St = Me!txtSt
!PostDate = Now()
!StatusTypeID = cboStatusTypeID
.Update
.Bookmark = .LastModified <---Not valid
lStatusID = !ChangeStatusID ' this is the autonumber field
.Close
End With

Set rst = Nothing
Set cnn = Nothing
 
kdw said:
Dirk,
I made some modifications to your example because I am using ADO.
But there is no LastModified property? Is there an equivalent?
Other than that this works great!
Thank you.

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim lStatusID as Long

Set cnn = Application.CurrentProject.Connection
Set rst = New ADODB.Recordset

rst.Open "SELECT * FROM tblChangeStatus WHERE False", cnn,
adOpenDynamic, adLockOptimistic

With rst

.AddNew
!St = Me!txtSt
!PostDate = Now()
!StatusTypeID = cboStatusTypeID
.Update
.Bookmark = .LastModified <---Not valid
lStatusID = !ChangeStatusID ' this is the autonumber field
.Close
End With

Set rst = Nothing
Set cnn = Nothing
Click to expand...

If you simply remove this line:
.Bookmark = .LastModified ' <---Not valid
Click to expand...

it should work in a Jet database. But if the back-end is not Jet -- SQL
Server for example -- I have no idea whether the autonumber is available
to you at that point or not. If necessary, you could query the
connection for the @@IDENTITY value:

Set rs = cnn.Execute("SELECT @@IDENTITY")
lStatusID = rs(0)
 
The functionality does exist in SQLServer, but under a different name.

Sign Me,
SQLServer Novice
 
Hi,


If you use ADO, the recordset "stays" on the record you just added, so,
after the rst.Update, read the autonumber field, before moving to any other
record.

If you use DAO, the autonumber is determined as soon as you get the
rst.AddNew. While the recordset probably won't stay on the record after you
write rst.update, you have plenty of room, between the AddNew and Update to
read the autonumber field. You have to check there was no error in the
Update (as in the case a validation fails), before effectively using the
autonumber for other uses in the database.




Hoping it may help,
Vanderghast, Access MVP
 
Michel Walsh said:
If you use DAO, the autonumber is determined as soon as you get
the rst.AddNew. While the recordset probably won't stay on the record
after you write rst.update, you have plenty of room, between the
AddNew and Update to read the autonumber field.
Click to expand...

Michel -

That is true for Jet tables, but not for linked SQL Server tables.
That's why I've gotten in the habit of using the .LastModified property
to find the record again after saving it.
 
Hi,


Indeed, for linked table, that is quite different and LastModified is
clearly the way to go in that case.


Vanderghast, Access MVP
 
Back
Top