ADO question... 1

[elziko]

I'm doing something along these lines:

recmdb.AddNew
recmdb.Fields("ObjectType&quot = ObjectTypeCombo.Text
recmdb.Update

However the Access database that I'm adding the record to has an ID field which is autonumbered.
At any point after the AddNew method I need to know what the autonumber given was.

If its not given a number until the .Update method has completed I'm happy to wait until then.

Any ideas?

elziko

 

[GTO]

Maybe the following will help

rs.Open ("SELECT * from ItemInfo where Identifier = '" & sBarScan & "'&quot
iDeptNum = rs(3) 'Get Dept# for scanned item
DeptAmt = rs(2) 'Get amt for scanned item

This gets 2 items from a record using a barcode which was scanned.
You could get your number, I think, by getting rs(0). The autonumber. In access that field should be set to indexed(yes, no duplicates)

 

[elziko]

Thanks for your reply but what you are suggesting is using, in your case, sBarScan to retrieve the record I want allowing me to find its autonumber.

Thats fine in theory, but in practice my several records in my database have identical "sBarScan" entries. The only thing that uniquely identifies any record is its auto number.

So if I dont know the autonumber at the time it means I cannot isolate a record and then return its autonumber field entry.

I need to find the autonumber field entry of the last added record.

When I try and return the autonumber of the record currently being added I get usually the last record accesed.

I have also tried opening the entire set of data as a record set. Doing a .MoveLast and then retrieving the autonumber field. But it would seem that the last record in the record set is NOT the last record added!

The only other way I can think of doing it is to go through every record and find the one with the greatest autonumber. Very inefficient.

Any other ideas?

elziko

 

[scorpio66]

If you've got a lock on the table you could do a MoveLast and read the ID field.

Chaz

 

[elziko]

"I have also tried opening the entire set of data as a record set. Doing a .MoveLast and then retrieving the autonumber field. But it would seem that the last record in the record set is NOT the last record added!"

But I've sorted that now by doing an ORDER BY ID first.

But it still seems messy having to close, reopen, requery, sort, and move to the last record just to find the nubmer of a record just added!

Cheers,

elziko

 

[scorpio66]

Ah, no that won't work. If you need to close and reopen the recordset then the last record cannot be guaranteed to be the last record added.

You might want to play with the locking/cursortype properties used when opening the recordset prior to opening. You should be able to retrieve the value of the ID field just after the Update, i.e.

.Update
ID = .Fields("ID&quot

Using a client-side cursor might do the trick.

Chaz

 

[RAtkison]

I've used the following code and it seems to work. I'm using ADO on a SQL Server database and also with Access Databases. cn is a connection that was made on the form load. If it's not possible to sort the data then it would be unreliable.

I've been working with this problem recently and was hoping someone had a great solution. This is what I've found to work best for me at this point in time.

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "Select * from Table order by ID", cn, adOpenDynamic, adLockOptimistic
rs.AddNew
rs.Fields("ItemType&quot = txtItem
rs.Fields("ItemDescription&quot = txtDescription
rs.Update
rs.MoveLast
txtID = rs.fields("ID&quot
rs.Close
Set rs = Nothing

 

[Terry123]

I believe after your update you can use a
"select max(ID) as MaxOfID from ..." statement and that
should give you the highest ID in the autonumber field
which would be the last one added..............

 

[scorpio66]

Try setting these two properties in the recordset:

  .CursorLocation = adUseClient
  .Properties("Update Resync") = adResyncAutoIncrement

Then just after the update you should be able to get the correct value of the ID field WITHOUT doing another select.

  .Update
  ID = .FieldS("ID")

I cannot stress the importance of not taking terry123s approach to this - in a multi-user environment THIS WILL NOT WORK RELIABLY.

Chaz

 

[Geos]

I don't really know a lot, but I'm learning. I do about the same thing you want to do with an invoice number. It is an autonumber. The data entry people wanted to see the last invoice for some reason. I did what terry123 suggested. I used a query with the one field set to MAX invoice number and then wrote the output to a textbox on the entry form. This way the previous number is always on the screen. I run the qrery and refresh the screen right after .Update. There is a split second that I suppose someone else might update the table.