Getting exception in method GetCustomProp(prpName As String)

[asifu9]

Hello all,

I am new to MS Access VBA, i am using 2007 version.

I have a form called feedback, in that load event we calling a method, in that method statement a bug is coming.

here is the method code

Function GetCustomProp(prpName As String) As Variant
    On Error Resume Next
    GetCustomProp = CurrentDb.Containers("Databases").Documents("UserDefined").Properties(prpName).Value
    If Err.Number <> 0 Then GetCustomProp = Null
End Function

This method is called in the form_load method

UpdateRating GetCustomProp("Rating")

In this method, GetCustomProp having a empty value, because of that is giving error.

Can any one explain me this statement

GetCustomProp = CurrentDb.Containers("Databases").Documents("UserDefined").Properties(prpName).Value

what actually is this code. I am working on a project which is build by some one else. actually i dont know what actually is this line of code is doing.

I had attached the whole page code with this issue. please have a look at this attachment.

Thanks in advance!
Asif

[ADezii]

GetCustomProp() is a Function that retrieves the Value of the User Defined Database Property passed to it. A User Defined Property doesn't exist until you request Access to create it, or you create it. The syntax for referring to these Properties is: Properties("<Name>"). The code for retrieving all User Defined Properties for the Current Database is:

Dim intCounter As Integer
Dim intPropNum As Integer
 
intPropNum = CurrentDb.Containers("Databases").Documents("UserDefined").Properties.Count
 
For intCounter = 0 To intPropNum - 1
  Debug.Print "Property Name: " & _
               CurrentDb.Containers("Databases").Documents("UserDefined").Properties(intCounter).Name & _
               " | Property Value: " & CurrentDb.Containers("Databases").Documents("UserDefined").Properties(intCounter).Value
Next

You can manually or programmatically Add a Database User Defined Property. I manually added a 'Rating' User Defined Property with a Value of Excellent in the following manner:

• File
• Database Properties
• Custom Tab
• Typed Rating in the Name Text Box
• Kept the Type as Text
• Set the Value as Excellent
• OK
• I then ran the above listed code to obtain the following results which will be different from your Database:
  

  Property Name: Name | Property Value: UserDefined
  Property Name: Owner | Property Value: admin
  Property Name: UserName | Property Value: admin
  Property Name: Permissions | Property Value: 0
  Property Name: AllPermissions | Property Value: 65536
  Property Name: Container | Property Value: Databases
  Property Name: DateCreated | Property Value: 11/18/2009 1:24:01 PM
  Property Name: LastUpdated | Property Value: 11/18/2009 1:24:01 PM
  Property Name: NWVersion | Property Value: 8.0
  Property Name: Rating | Property Value: Excellent
   

[asifu9]

Hi ADezii,

thanks a lot for clarifying the code, but i am wondering where to create this database property varialble.
As i am using MS Access 2007, i have set of tables, it would be greate if u guide me how to create this variable.

Again, thanks a lot.
Asif

[ChipR]

It took a little digging, but I found this article on Microsoft Support which explains everything: Using DAO to Set and Retrieve Custom Database Properties
I have not yet determined the latest version to which it applies.
I suspect that the custom property you name does not exist, but you can determine that with this code modified from msdn.microsoft.com:

Sub UserProps()
 
   Dim db As Database
   Dim prpLoop As Property
 
   Set db = CurrentDb
 
   With db.Containers!Databases.Documents("UserDefined")
         Debug.Print "Properties of " & .Name & " document"
         On Error Resume Next
         For Each prpLoop In .Properties
            Debug.Print "  " & prpLoop.Name & " = " & _
               prpLoop
         Next prpLoop
         On Error GoTo 0
   End With
 
   db.Close
 
End Sub
 

Never mind, ADezii beat me to it.

[ADezii]

@asifu9
ChipR provided a nice Reference for you to use - nice job ChipR! The only problem that I see is that the Linked Code does not check to see if the Property currently exists, which ChipR already addressed.

[ChipR]

A good solution would handle the possibility that the property does not exist by checking the return value of the function. Just from looking at the code, maybe something as simple as

UpdateRating Nz(GetCustomProp("Rating"), 0)

[ADezii]

@ChipR
Just as a side-note, ChipR, traditional programming checks for an Error Code of 3270 (Property Not Found), then Dynamically creates it:

If Err.Number = 3270 Then       'Property not found

[ChipR]

Thanks Adezii. I know very little about the Err object.
Is it a global object that would be available to check in Form_Load after it returns from the GetCustomProp function, should this be checked inside the function, or is there a way to propogate it out of the function?

And, what is this "traditional programming" you speak of?

[NeoPa]

@ChipR
The Err object is indeed global. It is always available, but as it is set (reset) after most operations, it is necessary to jump on it quickly if you want an accurate reflection of what it tells you. IE. It is easy to have code that resets it as a lead-up to displaying it for the operator. F1 on the word Err in the VBA editor window will give you a fuller description.
@ChipR
Just what one most frequently finds in example code from MS etc on the web.

[ADezii]

@ChipR
The Function GetCustomProp() actually does this, but in a different manner:

1. The Value of the User Defined Property is assigned to the Function.
2. If the Property doesn't exist (hasn't been created), Error 3270 is generated.
3. Because of the previously defined On Error Resume Next Statement, code execution continues to the next line.
4. If any Error is generated (If Err.Number <> 0), the Function simply returns NULL without giving the User the chance to create the Property.

Function GetCustomProp(prpName As String) As Variant 
    On Error Resume Next 
    GetCustomProp = CurrentDb.Containers("Databases").Documents("UserDefined").Properties(prpName).Value 
    If Err.Number <> 0 Then GetCustomProp = Null 
End Function