Updating Fields DAO

[caraprice]

Hi,

When trying to update a table with certid as primary key (Access), the field certid field cannot be updated. However if the line !certid = val(txtfields(0)) is commented out the updating line will overwrite the first 0 certid (or the first certid).
Please help, ay help will be very much appreciated. Thanks, in advance for any help.

Code:
Dim RecSource As Recordset
Dim RecDatabase As Database

Set RecDatabase = OpenDatabase("c:\SRS\SRS_Lic.mdb")
Set RecSource = RecDatabase.OpenRecordset("Certificate Details", dbOpenDynaset)

' Update new record
With RecSource
.Edit
!CertID = Val(txtFields(0)) ' this is the line which is causing the problems
!CertType = txtFields(1)
!CertNo = txtFields(2)
!CertExpiry = txtFields(3)
!DriverID = Val(txtFields(4))
.Update
.Bookmark = .LastModified
End With

RecSource.Close
RecDatabase.Close

[gab2001uk]

If your CertID field is an Autonumber then you cannot edit it.
If not, and you .AddNew, without writing the value into the database, it will take the default value of 0.
If you are editing an existing record, why do you want to change the CertID anyway?

[caraprice]

Hi,

I am not trying to add a new certid, rather allowing the user to edit the record saved with the current certid.

Certid is setup as an autonumber, which the user does not see, not have access to. The record which they want to alter is loaded via a grid.

[Psycho_NZ]

hiya,

I dont know if u've already solved this one, but if not, i think it's clearing the first one because u're updating straight after u open the recordset, so it is still pointing to that first certID, to fix it u'll have to go thro the records to the one u want to update, and when u find the one you want to change, then do the update

coolcool?
Ang

[gab2001uk]

You have answered your own question

!CertID = Val(txtFields(0)) ' this is the line which is causing the problems

This is because you are trying to write to an Autonumber field in this line.
As stated by Psycho_NZ you need to move to another record before editing. Use either the .Seek (DAO/ADO) or .Find(First) (ADO/DAO) methods