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()

 

[Pat Hartman]

You can't use an object property this way. If you want to count the number of times a form is opened, add a table to the BE. Add a row for each mainform you want to log. Then run an update query when the form opens to increment the count.

Create a table:

Create a module:

Option Compare Database
Option Explicit

Public Function IncrementCount(frmName As String)
Dim strSQL As String
Dim db As DAO.Database

strSQL = "UPDATE tblFormOpenCounts SET tblFormOpenCounts.OpenCount = [OpenCount]+1"
strSQL = strSQL & " WHERE tblFormOpenCounts.FormName = '" & frmName & "';"

Set db = CurrentDb()
    db.Execute strSQL

End Function

In the Open event of EVERY form you want to log:

Private Sub Form_Open(Cancel As Integer)
    Call IncrementCount(Me.Name)
End Sub

To populate the table, you can use this query.

INSERT INTO tblFormOpenCounts ( FormName )
SELECT MSysObjects.Name
FROM MSysObjects
WHERE MSysObjects.Type=-32768;

 

[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.

 

[Pat Hartman]

Saving as a 'property' is a new concept for me and I see opportunities to save stuff here.

If this were as useful as you are envisioning, everyone would be using them. Tables store data

Don't forget, every user will have different values in the property and all will be lost whenever you push out a new version.

 

[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.

 

[Pat Hartman]

No, I'm saying that in a multi-user application, every user has their own personal copy of the FE (if they don't they need to). So, if your code updates the property on Mark's PC, the value will be different from the value on Nancy's. Then when you make changes to the FE to add a new report and distribute a new copy of the FE, your latest version will overlay the previous versions on ALL PCs and therefore reset the count to whatever it was in your test copy of the FE.

Also, what's the point in saving a piece of data that you can't ever do anything with? Are you going to collect all the FE's at some point to see who opened what?

There may be some use for the properties to control settings but as a way to collect user data? I don't think so.

 

[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.