Bit to mean Yes/No

[Bill]

The code snippet below was executed successfully,
but I didn't end up with what I'd intended, a new
field to be taken as Yes/No.

strDDL = "ALTER TABLE Registry ADD Column PubCell1 BIT"
conBackend.Execute strDDL, dbFailOnError

Is there some data type that's supposed to be used besides "Bit"?
I don't see anything in the SQL HELP that further qualifies
the further meaning of "bit".

What am I missing here.

Thanks,
Bill

 

[orange]

Bill,

If you look in Access Help for Datatype you will see bit

It can take on values 0 (False/OFF/NO) or -1 (True/On/Yes)

I created a mock table with 2 fields fld1 and fld2, then
ran

Sub jtest()
  Dim strDDL As String
  Dim db As DAO.Database
  Set db = CurrentDb
  strDDL = "ALTER TABLE Registry ADD Column PubCell1 BIT"
  db.Execute strDDL
  End Sub

Table: Registry now has 3 fields.
fld1fld2PubCell1


I added 3 records with values as follows:
abc 230 0
xyzz 11 -1
wwq 137 0

If I run query:

SELECT Registry.fld1, Registry.fld2, Registry.PubCell1
FROM Registry
WHERE (((Registry.PubCell1)=True));



I get this result:

Query3

fld1 fld2 PubCell1
xyzz 11 -1



I hope this is helpful.

 

[Allen Browne]

YESNO should work.

For the names to use with DDL queries on JET tables, see:
Field type reference - names and values for DDL, DAO, and ADOX
at:
http://allenbrowne.com/ser-49.html

 

[Bill]

That didn't do the trick Allen. I changed the DDL statement to:

strDDL = "ALTER TABLE Registry ADD Column PubCell1 YESNO"

But the field, as viewed in the datasheet, is initially zero (0) rather than
a check box. When I click on a form's checkbox that is bound to the
new field, Access simply sounds a "blink".

When I examine the table in Design View, the data type is shown as
Yes/No, but again, in datasheet view it does not appear as a checkbox.

I looked at your reference table, but I don't see that DAO or ADOX
would seem appropriate.

Bill

 

[Bill]

(PS) conBackend is a ADODB.Connection

That didn't do the trick Allen. I changed the DDL statement to:

strDDL = "ALTER TABLE Registry ADD Column PubCell1 YESNO"

But the field, as viewed in the datasheet, is initially zero (0) rather
than
a check box. When I click on a form's checkbox that is bound to the
new field, Access simply sounds a "blink".

When I examine the table in Design View, the data type is shown as
Yes/No, but again, in datasheet view it does not appear as a checkbox.

I looked at your reference table, but I don't see that DAO or ADOX
would seem appropriate.

Bill

 

[Allen Browne]

Ah: so you want to set the DisplayControl property of the field?

You will have to do that with DAO, like this:

CurrentDb.TableDefs("Registry").Fields("PubCell1").Properties("DisplayControl")
= CInt(acCheckBox)

 

[Bill]

I added Properties("DisplayControl") as you see in
the code segment below. The field added okay but
I got the error: 3270 Property not found.

strDDL = "ALTER TABLE Registry ADD Column PubCell1 YESNO" 'Y/N Okay to
publish Cell1?
conBackend.Execute strDDL, dbFailOnError
CurrentDb.TableDefs("Registry").Fields("PubCell1").Properties("DisplayControl")
= CInt(acCheckBox)


Bill

 

[Bill]

Allen,
Forget about my previous comment:

"When I click on a form's checkbox that is bound to the
new field, Access simply sounds a "blink".",

as that is a un-related problem I created when I created
the form's underlying RecordSource query, which contained
an inner-join of two tables that had a one-to-many
relationship, which leaves the resulting recordset
un-editable.

I still can't find the answer to the 3270 error.

Bill

 

[Allen Browne]

Right: if the property doesn't exist yet, you have to create it.

The code I use to set the property (creating it if necessary), is
SetPropertyDAO(), towards the bottom of this page:
http://allenbrowne.com/AppPrintMgtCode.html#SetPropertyDAO

 

[Bill]

