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
    [B]GetCustomProp = CurrentDb.Containers("Databases").Documents("UserDefined").Properties(prpName).Value[/B]
    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("<Na me>"). 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 programmaticall y 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]

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 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]

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)

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 "traditiona l programming" you speak of?

[NeoPa]

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?

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.

And, what is this "traditiona l programming" you speak of?

Just what one most frequently finds in example code from MS etc on the web.

[ADezii]

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 "traditiona l programming" you speak of?

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