Results 1 to 13 of 13
  1. #1
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650

    Test for if field description exists

    This sample database contains a form that is the beginning to fix a problem in the table field descriptions.
    There is only one form, and it has one command button.

    The command button starts a procedure that looks through the tables, shows the table name and also the description for fields.
    With the on error line commented, the code stops with a run-time error if there is a missing description for a field.

    I could use the on error to get around this, but with more lines of code added to actually fix the description, the next line won't be appropriate.



    I like to be proactive with code, not reactive (as in write an error routine).
    Is there some way to test if the description property exists, so as to avoid the run-time error?

    FixItFieldDesc.zip

  2. #2
    moke123's Avatar
    moke123 is online now Me.Dirty=True
    Windows 11 Office 365
    Join Date
    Oct 2012
    Location
    Ma.
    Posts
    1,643
    Use a function to test for the property.
    If false you can then create the property in the else argument.

    Code:
    If chkPrp(tdf.Name, fld.Name) = True Then
    Code:
    Function chkPrp(tbl As String, fld As String) As Boolean
    
    
        On Error GoTo chkPrp_Error
        
        Dim chk As Variant
    
    
        chk = CurrentDb.TableDefs(tbl).Fields(fld).Properties("Description")
    
    
        chkPrp = True
        
        On Error GoTo 0
        Exit Function
    
    
    chkPrp_Error:
    
    
        If Err.Number = 3270 Then chkPrp = False: Exit Function
    
    
        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure chkPrp, line " & Erl & "."
    
    
    End Function
    If this helped, please click the star * at the bottom left and add to my reputation- Thanks

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If the field property doesn't exist you want to create it so you can set its value, yes?
    Then you need an error trap that traps that error and creates the property, then resume next to do whatever it was you're going to do. This is a common approach for built in objects that don't have a property you need.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650
    Okay thanks!
    So, it looks like I can write a generalized function for properties (by replacing "Description" with another passed argument),
    and then using that reactive code, do a proactive if.
    Very interesting (said in Wolfgang imitation voice @ Laugh-In)

  5. #5
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650
    Quote Originally Posted by Micron View Post
    If the field property doesn't exist you want to create it so you can set its value, yes?
    The idea is to fix things in the description, so if it doesn't exist, just if around the field.

  6. #6
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650
    I got to thinking. (Oh, there he goes again--I can just see the haters coming)

    If description is an option when creating table field properties, and someone doesn't enter anything,
    wouldn't it make more sense to just return a null for the property rather than crashing into a run-time error?
    I'm trying to imagine coding a RAD and saying something can exist, but later saying I'm not even going to have a placeholder for null.
    Or something like that.
    Is there a lot of this going on in Access when you get into the deeper properties and methods?
    Am I missing something that is otherwise logical?

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Logic is simple - properties don’t exist until they are created. Just like a table or query or module or report or macro or any other object. Just because you create a field in a table doesn’t mean all possible properties are created at the same time. Just when you create a database not all tables, queries et al are created

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Code:
    wouldn't it make more sense to just return a null for the property
    I do not see how you test anything about a property (such as null) if it doesn't exist. The parent is a collection AFAIK, the property is a member if it exists and isn't if not.
    Is there a lot of this going on in Access when you get into the deeper properties and methods?
    Only know about whatever properties I've ever wanted to add that don't exist. You can test using a function like the one provided but that won't create it for you. If you're going to do that in the calling code then I don't see the point of branching, especially when such functions have limited use. That's just my opinion. To that I'll add if I wonder why for such things (e.g. why doesn't the property just exist?) it is for about 2 seconds. Anything beyond that is a waste of time and effort. I'll simply employ whatever solution I find that appeals to me. My method would be
    Code:
    Dim db As DAO.Database
    Dim prop As DAO.Property
    
    On Error GoTo errHandler
    
    'next line would raise an error if "Description" didn't exist
    Set prop = db.Properties("Description")
    ' more code
    
    exitHere:
    Set db = Nothing
    Set prop = Nothing
    Exit Sub
    
    errHandler:
    If Err.Number = 3270 Then 'property doesn't exist so create it
        Set prop = db.CreateProperty("Description")'include other parameters if req'd
        db.Properties.Append prop
        Resume
    End If
    MsgBox Err.Number & ": " & Err.Description
    Resume exitHere
    
    End Sub
    With that I don't have to branch off to look at a function that might take care of that for me. Note that I modified code that was dealing with a different object and property so if you try that and it doesn't work it will likely be because of adaptation that based on me not having experience dealing with the property you're using.

    EDIT - I'll add that null should be equated to "unknown" and not some sort of value. You might be able to test a property value for null in some cases, but to return null for the property itself would be of no value even if it made sense. The takeaway on this point should be that your question relates to the property itself and not its value.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    twgonder is offline Expert
    Windows 10 Access 2016
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    650
    Okay, I get most of all that. I guess I'm just used to having "things" having a location to exist, whether used or not.
    So, isn't there a proactive way to test if a property exists other than wait for an error message?
    And then to have to test for all the possible err values?

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Updated version to allow printing to Immediate window, those Table(s) where Description property
    Exists or does NOT EXIST

    Code:
    ' ----------------------------------------------------------------
    ' Procedure Name: ckhTblDesc
    ' Purpose: To list tables where Description property Exists or doesn't Exist
    ' Procedure Kind: Sub
    ' Procedure Access: Public
    ' Parameter HasDescYN (Boolean): Parameter to select Exists or Not Exists
    ' Author: Jack
    ' Date: 30-Sep-22
    ' Usage:
    ' HasDescYN       to get tables with description
    ' HasDescYN False to get tables without description
    
    ' ----------------------------------------------------------------
    Sub ckhTblDesc(Optional HasDescYN As Boolean = True)
    10        On Error GoTo ckhTblDesc_Error
              Dim prp As Property
              Dim tdf As DAO.TableDef
    20        For Each tdf In CurrentDb.TableDefs
    30            If Not tdf.name Like "MSys*" Then
    40                For Each prp In tdf.Properties
    50                    If prp.name = "Description" Then
    60                        If HasDescYN Then
    70                            Debug.Print tdf.name & " -has a description-   " & prp.value
    80                        End If
    90                        GoTo getnextTdf
    100                   End If
    110               Next prp
    120               If HasDescYN = False Then Debug.Print "Table: " & tdf.name & " Description property does not exist"
    130           End If
    getnextTdf:
    140       Next tdf
              
    150       On Error GoTo 0
    ckhTblDesc_Exit:
    160       Exit Sub
    
    ckhTblDesc_Error:
    
    170       MsgBox "Error " & Err.Number & " (" & Err.Description & "), line " & Erl & " in Procedure ckhTblDesc" _
             & "  Module  DataDictionary "
    180       GoTo ckhTblDesc_Exit
    190       Resume
    End Sub
    Last edited by orange; 10-01-2022 at 07:04 AM.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    So, isn't there a proactive way to test if a property exists other than wait for an error message?
    I'd say that if there was you will find that documented somewhere. Since the method I posted is what I found in the M$ documentation and it's the only method I've ever seen posted anywhere else, all I can say is if there is another way I don't know of it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    So far as I know there is only one error code if a property doesn’t exist - it’s not going to have a different error if you spelt it wrong or have checked in the wrong object

  13. #13
    orange's Avatar
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with moke123, CJ and Micron---- If a property doesn't exist ==> Err 3270

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 08-19-2016, 08:16 AM
  2. Dlookup - Test if record exists in table.
    By kevinpreston in forum Access
    Replies: 3
    Last Post: 06-12-2014, 10:32 AM
  3. Replies: 4
    Last Post: 04-06-2014, 12:56 PM
  4. Trying to test if a file exists with no luck
    By jnrussell in forum Modules
    Replies: 3
    Last Post: 02-23-2014, 08:40 PM
  5. How to test of record exists in table?
    By tdaccess in forum Access
    Replies: 3
    Last Post: 04-13-2011, 10:22 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums