Create table column as CheckBox

I have the below which does create the table, but I need to have the "Assign" column show up as a checkbox.  However, I don't seem to be able to get this done with what I have so far.

Public Sub CreateAssignTables()
On Error GoTo ErrorHandler
Dim tdf As DAO.TableDef
Dim pt As DAO.Property

'First create for exitance of tables and delete
If fntDoesObjectExist("tblTEMPAssignVar", "Table") Then DoCmd.DeleteObject acTable, "tblTEMPAssignVar"
If fntDoesObjectExist("tblTEMPAssignCon", "Table") Then DoCmd.DeleteObject acTable, "tblTEMPAssignCon"

'Create each table locally
Set tdf = CurrentDb.CreateTableDef("tblTEMPAssignVar")

With tdf
      ' The CreateField method will set a default Size if one is not specified
      .Fields.Append .CreateField("DeptName", dbText, 50)
      .Fields.Append .CreateField("UserID", dbText, 50)
      .Fields.Append .CreateField("TaskName", dbText, 50)
      .Fields.Append .CreateField("TaskID", dbText, 50)
      .Fields.Append .CreateField("Assign", dbBoolean)
End With

Set pt = tdf.Fields("Assign").CreateProperty("DisplayControl", dbBoolean, acCheckBox)
  tdf.Fields("Assign").Properties.Append pt
 
CurrentDb.TableDefs.Append tdf
CurrentDb.TableDefs.Refresh

Set tdf = Nothing

Exit_ErrorHandler:
    Exit Sub
ErrorHandler:
    MsgBox "Error Number; " & Err.Number & vbCrLf & _
           "Description: " & Err.Description & vbCrLf & _
           "Procedure: CreateAssignTables", vbOKOnly
    Resume Exit_ErrorHandler
End Sub
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.

Rey Obrero (Capricorn1)Commented:
change this

Set pt = tdf.Fields("Assign").CreateProperty("DisplayControl", dbBoolean, acCheckBox)

with

Set pt = tdf.Fields("Assign").CreateProperty("DisplayControl", dbInteger, acCheckBox)

or

Set pt = tdf.Fields("Assign").CreateProperty("DisplayControl", dbInteger, 106)
0
Sandra SmithRetiredAuthor Commented:
I used Set pt = tdf.Fields("Assign").CreateProperty("DisplayControl", dbInteger, acCheckBox)

but it errors out on the next line where I append the property
tdf.Fields("Assign").Properties.Append pt
0
Sandra SmithRetiredAuthor Commented:
Is there a library reference I should be setting?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Sandra SmithRetiredAuthor Commented:
The error I get is Error Number 3219 Invalid Operation

Sandra
0
Rey Obrero (Capricorn1)Commented:
use this  codes


'Create each table locally
Set tdf = CurrentDb.CreateTableDef("tblTEMPAssignVar")

With tdf
      ' The CreateField method will set a default Size if one is not specified
      .Fields.Append .CreateField("DeptName", dbText, 50)
      .Fields.Append .CreateField("UserID", dbText, 50)
      .Fields.Append .CreateField("TaskName", dbText, 50)
      .Fields.Append .CreateField("TaskID", dbText, 50)
      .Fields.Append .CreateField("Assign", dbBoolean)
End With

CurrentDb.TableDefs.Append tdf     'moved here

Set pt = tdf.Fields("Assign").CreateProperty("DisplayControl", dbinteger, acCheckBox)
  tdf.Fields("Assign").Properties.Append pt
 
               'CurrentDb.TableDefs.Append tdf ' move up
0

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:
Now it stops and the Set pt line.  I don't think this can be done in code no matter what I have found on line to say that it can.  I get an Object invalid or no longer set error.

I have the correct library references and all, but I am getting ready to simply give up.

Sandra
0
Rey Obrero (Capricorn1)Commented:
don't panic..

try this codes


Public Sub CreateAssignTables()
On Error GoTo ErrorHandler
Dim tdf As DAO.TableDef, db as Dao.database
Dim pt As DAO.Property

'First create for exitance of tables and delete
If fntDoesObjectExist("tblTEMPAssignVar", "Table") Then DoCmd.DeleteObject acTable, "tblTEMPAssignVar"
If fntDoesObjectExist("tblTEMPAssignCon", "Table") Then DoCmd.DeleteObject acTable, "tblTEMPAssignCon"

'Create each table locally

set db=currentdb  
Set tdf =db.CreateTableDef("tblTEMPAssignVar")

