Adding a yes/no to a table using VB 6

[SadOldGoth]

Hi Folks,

I'm trying to add a yes/no field to a table in Access 2k using an SQL script. No problem in terms of actually putting the field on but I cannot find a way of changing the control format to yes/no, other than going into the table details - it defaults to text box. This is obviously a pain when I want to print the field in a report.

Anyone got a solution?

Later,

Jes

 

[Steve101]

Here's a simple example of some code using the SQL ALTER statement:
[tt]
Dim dbs As DAO.Database
Set dbs = CurrentDb
dbs.Execute "ALTER TABLE Table1 " _
& "ADD COLUMN Salary2 yesno;"
dbs.Close
[/tt]
Notes:

(a) This code uses the execute method of the DAO database object, so make sure that the Microsoft DAO library is referenced.

(b) There is no error handling incorporated here. An attempt to add a field which already will result in an error which will need to be appropriately handled.

Hope this helps,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[SadOldGoth]

Hi Steve,

yeah, I've got that bit, but when the column is added the control format comes out as Text Box, not yesno...um, that is, the field type is yes/no but the lookup type isn't ;0)

Later,

Jes

 

[SadOldGoth]

Hi Folks,

I don't know what the netiquette on this site is for bumping posts, so I'll going to apologise if it's out of order, but I wanted to keep this question alive.

Any ideas anyone?

Thanks,

Jes

 

[Steve101]

Hi Jes,

Sorry I never got back earlier. I'm not sure how you programatically change the Lookup property of the YesNo field, but note the following:

(a) You say that this is important to you for reporting purposes.

(b) If this is the issue, then you only need to worry about the Lookup DisplayControl being set to the format you want when you initially set up the control on the report. This property on the report will be inherited from the table at the time the control is set up on the report, and will not change on the report, irrespective of what happens to the property in the table.

(c) So ... if you remove the control from your report; change the property on the table to the Lookup DisplayControl you want, then add the control back to your report, the report DisplayControl property should be "set for life", even if the property changes on the underlying table.

Hope this helps,

Good luck,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[TonyJollans]

Hi SadOldGoth,

This one is fun. When you add a new column using SQL it has no effect on the Datasheet which comes by default with the table. Given no information Access simply uses its defaults of a Text Box with no formatting.

After adding your column you need to set its properties to force what you want. Note that the properties do not even exist by default so you must create them and append them to the (new) Field's Properties Collection.

I hope all that makes sense, here's some code. Replace the names in red.

Dim tjDb As DAO.Database
Dim tjTab As DAO.TableDef
Dim tjFld As DAO.Field
Dim tjPropFormat As DAO.Property
Dim tjPropDisplay As DAO.Property

Set tjDb = CurrentDb
Set tjTab = tjDb.TableDefs!

OldTab

tjDb.Execute "ALTER TABLE Table5 ADD COLUMN

NewCol

 YesNo;"

Set tjFld = tjTab.Fields!

NewCol

Set tjPropFormat = tjFld.CreateProperty("Format", dbText, "Yes/No")
tjFld.Properties.Append tjPropFormat

Set tjPropDisplay = tjFld.CreateProperty("DisplayControl", dbInteger, acCheckBox)
tjFld.Properties.Append tjPropDisplay

tjDb.Close

Set tjPropDisplay = Nothing
Set tjPropFormat = Nothing
Set tjFld = Nothing
Set tjTab = Nothing
Set tjDb = Nothing

Enjoy,
Tony

 

[SadOldGoth]

Hi Folks,

@Tony: That's exactly it! I tried playing with properties but as you say, there's not one that exists for this situation. I'll try it when I get into work later.

Thanks very much to all who've responded.

Later,

Jes

 

[Steve101]

Tony,

Very interesting and enlightening. I've often wondered about properties in the table and other objects which dont expose a property in the programmable object model. Does this mean that one can also get to things like the Description in the field definition line of the table design interface in the same way?

Good one mate,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[TonyJollans]

Yes, Steve,

As far as I understand it, you can add any Property you like and use it in any way that you want - they all show up if you use the Documenter on the Table. If you add a Property with a name which Access recognises (like "Description&quot it will do its own thing with it (in this case display in the table design form).

Enjoy,
Tony

 

[Steve101]

Thats great Tony,
I've often wanted to 'get to' the Field Description's programmatically, but not known how to do it. I'll have to give this a try.

Cheers,


Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)