That would be, in my case:' obj = PubCell1
' strPropertyName = "Display Control"Not clear about the two parameters:
' intType = the type of property (needed for creating)
'varValue = the value to set this property to.Bill"Allen Browne"

 

[Allen Browne]

Call SetPropertyDAO(CurrentDb.TableDefs("Registry").Fields("PubCell1"),
"DisplayControl", dbInteger, 106)

 

[Bill]

Okay, here's the whole picture. Four fields being created and
their display properties being created and set. I received the
error:

3265 Item not found in this collection.

PubCell1 and PubCell2 were created, but not neither of the
two PubEMA1 or PubEMA2. Leading me to believe the
error occurred pertaining to either the creation of the property
or setting of the property for PubCell2.

The four segments of code should be identical except for the
field names, so it's a mystery as to why the 1st field completed
okay while there was any sort of problem with the others????

Note that the dbFailOnError is only coded with the
conBackend.Execute statement. I believe the error
was displayed out of my error handler.
===========(The code)=======================

========(1st field)=========
strDDL = "ALTER TABLE Registry ADD Column PubCell1 YESNO" 'Y/N Okay to
publish Cell1?
conBackend.Execute strDDL, dbFailOnError
Call SetPropertyDAO(CurrentDb.TableDefs("Registry").Fields("PubCell1"),
"DisplayControl", dbInteger, 106)
CurrentDb.TableDefs("Registry").Fields("PubCell1").Properties("DisplayControl")
= CInt(acCheckBox)

========(2nd field)=========
strDDL = "ALTER TABLE Registry ADD Column PubCell2 YESNO" 'Y/N Okay to
publish Cell2?
conBackend.Execute strDDL, dbFailOnError
Call SetPropertyDAO(CurrentDb.TableDefs("Registry").Fields("PubCell2"),
"DisplayControl", dbInteger, 106)
CurrentDb.TableDefs("Registry").Fields("PubCell2").Properties("DisplayControl")
= CInt(acCheckBox)

========(3rd field)=========
strDDL = "ALTER TABLE Registry ADD Column PubEMA1 YESNO" 'Y/N Okay to
publish EMA 1?
conBackend.Execute strDDL, dbFailOnError
Call SetPropertyDAO(CurrentDb.TableDefs("Registry").Fields("PubEMA1"),
"DisplayControl", dbInteger, 106)
CurrentDb.TableDefs("Registry").Fields("PubEMA1").Properties("DisplayControl")
= CInt(acCheckBox)

========(4th field)=========
strDDL = "ALTER TABLE Registry ADD Column PubEMA2 YESNO" 'Y/N Okay to
publish EMA 2?
conBackend.Execute strDDL, dbFailOnError
Call SetPropertyDAO(CurrentDb.TableDefs("Registry").Fields("PubEMA2"),
"DisplayControl", dbInteger, 106)
CurrentDb.TableDefs("Registry").Fields("PubEMA2").Properties("DisplayControl")
= CInt(acCheckBox)

=========================================

 

[Allen Browne]

Is the table in this database (Currentdb)?
Or is it linked to your back end database?

You seem to have a conBackEnd which looks like a Database type. Try using it
instead of CurrentDb.

It might be easier to use DAO to append the new field, like this:
Dim fld As DAO.Field
With conBackEnd.TableDefs("MyTable")
Set fld = .CreateField("PubCell1", dbBoolean)
.Fields.Append fld
Call SetPropertyDAO(fld, "DisplayControl", dbInteger,
CInt(acCheckBox))
End With

 

[Bill]

Table is linked to the backend.

I get error:

3057 Operation not supported on linked tables.

when I attempt to use DAO

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

'Initialize
Set db = CurrentDb()

With db.TableDefs("Registry")
Set fld = .CreateField("PubCell1", dbBoolean)
.Fields.Append fld
Call SetPropertyDAO(fld, "DisplayControl", dbInteger,
CInt(acCheckBox))
End With

'Clean up
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

 

[Douglas J. Steele]

You need to replace

Set db = CurrentDb()

with

Set db = OpenDatabase("F:\Folder\File.mdb")

(or whatever the full path is your backend database)

 

[Bill]

That did the trick.

Thanks Allen and Doug,
Bill