By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,777 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

How to Add, Edit Records programmatically?

P: 8
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!
Aug 16 '07 #1
Share this Question
Share on Google+
2 Replies

Expert 100+
P: 126
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.value = rs!path 'whatever comes after rs! must match the field exactly.
end if

Then when it comes to adding the new record, and updating the old one:
docmd.runsql("INSERT 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.doc', '2');

docmd.runsql("UPDATE 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.

Aug 16 '07 #2

P: 8
Thats awesome! Thanks very much, and thanks for offering further help, I may probably take you up on your offer!
Aug 16 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.