bertman

Hello,

I use:
VB 6 sp5
Access 2000 database
DAO connection

I have the following problem. I add a new record in the database and in the
table is a Autonumbering field. I want to know that number.
For example

***
Set rs = db.OpenRecordset("T_Wedstrijd")
With rs
.AddNew
!Hoofdgroepnr = Hoofdgroepnr
!Teamnr = NeedNr(cmbTeam.Text)
!Tegenstander = txtTegenstander.Text
!Plaats = chkPlaats.Value
!Weeknr = txtWeeknr.Text
!Jaar = txtJaar.Text
!WedstrijdNr = txtWedstrijdnr.Text
.Update
End With
***

This works fine, but in the table 'T_Wedstrijd' is also a field call 'WedId'
this is a autonumber field. And after 'End With' I want that number back, I
can't compare the textfield in VB with the fields in this table because some
can be the same. So I need something uniek and that is 'WedId'.

Bert

johan bechthum

Just read it before the End With :-)
The Autonumbering field if "filled" as soon as a record is added.

Set rs = db.OpenRecordset("T_Wedstrijd")
With rs
.AddNew
txtWedID.Text = CLng(!WedID)
!Hoofdgroepnr = Hoofdgroepnr
!Teamnr = NeedNr(cmbTeam.Text)
!Tegenstander = txtTegenstander.Text
!Plaats = chkPlaats.Value
!Weeknr = txtWeeknr.Text
!Jaar = txtJaar.Text
!WedstrijdNr = txtWedstrijdnr.Text
.Update
End With


Hope this helps.

Johan.


"Bertman" <Bertman@home.nl> schreef in bericht
news:bmgmnp$n6v$1@news1.tilbu1.nb.home.nl...


the

'WedId'

I

some

robert berman

If the autonumber column is your identity column consider using
"SELECT @@IDENTITY"

bertman

Hello Johan

Thnx for youre reply. And how easy can it be. Thnx

BErt


"Johan Bechthum" <JohanBechthum@hotmail.com> schreef in bericht
news:k7Wib.11034$P51.317904@amstwist00...

back,

bertman

Hello Robert

Thnx for youre reply, but what do you mean with this:
"SELECT @@IDENTITY"
I never heard of it.

Bert


"Robert Berman" <ThornmastrNOSPAM@earthlink.net> schreef in bericht
news:cXWib.55605$mQ2.8682@newsread1.news.atl.earth link.net...

robert berman

Bert,

For SQL Server (and Access with the Jet 4.0) drivers

SELECT @@identity FROM Table will get you the last inserted records
identity/autonumber

tony m

The original poster was using DAO, which doesn't support the SELECT
@@identity method of retrieving the last inserted autonumber.

Tony

robert berman

Whoops. I forgot the DAO comment. However, while I can certainly
understand the use of DAO with Jet 3 drivers, but, why use DAO using
Jet 4?

tony m

Personally, I tend to stick with with DAO when doing Access projects,
because I've used it since Access 2/VB3 and I'm more comfortable
working with it. You are certainly right, however, about ADO being
more flexible when using Jet 4.

Tony

robert berman

Tony,

Like you, I have used DAO since ACCESS 2. If it was not for the
stupidity of M$ I would probably still be happily using DAO. But, when
Bill decided to take JET out of active development, I had clients
begging to rewrite applications using a JET backend to using MSDE
backends. This has become for me what the turn of the century fiasco was
for banking consultants.

Robert