Updating a Public Variable stored in a table (1 Viewer)

[arronjuk]

Hi

I am currently trying add variables into my database. I have managed it by storing the data in a table in the backend database. I also have a function to retrieve the data.

This all works, until I try to build a form that updates the data.

The structure of the table is, 3 fields (1. ID, 2.DATA, 3.Description)

I then reference it by the ID number.

When I use the below code to update the DATA in row ID# with a specific piece of data. I get an error message saying "Run-time error '3219': Invalid Operation".

Sub UpdateMyCompany(ID As Integer, NewData As String)
Dim myDb As Database, MySet As DAO.Recordset
Set myDb = CurrentDb()
Set MySet = myDb.OpenRecordset("TBL_MyCompany", dbOpenTable)
MySet.Index = "PrimaryKey"
MySet.Seek "=", ID
MySet.Edit
MySet!Data.Value = NewData
MySet.Update
Debug.Print "UpdateKeyData: Record " & Format(ID, "#") & " changed to " & NewData
MySet.Close
End Sub

This is a new subject to me, so any help is appreciated!

Thanks,

Arron

 

[Banana]

Off the cuff, but I think you can't do any updates to a recordset with type dbOpenTable; which is basically read-only. You need a dbOpenDynaset.

 

[LPurvis]

Table type recordsets are updateable enough.
I'd imagine that you are, however, violating their one driving requirement - that the table is local to the database object.
i.e. you're opening TBL_MyCompany - it's a linked table huh?
You need to open the database explicitly
i.e. instead of
Set myDb = CurrentDb()
use
Set db = OpenDatabase(Mid(CurrentDb("TBL_MyCompany").Connect, 11))

Or use a method other than Seek. (Although it's efficient - it isn't necessarily the way you want to go).

Findfirst is the obviously (slower but available on linked tables) alternative.
However the most obvious course is just standard limited source recordset opening:

Sub UpdateMyCompany(ID As Integer, NewData As String)
 
    Dim myDb As Database, MySet As DAO.Recordset
 
    Set myDb = CurrentDb
    Set MySet = myDb.OpenRecordset("SELECT * FROM TBL_MyCompany WHERE ID = " & ID, dbOpenDynaset)
    With MySet
        If Not .RecordCOunt = 0 Then
            .Edit
                !Data.Value = NewData
            .Update
            Debug.Print "UpdateKeyData: Record " & Format(ID, "#") & " changed to " & NewData
        End If
        .Close
    End With
End Sub

 

[Pat Hartman]

If you are using this code in a form bound to the data you are trying to update, you are doing a lot of work for nothing. Bound forms automatically update changed data, you don't need any code. Also, when you write code that updates the the bound record by using a recordset, you are actually conflicting with yourself.

 

[arronjuk]

Thanks Lpurvis! ! ! Just tried your solution and it worked perfectly!

Really appreciate it !

Arron

 

[LPurvis]

No problem.

I take it that this isn't a similarly bound form then as Pat righty asks?
(It would be a bit mad to attempt to do so anyway - since you already have the data loaded locally at that time ;-)

Just to mention - there are, more abbreviated, alternatives open to you.
e.g.

Sub UpdateMyCompany(ID As Integer, NewData As String)

    With CurrentDb
        .Execute "UPDATE TBL_MyCompany SET [Data] = '" & Replace(NewData, "'", "''") & "' WHERE ID = " & ID
        If .RecordsAffected > 0 Then
            Debug.Print "UpdateKeyData: Record " & Format(ID, "#") & " changed to " & NewData
        End If
    End With

End Sub

Which is just a quick and dirty direct update on the data.
The difference is that the recordset method could let you check to see if you even need to alter the [Data] field at all. The Update method will just determine if there was a matching row for [ID] - which was duly updated regardless.

Cheers.