Fields property via TableDefs not recognized

[GraeagleBill]

With the snipit of code below, the modules error code reports that "Format" is not a recognized property. The field "Deceased" was added okay via the ALTER TABLE, but trying to change the table def to format the field as Yes/No fails.

    strDDL = "ALTER TABLE Registry ADD Column Deceased YESNO False"
    conBackend.Execute strDDL, dbFailOnError
    CurrentDb.TableDefs("Registry").Fields("Deceased").Properties("Format") = acCheckBox

Anyone know what it should be?
Thanks,
Bill

[orange]

Sub testddl_Jed()
   Dim strddl As String
    strddl = "ALTER TABLE Registry ADD Column Deceased YESNO False"
    CurrentDb.Execute strddl, dbFailOnError
    CurrentDb.TableDefs("Registry").Fields("Deceased").Properties("Format") = acCheckBox
End Sub

I ran the first 2 lines of your code and all is well.
The format property of the Deceased field :

   
True/False
Yes/No  
On/Off

The third line results in 3270 Property not found.


Are you thinking of adding a checkbox control on form and setting its control source to the field Deceased??

[ssanfu]

Found this at Allen Browne's site
http://allenbrowne.com/func-DDL.html#CreateFieldDDL

(I added the bold blue hi-light)

While DDL is important in some larger databases, it is of limited use in Access. You can create a Text field, but you cannot set the Allow Zero Length property to No, so any query criteria must test for both Nulls and zero-length strings. You can create a Yes/No field, but you get a text box, not a check box, since you cannot set the Display Control property with DDL. You can create a Date/Time field, but you cannot set the Format property. DDL cannot create Hyperlink fields, Attachment fields, or the complex data types at all.

[GraeagleBill]

Are you thinking of adding a checkbox control on form and setting its control source to the field Deceased??

I already have and that's not the problem. I just wanted the table display to show check boxes versus 0 and -1.
Like you see for the fields PubCell and PubEMA in the screenshot below.
(I also want to do the same for the field SecurityCL where you also see as 0 and -1)

[orange]

I think Steve has the answer. Not availble via DDL.

I tried to create the Property "Display Control", then set it to acCheckbox, but Property Not Found results.

[GraeagleBill]

I won't mark the thread as "Solved", but I'm not going to spend any more time on the issue.
Thanks for your effort.
Bill

[pbaldy]

Hi Bill! I suspect others have identified the problem (I've never worried about the format in the table), but I'd wonder if conBackend was different than CurrentDb:

conBackend.Execute strDDL, dbFailOnError
CurrentDb.TableDefs("Registry").Fields("Deceased").Properti es("Format") = acCheckBox

In other words, it appears you are trying to apply the format to a linked table (CurrentDb) rather than the actual back end table conBackend). Maybe that's just me making wrong assumptions based on the variable name.

[orange]

I did find a reference that said in Acc 97 Display Control for YesNo field as a checkbox was a default.
Probably deprecated?? I don't use it, but haven't looked too much.

[GraeagleBill]

Hi Paul,
I'll play with this as time allows. Allen has more examples on subjects such as this one.
I only address things like this where DB field updates become an issue among users that look at tables.
Bill