Simple Question - changing value of a field in a table

[Mike3411]

Hello, I have a short question about updating a value from a form. My form is used to check books out from a library db, and everything works peachy except that I am currently using a checkbox to denote checkout status. That means the user pulls up the book (which is stored in the table bookList) then enters information about who is checking it out, then clicks the check button to set the checkedOut value in the bookList for that book to TRUE. I want to change the form so that instead of the checkbox, there is a button that, when clicked, sets the checkedOut value to TRUE, then saves the record, then advances to a new record (each transaction is stored in a seperate table, transactionHistory). I can get the button to do the second two things, but I don't know how to get it to set the checkedOut value. What is the best way to do this? I found some other code here about opening the db & modifying the value, here is how I tried to use it:

Set rst = New ADODB.Recordset 'Instantiate a new recordset
With rst
.Open "bookList", CurrentProject.Connection, adOpenDynamic 'Open recordset against your table

.AddNew 'Add new record
.Fields("checkedOut") = "True" 'Set values of the various fields
'etc
.Update 'Commit the change
End With

rst.Close 'Always explicitly close your recordsets...
Set rst = Nothing '...and release the reference



However, with this code I get the error:
"Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype."


Is there a different way to do this? Or am i just messing up the code? Would something like DoCmd.SelectObject, select the radio button, and set TRUE work? Any suggestions would be appreciated.
Thanks
-Mike

 

[Pat Hartman]

Several problems:
1. Append adds a new ROW to a table. Update updates the value in an existing row. Appending a new row with only a flag value doesn't accomplish anything.
2. A flag that just indicates a book is checked out is not really useful. It would be much more useful to use a Date field so that you know when the book was checked out. The Date field can be used like a flag by simply checking it for Null. The date will be null when the book is in the library but not null when it has been checked out.
3. You don't need to use ADO at all. The record you want to update is already open on the current form. It only takes a single line of code -
Me.CheckoutDate = Date()

 

[Mike3411]

thank you, that worked perfectly. i know that a date would be more useful, but the way i've set up the database is to store the date information in a different table. technically i don't think i need the flag in the booklist table, but it provides a second location to store the information for better accountability (sort of). I'm not sure if the design is great, but as you can probably tell I have very little experience with Access and it's about all I can manage.
Thanks,
Mike

 

[Pat Hartman]

If you are keeping a history of check out/in activity in a separate table, the flag in the book record is redundant and violates second normal form.