Solved Cannot Update Database Property.

[HillTJ]

Friends, I wish to increment a counter each time my main form is opened to track usage. I've Managed to save my usage counter & recall it to an unbound textbox using

Public Function Usage() ' recalls usage counter value from database properties
        Dim DB As Database
        Set DB = DBEngine(0)(0)
        Usage = DB.Properties![UsageCounter]
End Function

I populate the 'usage' on the form thus, NewCounter = Usage()

Now I wish to increment it by 1 and resave the result.

I've tried to adapt the following Allen Browne code which i run from the on_load event of my form;

Function Update(filename As String)
        Dim DB As dao.Database
        DB.Properties![UsageCounter] = NewCounter + 1
        DB.Close
    End Function

It doesn't work. Displays Run-Time error 91 'Object Variable or with block variable not set'

I don't understand the code nor the error message.

Please help.

 

[arnelgp]

did you First saved the property?
if you did, try:

Public Function Usage()
        Dim DB As DAO.Database
        Dim prp As DAO.Property
On Error GoTo ErrHandler
        Set DB = CurrentDb
        Usage = DB.Properties("UsageCounter").Value
ExitFunc:
        Set DB = Nothing
        Exit Function

ErrHandler:
       If Err.Number = 3270 Then
              'The property was not found; create it
             Set prp = DB.CreateProperty(Name:="UsageCounter", Type:=dbLong, Value:=1)
              DB.Properties.Append prp
              Usage = 1
              Resume Next
        End If
End Function


Function Update(filename As String)

        Dim DB As DAO.Database
        Dim prp As DAO.Property
        Set DB = CurrentDb
On Error GoTo ErrHandler
        With DB.Properties("UsageCounter")
            .Value = .Value + 1
            Update = .Value
        End With
        
ExitFunc:
        Set DB = Nothing
        Exit Function

ErrHandler:
       If Err.Number = 3270 Then
              'The property was not found; create it
             Set prp = DB.CreateProperty(Name:="UsageCounter", Type:=dbLong, Value:=1)
              DB.Properties.Append prp
              Update = 1
              Resume Next
        End If
End Function

 

[Gasman]

Friends, I wish to increment a counter each time my main form is opened to track usage. I've Managed to save my usage counter & recall it to an unbound textbox using

Public Function Usage() ' recalls usage counter value from database properties
        Dim DB As Database
        Set DB = DBEngine(0)(0)
        Usage = DB.Properties![UsageCounter]
End Function

I populate the 'usage' on the form thus, NewCounter = Usage()

Now I wish to increment it by 1 and resave the result.

I've tried to adapt the following Allen Browne code which i run from the on_load event of my form;

Function Update(filename As String)
        Dim DB As dao.Database
        DB.Properties![UsageCounter] = NewCounter + 1
        DB.Close
    End Function

It doesn't work. Displays Run-Time error 91 'Object Variable or with block variable not set'

I don't understand the code nor the error message.

Please help.

I do not understand?
How did you manage to save it in the first place?

I always prefix my form controls with Me
Is usage a Public variable as you have not declared it locally.?
Do you use Option Explicit?

 

[HillTJ]

Guys, yes i have saved the property. Ran code to save property as per Allen Browne's example. Will give @arnelgp 's code a go. Thanks

 

[Gasman]

Guys, yes i have saved the property. Ran code to save property as per Allen Browne's example. Will give @arnelgp 's code a go. Thanks

Well all arnel's code does is create the property if it does not already exist?
You say yours already exists?
Perhaps it is because you are no using the .Value property?

However I had code, used ages ago, which I am sure worked, and that did not use that property either?

Public Function SetProperties(PropName As String, PropType As Variant, PropValue As Variant) As Integer
    On Error GoTo Err_SetProperties
    Dim db As Database, prop As Property

    Set db = CurrentDb
    db.Properties(PropName) = PropValue
    SetProperties = True
    Set db = Nothing
Exit_SetProperties:
    Exit Function

Err_SetProperties:
    If Err = 3270 Then
        Set prop = db.CreateProperty(PropName, PropType, PropValue)
        db.Properties.Append prop
        Resume Next
    Else
        SetProperties = False
        MsgBox "runtime Error #" & Err.Number & vbCrLf & vbLf & Err.Description
        Resume Exit_SetProperties
    End If
End Function

 

[HillTJ]

@Gasman, thanks i'm sure i've created the property as i can recall it. Just cannot update it. I like your and @arnelgp 's code and will adapt to suit my purpose. I've not played with database properties before. New to me. Thanks

 

[HillTJ]

@arnelgp , a queston. When i run function Update (filename as string) do i need to pass in the database name? Eg Update("example.accdb") ?

 

[theDBguy]

Hi. I think the code you posted earlier to update the property is missing a line like.

Set DB = CurrentDb()

 

[HillTJ]

@Pat Hartman , Pat in essence, this is what I've been doing. I like the idea of saving the 'counter' value outside a table. Saving as a 'property' is a new concept for me and I see opportunities to save stuff here.

 

[HillTJ]

So, Pat you raise an important question & have opened my eyes!. If I say, make changes then save as a different filename I'd loose the values stored as a 'property'? I think that's how I read your response. If that's the case saving to a table seems a bit better!! Never mind, at least I've learn 't a bit about properties (you know, I've been researching for about a week and I never came across what you pointed out). Thanks for putting me back on the straight & narrow.

 

[HillTJ]

@Pat Hartman , I understand. Better to keep count in a form as you suggest.

 

[jdraw]

HillTJ,

You may find Daniel Pineault's recent youtube video on Database Properties informative. I agree with Pat's suggestion to use a Table for your formc ounts.