Setting the Display Control property of a table field to Check Box

The following code is my attempt to set the Display Control property = "Check Box" for a table field named MyCheckBox which I am adding to the table trefLinkList. I get the error Data Type Mismatch on the line that is supposed to set the DisplayControl property:

 Set prp = tdf.Fields("MyCheckbox").CreateProperty("DisplayControl", dbInteger, acCheckBox)


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim db As Database
Dim tdf As TableDef, tf As Field
Dim prp As Property
 
 Set db = CurrentDb
 Set tdf = db.TableDefs("trefLinkList")
 
 With tdf
  .Fields.Append .CreateField("MyCheckbox", dbBoolean)
 End With
 
 tdf.Fields.Refresh
 Set tdf = db.TableDefs("trefLinkList")
 Set prp = tdf.Fields("MyCheckbox").CreateProperty("DisplayControl", dbInteger, acCheckBox)
 tdf.Fields(tf.Name).Properties.Append prp
paulmcneilAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
You haven't defined tf, which you are using in the following line:
>tdf.Fields(tf.Name).Properties.Append prp

Your code works beatifully in Access 2003, by changing this line to:
tdf.Fields("MyCheckbox").Properties.Append prp


0
 
paulmcneilAuthor Commented:
mbizup,
I took your advice and now I get the data type mismatch on

Set prp = tdf.Fields("MyCheckbox").CreateProperty("DisplayControl", dbInteger, acCheckBox)
0
 
mbizupCommented:
What version of access are you using?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
jefftwilleyCommented:
Can you use this?
 
        strSQL = "Alter Table tbl_Specs ADD COLUMN ["ColumnName"] YesNo"
        CurrentDb.Execute strSQL
0
 
paulmcneilAuthor Commented:
I'm using Access 2003 SP2
0
 
mbizupCommented:
Paul,

Thats the same version I'm using.  When something works on one machine but not another, the first thing I check is references (although I'd expect a different error message if it were a reference problem).  Anyhow to rule that out as a possibility, open a VBA module and go to Tools -> References.  Do you see anything flagged as MISSING?
0
 
harfangConnect With a Mentor Commented:
Hello paulmcneil

Both ADODB and DAO have an object type called "Property". If your "ActiveX Data Objects" is loaded first in the list of references (it probably is), you would get a type mismatch. You need to be explicit:

    Dim prp As DAO.Property

Your code will work fine with that change.
Gool luck!
(°v°)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.