UPDATING A TABLE: EXPECTED FUNCTION OR VARIABLE

[bifteki via AccessMonster.com]

I have this code:

Public Function Update_Soft_Licenses_Table()
Dim sql_str As String
Dim up_from As String
Dim max_records As String

sql_str = "SELECT MAX(fld_software_item_id)" & _
"FROM dbo.tbl_soft_items"

Debug.Print sql_str
max_records = DoCmd.RunSQL(sql_str, -1)


For i = 1 To max_records
sql_str = "SELECT dbo.tbl_soft_items.fld_upgraded_from FROM dbo.
tbl_soft_items" & _
"WHERE dbo.tbl_soft_items.fld_software_item_id = " & i

Debug.Print sql_str
up_from = DoCmd.RunSQL(sql_str, -1)

If Not IsNull(up_from) Then
sql_str = "UPDATE dbo.tbl_soft_items" & _
"SET dbo.tbl_soft_items.fld_upgraded =true" & _
"WHERE dbo.tbl_soft_items.fld_sn = " & up_from

Dim sql_str As String
Dim up_from As String
End If

'If Not IsNull(DLookup("fld_sn", "tbl_soft_items",
"fld_upgraded_from_item_id=" & i)) Then

End If

End Function

When I run it, I get a Compile Error: "Expected function or variable"
Does anyone know why this happens?

 

[pietlinden]

Yes. Really simple. RunSQL is for executing _action_ queries
(updates, deletes, inserts), but *NOT* selects. Use OpenRecordset for
that. Then you can use the fields collection or whatever to get values
etc.

Look up OpenRecordset in the Help (hidden carefully under the F1 key).
Or open up NWInd and search for OpenRecordset.

 

[bifteki via AccessMonster.com]

Thanks! Though I haven't done it yet and I 'm still trying to find out how to
use it, your reply was really helpful.