469,898 Members | 1,418 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,898 developers. It's quick & easy.

Getting exception in method GetCustomProp(prpName As String)

3
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
Expand|Select|Wrap|Line Numbers
  1. Function GetCustomProp(prpName As String) As Variant
  2.     On Error Resume Next
  3.     GetCustomProp = CurrentDb.Containers("Databases").Documents("UserDefined").Properties(prpName).Value
  4.     If Err.Number <> 0 Then GetCustomProp = Null
  5. End Function
This method is called in the form_load method
Expand|Select|Wrap|Line Numbers
  1. UpdateRating GetCustomProp("Rating")
In this method, GetCustomProp having a empty value, because of that is giving error.

Can any one explain me this statement
Expand|Select|Wrap|Line Numbers
  1. 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
Attached Files
File Type: txt feeback form code.txt (1.3 KB, 402 views)
Nov 18 '09 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. Dim intCounter As Integer
  2. Dim intPropNum As Integer
  3.  
  4. intPropNum = CurrentDb.Containers("Databases").Documents("UserDefined").Properties.Count
  5.  
  6. For intCounter = 0 To intPropNum - 1
  7.   Debug.Print "Property Name: " & _
  8.                CurrentDb.Containers("Databases").Documents("UserDefined").Properties(intCounter).Name & _
  9.                " | Property Value: " & CurrentDb.Containers("Databases").Documents("UserDefined").Properties(intCounter).Value
  10. 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:
    Expand|Select|Wrap|Line Numbers
    1. Property Name: Name | Property Value: UserDefined
    2. Property Name: Owner | Property Value: admin
    3. Property Name: UserName | Property Value: admin
    4. Property Name: Permissions | Property Value: 0
    5. Property Name: AllPermissions | Property Value: 65536
    6. Property Name: Container | Property Value: Databases
    7. Property Name: DateCreated | Property Value: 11/18/2009 1:24:01 PM
    8. Property Name: LastUpdated | Property Value: 11/18/2009 1:24:01 PM
    9. Property Name: NWVersion | Property Value: 8.0
    10. Property Name: Rating | Property Value: Excellent
    11.  

9 2175
ADezii
8,800 Expert 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. Dim intCounter As Integer
  2. Dim intPropNum As Integer
  3.  
  4. intPropNum = CurrentDb.Containers("Databases").Documents("UserDefined").Properties.Count
  5.  
  6. For intCounter = 0 To intPropNum - 1
  7.   Debug.Print "Property Name: " & _
  8.                CurrentDb.Containers("Databases").Documents("UserDefined").Properties(intCounter).Name & _
  9.                " | Property Value: " & CurrentDb.Containers("Databases").Documents("UserDefined").Properties(intCounter).Value
  10. 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:
    Expand|Select|Wrap|Line Numbers
    1. Property Name: Name | Property Value: UserDefined
    2. Property Name: Owner | Property Value: admin
    3. Property Name: UserName | Property Value: admin
    4. Property Name: Permissions | Property Value: 0
    5. Property Name: AllPermissions | Property Value: 65536
    6. Property Name: Container | Property Value: Databases
    7. Property Name: DateCreated | Property Value: 11/18/2009 1:24:01 PM
    8. Property Name: LastUpdated | Property Value: 11/18/2009 1:24:01 PM
    9. Property Name: NWVersion | Property Value: 8.0
    10. Property Name: Rating | Property Value: Excellent
    11.  
Nov 18 '09 #2
asifu9
3
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
Nov 18 '09 #3
ChipR
1,287 Expert 1GB
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:
Expand|Select|Wrap|Line Numbers
  1. Sub UserProps()
  2.  
  3.    Dim db As Database
  4.    Dim prpLoop As Property
  5.  
  6.    Set db = CurrentDb
  7.  
  8.    With db.Containers!Databases.Documents("UserDefined")
  9.          Debug.Print "Properties of " & .Name & " document"
  10.          On Error Resume Next
  11.          For Each prpLoop In .Properties
  12.             Debug.Print "  " & prpLoop.Name & " = " & _
  13.                prpLoop
  14.          Next prpLoop
  15.          On Error GoTo 0
  16.    End With
  17.  
  18.    db.Close
  19.  
  20. End Sub
  21.  
Never mind, ADezii beat me to it.
Nov 18 '09 #4
ADezii
8,800 Expert 8TB
@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.
Nov 18 '09 #5
ChipR
1,287 Expert 1GB
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
Expand|Select|Wrap|Line Numbers
  1. UpdateRating Nz(GetCustomProp("Rating"), 0)
Nov 18 '09 #6
ADezii
8,800 Expert 8TB
@ChipR
Just as a side-note, ChipR, traditional programming checks for an Error Code of 3270 (Property Not Found), then Dynamically creates it:
Expand|Select|Wrap|Line Numbers
  1. If Err.Number = 3270 Then       'Property not found
Nov 18 '09 #7
ChipR
1,287 Expert 1GB
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?
Nov 18 '09 #8
NeoPa
32,231 Expert Mod 16PB
@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.
Nov 18 '09 #9
ADezii
8,800 Expert 8TB
@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.
Expand|Select|Wrap|Line Numbers
  1. Function GetCustomProp(prpName As String) As Variant 
  2.     On Error Resume Next 
  3.     GetCustomProp = CurrentDb.Containers("Databases").Documents("UserDefined").Properties(prpName).Value 
  4.     If Err.Number <> 0 Then GetCustomProp = Null 
  5. End Function 
Nov 18 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Vikram | last post: by
3 posts views Thread by neoswf | last post: by
1 post views Thread by Michael Hesse | last post: by
1 post views Thread by MAF | last post: by
3 posts views Thread by =?Utf-8?B?c3BkMzAwMQ==?= | last post: by
reply views Thread by j.lendholt | last post: by
1 post views Thread by Waqarahmed | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.