ADO: primary key not accessable

[zysset]

im using an access db within my vb application. there are absolutely no problems to fill in the fields with data, except the autonumbered (long integer) id field, which is always referenced by NULL. i have to read this value to do the relationship to other tables...
hope someone have an idea?

[paulw]

I don't entirely understand your q.

You never write a value to an AutoNumber field and it will CERTAINLY never be NULL. How are you trying to get at the value? Some code might help.

Paul.

[zysset]

AccessGUI.datHwRS.Recordset.AddNew
For i = 0 To (UBound(hwFields) - 1)
AccessGUI.datHwRS.Recordset.Fields(hwFields(i)).value =_
col.item(hwFields(i))
Next i
AccessGUI.datHwRS.Recordset.UpdateBatch adAffectAll
hwId = AccessGUI.datHwRS.Recordset.Fields("id").value

error happens in the last line - the id field is a null object?!? of course it is not...

[Vit]

Make sure that "ID" field is included into recordset's fields collection.

[paulw]

There is no code here to omit the autonumber field - you should not assign a value to that field - this may be what is causing you probs.

Try omitting the value update if the field name is "id"

e.g.

AccessGUI.datHwRS.Recordset.AddNew 
For i = 0 To (UBound(hwFields) - 1) 
If Not(hwFields(i) = "id") Then
  AccessGUI.datHwRS.Recordset.Fields(hwFields(i)).value = col.item(hwFields(i)) 
End If
Next i 
AccessGUI.datHwRS.Recordset.UpdateBatch adAffectAll 
hwId = AccessGUI.datHwRS.Recordset.Fields("id").value

I am assuming that hwFields is your array of field names...

Cheers,

Paul.

P.S. Your naming conventions are a tad difficult to read

[zysset]

first of all sorry my naming conventions...

hwFields is an array of my field names, except the "id" field! -> your if statement is always true!

the problem is, that when i have added a new record, the autonumber field isn't up to date after the updateBatch-call . the autonumber is physically stored in the database but not in my variables. and unfortunately a refresh causes a reset of the record pointer...

the only thing i want, is to get the primary key of my new record!

[paulw]

Now I've got you!

You will need to use refresh, but can you not use a bookmark to get back to your current position?

Cheers,

Paul.

[zysset]

Originally posted by paulw
Now I've got you!

You will need to use refresh, but can you not use a bookmark to get back to your current position?

i have to connect different tables together using foreignkeys (without gui). and i tried it this way (maybe there are more effective solutions). and yes you're right i tried to bookmark the current position but the bookmark isn't valid after the refresh call...

the code now looks like:

With ado
  .Recordset.addNew
  For i = 0 To (UBound(fields) - 1)
    .Recordset.fields(fields(i)) = col.item(fields(i))
  Next i
  .Recordset.UpdateBatch adAffectAll
  bookmark = .Recordset.bookmark
  .Refresh
  .Recordset.bookmark = bookmark
  MyId = .Recordset.fields("id").value
End With

where:
ado is a ado data control
fields is the array of field names
col is a collection with field values to enter
bookmark is a variant variable

cheers

patrick

[paulw]

OK, this one is hacking me off.

If you are adding new, how come the record is not the last record? You could set the recordset to be ORDERed by "id", then that guarantees that the latest record added will be the last. You can then use

.Refresh
.MoveLast

.GiveMeMyValuesYouBastard (This is a user defined method)

Let me know why you cannot do this ('cos I bet you thought of it).

Cheers,

Paul.

[zysset]

cause the (predefined) database uses a autonumber RANDOM ! field :-( this is why the new entry isn't at the end....

[paulw]

I hadn't even realised that you could randomize the autonumber! Can you not change that behaviour?

I checked out ADO last night and the bookmarks should not be destroyed by a refresh. Are you sure the dataset is bookmarkable?

What is the dataset SQL source? Maybe we can tweak that.

Cheers,

Paul

[zysset]

Originally posted by paulw
I hadn't even realised that you could randomize the autonumber! Can you not change that behaviour?

I checked out ADO last night and the bookmarks should not be destroyed by a refresh. Are you sure the dataset is bookmarkable?

What is the dataset SQL source? Maybe we can tweak that.

Cheers,

Paul

okay, i've killed all the records of my db, and changed the behaviour to increment. (i hope i can re-import them later)

but now there is a new problem, i can't solve:

Private Function addRecord(ByRef ado As Adodc, ByRef col As Collection, ByRef fields() As String, Optional spec As String) As Variant
'On Error GoTo AddErr
  Dim bookmark As Variant
  Dim i As Integer
  With ado
    .Recordset.addNew
    For i = 0 To (UBound(fields) - 1)
      .Recordset.fields(fields(i)) = col.item(fields(i))
    Next i
    ...

this method is called for each table. first time the method runs good, but second time the argument isn't passed!!!
i can direct call the method by <formname>.<adodc>.recordset.addnew but it isn't working with the passed reference. (nothing)

[paulw]

E-Mail me your phone number at paul.whitfield@fortisbank.com and we can talk this through - it sounds odd.

Cheers,

Paul.

P.S. I'll talk you through the re-import as well...