In fact, here is how I would do this in ADO:

Dim rst As ADODB.Recordset
Dim sSQL, sISBN as String

If IsNull(Me.ISBN) Or Me.ISBN = "" Then
   MsgBox "Please enter an ISBN", vbCritical
Else
   sISBN = Me.ISBN
End If

sSQL = "SELECT * FROM tbl_Books WHERE [ISBN] = '" & sISBN & "'"

Set rst = New ADODB.Recordset
rst.Open sSQL, CurrentProject.Connecton, adOpenDynamic, adLockOptimistic

rst("Book_Title") = Me.Book_Title
rst("Publisher") = Me.Publisher
rst("Copyright") = Me.Copyright
rst("Edition") = Me.Edition
rst("Author") = Me.Author
rst("Book_Type") = Me.Book_Type
rst("Trial_Software") = Me.Trial_Software
rst("Comments") = Me.Comments
rst.Update
rst.Close

HTH


mmcdonal

I guess the upshot is that ISBN's are unique, so they are probably your PK, and as such are indexed with no dupes. In that case, FindFirst is superfluous if you parameterize the recordset.



mmcdonal

I like that code, but it keeps throwing an error message for the MsgBox portion of the code. So, I fixed that but then started getting this error for this line of code:
The message said "Object doesn't support this property or method."

Type the code in by hand rather than pasting, and see what autocomplete options you get. Then use the autocomplete options.

mmcdonal

Okay, I did the code by hand but now it does not look up anything. Any thoughts?

When you say it does nothing now, how far does it get?
I mean, you want to use an ISBN (unique identifier) on your form, use it to pull its record from the proper table, then update the record in the table based on data recently typed in the form.
So you take the ISBN in the variable (it is a string or number in the database?), add it to the sSQL string, and pull the record.

Are you saying it is not pulling any record? Or it is throwing an error?


mmcdonal

Sorry, I was not clear. It is not pulling any records now. When I switched out the code for the code below, it stopped pulling any records. My bad on the bad communication.

There may be parameter issues with type mismatches or something. Can you either enter a parameter you know is there, or remove the parameter, and the update, and have it give you the recordcount boolean, like:

Dim bCount as Boolean

sSQL = "SELECT * FROM tbl_Books"

rst.Open etc.

bCount = rst.Recordcount

Msgbox bCount

See if this doesn't come up with a 0





mmcdonal

Okay, here is something else I discovered. When it is looking up the record, it is blanking all the values out. So, it looks like it is updating all the fields with the blanks instead of just looking up the information.

I checked this by actually looking in the table itself to make sure everything is going correct and low and behold.

This is a problem with taking values at runtime. I would suggest taking the form field values in seperate variables first, then open the recordset, then paste the values from the variables, like:

Dim sAuthor As String

sAuthor = Me.Author

rst("Author") = sAuthor

That is how I usually do it.



mmcdonal