VBA Access Code - Adding a checkbox field

[allengee]

I am trying to add a column w/ a boolean checkbox datatype to a table but am having problem with the syntax error (Runtime: 3292)

 

[Remou]

3292: Syntax error in field definition.

Please post the code you are using.

 

[allengee]

Option Compare Database


Function ForeignCountry()
Const DepFile As String = "E:\Deposit Foreign\ForeignDepositsMetavante.txt"

'DoCmd.SetWarnings False

'Delete existing data, Import current data and add columns: Foreign Flag and Country
On Error Resume Next
DoCmd.DeleteObject acTable, "ForeignDepositsMetavante"
On Error GoTo 0
DoCmd.TransferText acImportDelim, "ForeignDepositsMetavante_Import_Spec2", "ForeignDepositsMetavante", DepFile

DoCmd.RunSQL "ALTER TABLE ForeignDepositsMetavante ADD COLUMN Country text"
'DoCmd.RunSQL "ALTER TABLE ForeignDepositsMetavante ADD COLUMN [Foreign Flag] yesno"

'DoCmd.SetWarnings True

End Function

 

[Remou]

I find that this line:
DoCmd.RunSQL "ALTER TABLE ForeignDepositsMetavante ADD COLUMN [Foreign Flag] yesno"

Works for me. Is this the line that is failing?

 

[allengee]

It actually works but I needed this field to be displayed as a checkbox not values of "0" and "-1"; I beleive it has something to do with the "Display Control" checkbox...

thanks.

 

[Remou]

You need to go about that a different way, SQL will not suit.

Sub AddDisplayControl()
'Requires reference to Microsoft DAO 3.6 Object Library
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim db As Database

Set db = CurrentDb
Set tdf = db.TableDefs("ForeignDepositsMetavante")
Set fld = tdf.Fields("Foreign Flag")

Set prp = fld.CreateProperty("DisplayControl", dbInteger, 106)
fld.Properties.Append prp

End Sub

 

[allengee]

Thanks for all your help...I will give it a shot and let you know...