Set Display Control for a Table Yes/No Field

[CB]

I need to create a macro that will change the display control on existing
Yes/No fields from Text to Check box. The person who originally created the
database didn't know that that when you created tables from queries, the
properties were not copied over. As such, I have to update over a hundred
tables and it will have to be repeated every time the tables are created. I
would prefer creating a macro than having to update the tables every time
they are created.

Unfortunately, I don't know VB yet so I am trying to create a macro without
using VB. Here is what I have done.
Action - Open Table
Table - City
View - Design
Mode Edit
Action - Run Code
Table![City]![Complete].CreateProperty("DisplayControl", dbInteger, 106)
Action - Close Table
Table - City

Everytime I run it, I get an error message saying that it cannot locate my
object. I am beginning to wonder if I can only use the command on forms or
reports.

 

[Steve Schapel]

CB,

Basically, this does not make sense:
Table![City]![Complete]

As far as I know, there is no provision for this type of syntax with respect
to tables.

In any case, the RunCode macro action requires the name of a Function as its
argument.

That aside, what you are doing seems to be very unusual. Common wisdom
holds that the purpose of tables in a database is background storage of
data. Apart from the design and debugging stages of application
development, they would never be seen, and as such the way the data is
displayed is of no concern. Is it possible you could review your
application design?

 

[CB]

Unfortunately, I do not have the ability to redesign the database at this
time. I just need to bandaid it until I can fix it later. It is an unusual
application. All of that aside, do you have a recommendation on how I can
programmatically update the display control on a yes/no field without using
VB (since I haven't learned VB yet).

Thank you for your time.

CB,

Basically, this does not make sense:
Table![City]![Complete]

As far as I know, there is no provision for this type of syntax with respect
to tables.

In any case, the RunCode macro action requires the name of a Function as its
argument.

That aside, what you are doing seems to be very unusual. Common wisdom
holds that the purpose of tables in a database is background storage of
data. Apart from the design and debugging stages of application
development, they would never be seen, and as such the way the data is
displayed is of no concern. Is it possible you could review your
application design?

--
Steve Schapel, Microsoft Access MVP

I need to create a macro that will change the display control on existing
Yes/No fields from Text to Check box. The person who originally created
the
database didn't know that that when you created tables from queries, the
properties were not copied over. As such, I have to update over a hundred
tables and it will have to be repeated every time the tables are created.
I
would prefer creating a macro than having to update the tables every time
they are created.

Unfortunately, I don't know VB yet so I am trying to create a macro
without
using VB. Here is what I have done.
Action - Open Table
Table - City
View - Design
Mode Edit
Action - Run Code
Table![City]![Complete].CreateProperty("DisplayControl", dbInteger, 106)
Action - Close Table
Table - City

Everytime I run it, I get an error message saying that it cannot locate my
object. I am beginning to wonder if I can only use the command on forms
or
reports.

.

 

[Daryl S]

CB -

It sounds like you re-create the same tables over and over. Why don't you
instead create a base table with the correct field types, then instead of
using a make-table query, copy the base table and change the make-table to an
append query.

 

[CB]

You would be correct. I was told that it was created this way because the
append query took longer to run than the make table query.

I take it that there is not a way then to programmatically change the
control type?

Thank you for your response.

CB -

It sounds like you re-create the same tables over and over. Why don't you
instead create a base table with the correct field types, then instead of
using a make-table query, copy the base table and change the make-table to an
append query.

--
Daryl S

I need to create a macro that will change the display control on existing
Yes/No fields from Text to Check box. The person who originally created the
database didn't know that that when you created tables from queries, the
properties were not copied over. As such, I have to update over a hundred
tables and it will have to be repeated every time the tables are created. I
would prefer creating a macro than having to update the tables every time
they are created.

Unfortunately, I don't know VB yet so I am trying to create a macro without
using VB. Here is what I have done.
Action - Open Table
Table - City
View - Design
Mode Edit
Action - Run Code
Table![City]![Complete].CreateProperty("DisplayControl", dbInteger, 106)
Action - Close Table
Table - City

Everytime I run it, I get an error message saying that it cannot locate my
object. I am beginning to wonder if I can only use the command on forms or
reports.

 

[Daryl S]

CB -

I don't think you can change it programatically. I think you could
programatically add a new boolean field, then run a query to populate it
based on the original field, delete the original field, then rename the new
boolean field, but I think that would be a lot more processing then using the
append query approach.

--
Daryl S

You would be correct. I was told that it was created this way because the
append query took longer to run than the make table query.

I take it that there is not a way then to programmatically change the
control type?

Thank you for your response.

CB -

It sounds like you re-create the same tables over and over. Why don't you
instead create a base table with the correct field types, then instead of
using a make-table query, copy the base table and change the make-table to an
append query.

--
Daryl S

I need to create a macro that will change the display control on existing
Yes/No fields from Text to Check box. The person who originally created the
database didn't know that that when you created tables from queries, the
properties were not copied over. As such, I have to update over a hundred
tables and it will have to be repeated every time the tables are created. I
would prefer creating a macro than having to update the tables every time
they are created.

Unfortunately, I don't know VB yet so I am trying to create a macro without
using VB. Here is what I have done.
Action - Open Table
Table - City
View - Design
Mode Edit
Action - Run Code
Table![City]![Complete].CreateProperty("DisplayControl", dbInteger, 106)
Action - Close Table
Table - City

Everytime I run it, I get an error message saying that it cannot locate my
object. I am beginning to wonder if I can only use the command on forms or
reports.