How to add YES/NO checkbox column to existing table using VBA?

Question

To begin with, I'm quite new to access.

I have a table , call it 'A'. I also have a form with a button which triggers an event when pushed. Using VBA I want to add a YES/NO column to 'A'. I have accomplished that with the following code:

Dim strSQL As String
strSQL = "ALTER TABLE 'A' ADD COLUMN test YESNO;"
    
CurrentDb.Execute strSQL

But it's just zeros on each row. I want to have the checkbox style. I know how to set it manually in "Display Control" but since I'm creating a "program" for a friend I want it to be set automatically when the button is pushed.

I have tried working around this problem for a while now and to be honest I don't even remember all the results I have gotten, all I know is that it's not what I want. On different forums, with posts as old as 2003, I find some people recommending changing the "fld.CreateProperty..." to "fld.Append..." and vice versa. Not working for me.

The code following below is what I have tried but I get:

"Run-time error '3421' Data type conversion error."

Set db = CurrentDb
Set td = db.TableDefs("A")
Set fld = td.Fields("test")
Set prp = fld.CreateProperty("DisplayControl", dbInteger, 106)

fld.Properties.Append prp

After some help in the comments my VBA code looks like this:

Private Sub Command13_Click()

    Dim strSQL  As String
    Dim dbs     As DAO.Database
    Dim tdf     As DAO.TableDef
    Dim fld     As DAO.Field
    Dim prp     As DAO.Property

    Set dbs = CurrentDb

    strSQL = "ALTER TABLE table ADD COLUMN test YESNO;"
    dbs.Execute strSQL

    Set tdf = dbs.TableDefs("table")
    Set fld = tdf.Fields("test")
    Set prp = fld.CreateProperty("DisplayControl", dbInteger, 106)
    fld.Properties.Append prp`
End Sub

When this code runs from a press of a button, I get the error "Compile error: User-defined type not defined". The debugger highlights the following yellow:

Private Sub Command13_Click()

and the following blue:

Dim dbs     As DAO.Database

Why do you need code to add a field? If you are routinely modifying table, likely structure is not adequately normalized. Why does it matter how field displays in table? Set whatever you want on form. — June7
– June7, Commented Aug 15, 2019 at 7:14
Once you have adapted the table and added the column, you are not going to do it again … not sure why you want the complexity of VBA? — AJD
– AJD, Commented Aug 15, 2019 at 7:35
You have to add another yes/no field every year? Sorry, still doesn't make sense to me. However, hope the supplied answer resolves issue. — June7
– June7, Commented Aug 15, 2019 at 8:43

Gustav · Accepted Answer · 2019-08-15 08:03:39Z

4

Your SQL syntax is not correct. Once corrected, this runs smoothly and as intended here:

Public Function SetFieldDisplay()

    Dim strSQL  As String
    Dim dbs     As DAO.Database
    Dim tdf     As DAO.TableDef
    Dim fld     As DAO.Field
    Dim prp     As DAO.Property

    Set dbs = CurrentDb

    strSQL = "ALTER TABLE TestTable ADD COLUMN DisplayTest YESNO;"
    dbs.Execute strSQL

    Set tdf = dbs.TableDefs("TestTable")
    Set fld = tdf.Fields("DisplayTest")
    Set prp = fld.CreateProperty("DisplayControl", dbInteger, 106)
    fld.Properties.Append prp

End Function

answered Aug 15, 2019 at 8:03

Gustav

56.5k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Tesel Over a year ago

Thanks for the reply! Since I'm using VBA, is this code (including the 'public function' and 'end function') supposed to be inbetween the 'Private Sub Command_Click()' and 'End Sub'? As I want this to occur when the button is pressed.

Gustav Over a year ago

You can either call the function from the click event, or you can copy-paste the code lines into the click event.

Gustav Over a year ago

Maybe you should append your code to your question.

Gustav Over a year ago

Go to menu Tools, References and add a reference to: Microsoft Office 16.0 Access database engine Object Library.

Gustav Over a year ago

Probably you are also using ADO which is quite different from DAO and cannot use code like this for schema changes. Then you must use ADOX which is very different story.

|

Collectives™ on Stack Overflow

How to add YES/NO checkbox column to existing table using VBA?

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related