CODEWITHSUNDEEP
vbams-access
Tesel
Tesel

Reputation: 41

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

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

Upvotes: 2

Views: 2052

Answers (1)

Gustav
Gustav

Reputation: 55841

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

Upvotes: 4

Related Questions