With tdf
      ' The CreateField method will set a default Size if one is not specified
      .Fields.Append .CreateField("DeptName", dbText, 50)
      .Fields.Append .CreateField("UserID", dbText, 50)
      .Fields.Append .CreateField("TaskName", dbText, 50)
      .Fields.Append .CreateField("TaskID", dbText, 50)
      .Fields.Append .CreateField("Assign", dbBoolean)
End With

 
db.TableDefs.Append tdf

Set pt = tdf.Fields("Assign").CreateProperty("DisplayControl", dbBoolean, acCheckBox)
  tdf.Fields("Assign").Properties.Append pt

db.TableDefs.Refresh

Set tdf = Nothing
Set db= nothing
Exit_ErrorHandler:
    Exit Sub
ErrorHandler:
    MsgBox "Error Number; " & Err.Number & vbCrLf & _
           "Description: " & Err.Description & vbCrLf & _
           "Procedure: CreateAssignTables", vbOKOnly
    Resume Exit_ErrorHandler
End Sub
0
Sandra SmithRetiredAuthor Commented:
Got it to work with the below changes.  You gave me the idea for dbinteger and acCheckbox, but I realized thta it was using CreateTableDefs, not just TableDefs in the statement we well as moving it to the correct spot in the code<br /><br /><br />Set pt = CurrentDb.TableDefs("tblTE<wbr />MPAssignVa<wbr />r").Fields<wbr />("Assign")<wbr />.CreatePro<wbr />perty("Dis<wbr />playContro<wbr />l", dbInteger, acCheckBox)<br />        CurrentDb.TableDefs("tblTE<wbr />MPAssignVa<wbr />r").Fields<wbr />("Assign")<wbr />.Propertie<wbr />s.Append pt
0
Sandra SmithRetiredAuthor Commented:
Yes, I was panicing!  Thank you for your help, this has been a thorn in my side for a good part of the date.

Sandra
0
Sandra SmithRetiredAuthor Commented:
Below is the final full version of the code that works.

Public Sub CreateAssignTables()
On Error GoTo ErrorHandler
Dim tdf As DAO.TableDef
Dim pt As DAO.Property
Dim dbs As DAO.Database
Set dbs = CurrentDb

'First create for exitance of tables and delete
If fntDoesObjectExist("tblTEMPAssignVar", "Table") Then DoCmd.DeleteObject acTable, "tblTEMPAssignVar"
If fntDoesObjectExist("tblTEMPAssignCon", "Table") Then DoCmd.DeleteObject acTable, "tblTEMPAssignCon"

'Create each table locally - Variable tasks
Set tdf = dbs.CreateTableDef("tblTEMPAssignVar")
With tdf
      ' The CreateField method will set a default Size if one is not specified
      .Fields.Append .CreateField("DeptName", dbText, 50)
      .Fields.Append .CreateField("UserID", dbText, 50)
      .Fields.Append .CreateField("TaskName", dbText, 50)
      .Fields.Append .CreateField("TaskID", dbText, 50)
      .Fields.Append .CreateField("Assign", dbBoolean)
End With
    dbs.TableDefs.Append tdf     'moved here
    dbs.TableDefs.Refresh
Set pt = dbs.TableDefs("tblTEMPAssignVar").Fields("Assign").CreateProperty("DisplayControl", dbInteger, acCheckBox)
         dbs.TableDefs("tblTEMPAssignVar").Fields("Assign").Properties.Append pt
Set tdf = Nothing
   

'Create each table locally - Constant tasks
Set tdf = dbs.CreateTableDef("tblTEMPAssignCon")
With tdf
      ' The CreateField method will set a default Size if one is not specified
      .Fields.Append .CreateField("DeptName", dbText, 50)
      .Fields.Append .CreateField("UserID", dbText, 50)
      .Fields.Append .CreateField("TaskName", dbText, 50)
      .Fields.Append .CreateField("TaskID", dbText, 50)
      .Fields.Append .CreateField("Assign", dbBoolean)
End With
    dbs.TableDefs.Append tdf
    dbs.TableDefs.Refresh
Set pt = dbs.TableDefs("tblTEMPAssignCon").Fields("Assign").CreateProperty("DisplayControl", dbInteger, acCheckBox)
         dbs.TableDefs("tblTEMPAssignCon").Fields("Assign").Properties.Append pt
Set tdf = Nothing


Exit_ErrorHandler:
    Exit Sub
ErrorHandler:
    MsgBox "Error Number; " & Err.Number & vbCrLf & _
           "Description: " & Err.Description & vbCrLf & _
           "Procedure: CreateAssignTables", vbOKOnly
    Resume Exit_ErrorHandler
End Sub
0
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 Development

From novice to tech pro — start learning today.