Appending propertly to column not working ACCESS 2010

I need to append a displycontrol property to a newly created column in a table.  This is done with vba and I have the below, but it is not recognizing the table name.  


Set prp = CurrentDb.tblPerson("AAPerson").CreateProperty("DisplayControl", dbInteger, acCheckBox)
AAPerson.Properties.Append prp
Sandra SmithRetiredAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
What error message are you actually getting?
NorieAnalyst Assistant Commented:
Have you looked at TableDefs?

PS Sorry for the disjointed post, on phone.
Sandra SmithRetiredAuthor Commented:
that property already existed, so I changed it to be the below.  I want the display for the YesNo to be a checkbox. It goes through without throwing an error, but does not change the displaycontrol to a check box, it is still text.

CurrentDb.TableDefs("tblPerson").Fields("AAPerson").Properties("DisplayControl") = acCheckBox
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Sandra SmithRetiredAuthor Commented:
Norie, no problem, so am I!
Nick67Commented:
You are planning to do a whack of this stuff.
I think firing up TableDefs is going to be a better fit
I don't do a lot of DDL, but I think this is close
You need a tabledef, then the field, and you create the property of the field
Allen Browne's done the heavy lifting here
http://allenbrowne.com/func-dao.html#SetPropertyDAO

Dim TD As TableDef
dim prp as Property
dim fld as field
Set TD = CurrentDb.TableDefs("tblPerson")
set fld as TD.fields("AAPerson")
fld.Properties.Append fld.CreateProperty("DisplayControl", dbInteger, acCheckBox)

If it exists, then you just need to change it's value
fld.Properties("DisplayControl") =acCheckBox
Sandra SmithRetiredAuthor Commented:
Yes, I came to that conclusion to created the below.  It is based on a procedure that does work, but now I get an error message Property Not Found, but the column does exist in the table, but with a text box for the YesNo.

Public Sub TestAAPerson()
On Error GoTo ErrorHandler
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strTableName As String
    Dim rst As DAO.Recordset
    Dim i As Integer
   
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblPerson1")
        With rst
            For i = 1 To .Fields.Count - 1
                   If .Fields(i).Name = "AAPerson" Then    'Only modify one field
                        .Fields(i).Properties("DisplayControl") = acCheckBox
                   End If
             Next i
        End With

Exit_Errorhandler:
rst.Close
Set rst = Nothing
Set tdf = Nothing
Set dbs = Nothing
   
    Exit Sub
ErrorHandler:
    MsgBox "Error Number: " & Err.Number & " Description: " & Err.Description
    Resume Exit_Errorhandler
End Sub
Sandra SmithRetiredAuthor Commented:
I tried the below and still does notwork
Public Sub TestAAPerson()
'On the tblPerson, want to change DisplayControl to a checkbox rather
'than a text box for the YesNo column type.
On Error GoTo ErrorHandler
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim strTableName As String
    Dim rst As DAO.Recordset
    Dim i As Integer
    Dim fld As DAO.Field
   
Set dbs = CurrentDb
Set tdf = dbs.TableDefs("tblPerson")
'Set fld = tdf.Fields("AAPerson")
Set rst = dbs.OpenRecordset("tblPerson1")
        With rst
            For i = 1 To .Fields.Count - 1
                   If .Fields(i).Name = "AAPerson" Then    'Only modify Text fields
                   Set fld = tdf.Fields("AAPerson")
                   Call SetPropertyDAO(fld, "DisplayControl", dbInteger, CInt(acCheckBox))
                   Exit Sub
                   End If
             Next i
        End With

Exit_Errorhandler:
rst.Close
Set rst = Nothing
Set tdf = Nothing
Set dbs = Nothing
   
    Exit Sub
ErrorHandler:
    MsgBox "Error Number: " & Err.Number & " Description: " & Err.Description
    Resume Exit_Errorhandler
End Sub
Nick67Commented:
After some hacking, this worked
Why the set statements had to go in favor of walking the collection to get it to work is a bit of a mystery

Option Compare Database
Option Explicit

Private Sub Command0_Click()
Dim TD As TableDef
Dim prp As Property
Dim fld As Field
For Each TD In CurrentDb.TableDefs
    If TD.Name = "tblPerson" Then
        For Each fld In TD.Fields
            If fld.Name = "AAPerson" Then
                If HasProperty(fld, "DisplayControl") = False Then
                    fld.Properties.Append fld.CreateProperty("DisplayControl", dbInteger, acCheckBox)
                Else
                    fld.Properties("DisplayControl") = acCheckBox
                End If
            End If
        Next fld
    End If
Next TD

 
End Sub

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
    'Purpose:   Return true if the object has the property.
    Dim varDummy As Variant
    
    On Error Resume Next
    varDummy = obj.Properties(strPropName)
    HasProperty = (Err.Number = 0)
End Function

Open in new window

NorieAnalyst Assistant Commented:
@Nick67, just a thought but instead of looping couldn't you use something like this?
Set TD = CurrentDb!tblPerson

Open in new window

If that works for the TableDef then perhaps something similar would work for the field.
Nick67Commented:
More hacking made this work.
Note the need to actually instantiate a database object
CurrentDb just doesn't cut it for some reason.
Working sample posted

Dim db As Database
Dim TD As TableDef
Dim prp As Property
Dim fld As Field


Set db = CurrentDb
Set TD = db.TableDefs("tblPerson")
'here I am going to rub out AAPerson to get a clean slate
TD.Fields.Delete "AAPerson"
'here I'll append it as a Boolean field
TD.Fields.Append TD.CreateField("AAPerson", dbBoolean)
'and then make it a checkbox
Set fld = TD.Fields("AAPerson")
If HasProperty(fld, "DisplayControl") = False Then
    fld.Properties.Append fld.CreateProperty("DisplayControl", dbInteger, acCheckBox)
Else
    fld.Properties("DisplayControl") = acCheckBox
End If

'------------------------------------

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
    'Purpose:   Return true if the object has the property.
    Dim varDummy As Variant
    
    On Error Resume Next
    varDummy = obj.Properties(strPropName)
    HasProperty = (Err.Number = 0)
End Function

Open in new window

test.mdb
Sandra SmithRetiredAuthor Commented:
Will try these first thing tomorrow, done with this for today out of sheer frustration and am heading home.
Nick67Commented:
Ok,

I urge you to look over what Allen Browne has done here, if you haven't already
http://allenbrowne.com/func-dao.html
He creates the field and then keeps that object to do everything else with.
We started with the field already created by an Alter Table, so that field object has to be re-gained to make his code go.

You can dump all that code of his into a module and call most of what he has there straight-up
It's odd that CurrentDb was the issue, but it clearly was.
Once I instantiated that, it all flowed nicely
Before that, in the code block of #41138984, the Set statements made errors fly.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sandra SmithRetiredAuthor Commented:
Sorry have not worked on this, some medical issues came up.  At least this has gotten resolved, thank you.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.