Getting the PK Autonumber - Unbound Save

  • Thread starter Thread starter Bob Barnes
  • Start date Start date
B

Bob Barnes

Using Unbound Form...as part of the Recordset (how can I get the PK
Autonumber right after the .Update??

...the ": MsgBox !RecID: RecID = !RecID" is NOT correct

TIA - Bob

Set Z = CurrentDb
Q = "SELECT * FROM MainData;"
Set RS = Z.OpenRecordset(Q, dbOpenDynaset)
With RS
.AddNew: !TheDate = ADate: !CC = ACC: !ReqNum = ARN
!TheStat = TheStat: !EstCost = AEstCost: !TheReq = TheReq
!AcctNum = AcctNum: .Update: MsgBox !RecID: RecID = !RecID
.Close: Set RS = Nothing: Z.Close: Set Z = Nothing
End With
 
If the form is unbound, you'll need to explicitly save the record, then
build a recordset to recover the Max value of the Autonumber. If the
Autonumber is random, you'll need a date/time stamp to get the last record
and read the PK value.
 
After saving the new record, you can read the AutoNumber from the field
(named ID in the example below.)

Dim Z As DAO.Database
Dim Q As DAO.Recordset

Set Z = CurrentDb
Q = "SELECT * FROM MainData;"
Set RS = Z.OpenRecordset(Q, dbOpenDynaset)

With RS
.AddNew
!TheDate = ADate
!CC = ACC
!ReqNum = ARN
!TheStat = TheStat
!EstCost = AEstCost
!TheReq = TheReq
!AcctNum = AcctNum
.Update
.Bookmark = .LastModified
MsgBox "The new ID is " & RS!ID
End With
RS.Close

Set RS = Nothing
Set Z = Nothing

Notes:
1. If the data is in a JET table, you can read the ID even before the
Update.

2. After the Update, you can't assume the current record is the new one
unless you explicitly set the Bookmark.

3. Do not Close the database. You did not open it, so you should not close
it. (You merely set Z equal to it, so to clean up you just destroy Z.)

4. Isn't it easier to read when set out like that? :-)
 
If you using a access database (jet), then you get the "id" *anytime* right
after the record is dirty...

so,
With RS
.AddNew
!TheDate = ADate
!CC = ACC
!ReqNum = ARN
!TheStat = TheStat
!EstCost = AEstCost
!TheReq = TheReq
!AcctNum = AcctNum
MsgBox !RecID
RecID = !RecID
.Update:
end with

dao has this annoying thing that when you ADD a new record, and issue a
update, the record pointer moves to the 1st record in the table. (this does
not happen when you UPDATE and ***existing*** record, but only when you add
a ***new*** record).

If you want the id *after* the update is done, then go:

rstRec.Update
rstRec.BookMark = rstRec.LastUpdated
lngNewID = rstRec!ID
rstRec.Close

The above code is preferred, since when using sql server, the "id" is not
actually generated until the update occurs. So if you want your code
to work with linked tables to sql server (or now sharepoint), the
above use of lastupdated is a good coding standard to adopt.

By the way, I would avoid that "multi" line syntax you using, as you can
see how hard it was to simply "move" up the position of the
msgbox command. When you put things on separate lines, not only can
you easily cut/paste to "move" commands, but you can later **easily***
grab parts of your code for re-use in other applications. Hence,
advoiding multi-line code aids in re-use of code.

When you put multiple statements on one line, you increase the cost of
maintaining your code, and maintains code over the life of project usually
will cost MORE then the initial coding time. So you want to make you code
as easy to edit as possible. The issue of multiple lines of code looking
amateur is not a big deal (who cares). However, the increased difficulty
of editing and modifying the code is an issue that you want to be aware
of.
 
That's what I thought..turning around and getting Max right away.

The DB is limited use. Wonder if there were MANY saves of New Records
at the same time, if Max could possibly get the incorrect record in that
nanosecond>

If so, it opens a Report, where the User would see if it's not the correct
record just entered.
 
Thank you Allen.

Allen Browne said:
After saving the new record, you can read the AutoNumber from the field
(named ID in the example below.)

Dim Z As DAO.Database
Dim Q As DAO.Recordset

Set Z = CurrentDb
Q = "SELECT * FROM MainData;"
Set RS = Z.OpenRecordset(Q, dbOpenDynaset)

With RS
.AddNew
!TheDate = ADate
!CC = ACC
!ReqNum = ARN
!TheStat = TheStat
!EstCost = AEstCost
!TheReq = TheReq
!AcctNum = AcctNum
.Update
.Bookmark = .LastModified
MsgBox "The new ID is " & RS!ID
End With
RS.Close

Set RS = Nothing
Set Z = Nothing

Notes:
1. If the data is in a JET table, you can read the ID even before the
Update.

2. After the Update, you can't assume the current record is the new one
unless you explicitly set the Bookmark.

3. Do not Close the database. You did not open it, so you should not close
it. (You merely set Z equal to it, so to clean up you just destroy Z.)

4. Isn't it easier to read when set out like that? :-)
Click to expand...
 
Albert - Yes, I noticed the number returns as 1.

Will do this as Allen also recommended..
If you want the id *after* the update is done, then go:

rstRec.Update
rstRec.BookMark = rstRec.LastUpdated
lngNewID = rstRec!ID
rstRec.Close
Click to expand...

I know about the multi-line syntax, and how the rest of the world programs
code.
I do that because it's so more easier for me to read a page of code. And,
if another
Programmer looks at my code, there might be a brief cuss, but (s)he will
adapt.

W/ all the "white space" between code, I find it harder to read...at least
for me.

Oh, remember me for all the embedded commas, double-quotes and linefeed??
I talked w/ a friend, and he showed me MS Scripting to completely clean a
text file,
and the Line Input the cleaned text file....works great, even when I tried
to mess it up w/ 2 - 3 linefeeds.

If you'll like a copy of that code, let me know. It's at another Client
where I show be on Thursday.

Thank you - Bob
 
If the form is unbound, you'll need to explicitly save the record,
then build a recordset to recover the Max value of the Autonumber.
If the Autonumber is random, you'll need a date/time stamp to get
the last record and read the PK value.
Click to expand...

Or, you can use the same db variable and if it's a server back end
that supports it (such as SQL Server), open a recordset on:

SELECT @@IDENTITY

The last assigned identity field assigned will be returned as
Field(0) of your recordset.

This approach also makes it possible do to the insert entirely with
a SQL INSERT statement, which is going to lock your table a
substantially shorter time.
 
I know about the multi-line syntax, and how the rest of the world programs
code.
I do that because it's so more easier for me to read a page of code. And,
if another
Programmer looks at my code, there might be a brief cuss, but (s)he will
adapt.

W/ all the "white space" between code, I find it harder to read...at least
for me.
Click to expand...

The question is not so much you find it easier to read, the question is do
you find it easier to maintain?

It's a little bit arrogance on your part to put your thumbs up to the whole
computing industry in general as to the coding standards they adopt.

The problem is not that other developers can't adapt to your coding style,
the problem is your coding style does not allow you to cut and paste lines
of code from different pieces of code throughout your application.
Furthermore it's always good to adopt a consistent coding style, but then
how are you going to incorporate any or many of the free code libraries and
examples you find on the Internet into your code? If you don't spend the
time to re-format the code, then you have an application with two completely
different coating styles. And, if you wanna keep consisting coding style,
then you'll have to reformat and rewrite large portions of code that you
reuse from other people's applications.

At a certain point this type of coding style constitutes wasting of company
resources, and virtually any experienced developer would tell you that.
Thus, what happens now when your current, or new boss googles here, and sees
how you are willfully, and knowingly wasting company resources?
Oh, remember me for all the embedded commas, double-quotes and linefeed??
I talked w/ a friend, and he showed me MS Scripting to completely clean a
text file,
and the Line Input the cleaned text file....works great, even when I tried
to mess it up w/ 2 - 3 linefeeds.
Click to expand...

That's great, and I suggested to you that it makes a lot of sense to change
the format of the date BEFORE you attempt to import it into access, and it
seems you at least heeding some of my advice in this regards.
 
Back
Top