Updating a table via Procedure (1 Viewer)

[jray9242]

I have the following code that does the following

Loop through a table that contains ".jpg". When found, run my MetaTag procedure. Next I want to update the record with the MetaTag info.

I can get the first part to work but some how can't see what I am missing to get the UPDATE query to work.

Private Sub Command50_Click()
Dim db As dao.Database
Dim rs As dao.Recordset
Dim txtTags As String
Dim strSQL As String

Set db = CurrentDb
Set rs = db.OpenRecordset("Catalog")

rs.MoveLast
rs.MoveFirst

Do While Not rs.EOF
    If InStr(1, rs!FPath, ".jpg") Then
        txtTags = getFileMetadata(GetFolder(rs!FPath), GetFileName(rs!FPath), "photo")
        Debug.Print (rs!FPath)
        Debug.Print (txtTags)
        strSQL = "UPDATE Catalog SET Catalog.Tags = " & " txtTags " & "  WHERE Catalog.FPath = rs!FPath;"
    
        CurrentDb.Execute strSQL
    End If
    
    rs.MoveNext
    
End Sub

When I run the UPDATE query, this is what I get:

"UPDATE Catalog SET Catalog.Tags =  txtTags   WHERE Catalog.FPath = rs!FPath;"

Plus I get a missing parameter error concerning the UPDATE query.

Thanks again for the help.

 

[Minty]

You'll have to concatenate the rs field value into your query string. SQL won't know what it is ditto your txtTags value. Something like;

strSQL = "UPDATE Catalog SET Catalog.Tags = '" &  txtTags  & "'  WHERE Catalog.FPath = '" &  rs!FPath & "'; "

This assumes both your values are text.

 

[The_Doc_Man]

Just as a comment, might not be a bad idea at the end of that routine to close the recordset before you exit the procedure. Just a habit of mine, but it is better to close what you open. Since the routine does the open, it should also do the close. Otherwise you will exit the app with dangling recordsets.

Your recordset variable, being an "object" pointer for a specific object type, doesn't contain the overhead of the recordset. It points to a structure that DOES hold the overhead of that recordset including pointers ("bookmarks") to where you are in terms of stepping through the records. Since the recordset is local to the routine, it SHOULD clean itself up normally, but people HAVE reported issues regarding apps that won't close correctly or don't seem to save things that should have been saved. (Admittedly not often.) Adding the rs.Close line is a small price to pay for guaranteeing that you have no dangling issues with the recordset.

Also, since you are planning to scan the recordset but not use a rs.Count property, I'm not entirely sure you need the .MoveLast followed by .MoveFirst. It might be enough to just .MoveFirst and let rs.EOF happen when it happens.

These are both just nit-picks but might be helpful down the road.

 

[jray9242]

Minty,

Your solution worked. This seems to be the hardest part for me to understand is how to concatenate the query.

THANK YOU!

The_Doc_Man

For some reason, my Loop and rs.close got deleted from my close.

Thank you for the help as well.