How to Add, Edit Records programmatically?

[spudpeel]

Im still finding problems in my document control system, and have arrived at one of the most important bits. (Im completely new to access so Ive been learning as I go).
Part of the system involves adding a new version of a document to the database, by setting the old one to obselete and adding a record of the new one. To reduce the likelihood of typos I made it so that the Name of the document is picked from a list of the existing records in the database. At present I just allow additions and close the window to add a record, or Me.Undo and then Exit If I do not want to keep the new record, but I think this is a bit cumbersome and probably quite clumsy. How would I correctly add a new record, AND edit the old one?
Any help would be very much appreciated!
Thanks

[Stwange]

You could leave the fields unbound, using a recordset to fill them, eg.

dim rs as DAO.recordset
set rs = DBEngine(0)(0). openRecordSet(" SELECT * FROM yourTable WHERE yourTable.[ID] = '" & whatever & "';)

then you refer to each of the fields from this recordset like so:
if not rs.EOF then
txtFilePath.val ue = rs!path 'whatever comes after rs! must match the field exactly.
etc.
end if

Then when it comes to adding the new record, and updating the old one:
'INSERT:
docmd.runsql("I NSERT INTO yourTable VALUES(" & each of the field names here. The fields must appear IN THE SAME ORDER AS DESIGNED IN YOUR TABLE, if you don't want to add a particular one, use '', and seperate them by commas & ");")

'eg INSERT INTO yourTable VALUES('1', 'C:\filepath.do c', '2');

'UPDATE PREVIOUS TABLE:
docmd.runsql("U PDATE yourTable SET obsolete = Yes (or 1) WHERE ID = " & ID & ";")

Hope this helps, and isn't too confusing. There's probably a better way of doing it, but I find using unbound fields, albeit taking a little longer (not too much), gives you a lot more control of the form.

If you need any more help from me, please give me more details as to how your tables and forms are laid out and what they need doing exactly.

-James

[spudpeel]

Thats awesome! Thanks very much, and thanks for offering further help, I may probably take you up on your offer!