Change Display Control on a Table Programmatically

[CB]

First, let me begin by saying that I don't know VB. I have been trying to do
this using a macro. I need to set the display control for a field in about a
100 tables. Currently, the Yes/No field has a Display Control of Text and I
need it to be a check box. When I created a macro using the following
actions, I get an error message that it doesn't recognize Tables!. Does this
mean that I can only use run code for forms and reports. Please excuse me if
the question is stupid. I am trying to muddle along until I can take a class.

Open Table:
City
Design View
Edit

Run Code:
Tables![CITY]![COMPLETE].CreateProperty("DisplayControl",[dbInteger],106)

Close Table:
City

 

[Dirk Goldgar]

First, let me begin by saying that I don't know VB. I have been trying to
do
this using a macro. I need to set the display control for a field in
about a
100 tables. Currently, the Yes/No field has a Display Control of Text and
I
need it to be a check box. When I created a macro using the following
actions, I get an error message that it doesn't recognize Tables!. Does
this
mean that I can only use run code for forms and reports. Please excuse me
if
the question is stupid. I am trying to muddle along until I can take a
class.

Open Table:
City
Design View
Edit

Run Code:
Tables![CITY]![COMPLETE].CreateProperty("DisplayControl",[dbInteger],106)

Close Table:
City

I don't think you're going to be able to do this in a macro, because you
need to create a property object and append it to the properties
collection -- or, if the property already exists, just change its value.
Here is an untested VBA procedure to set the DisplayControl property for
given table and field name:

'------ start of **air code** ------
Sub SetDisplayControl( _
TableName As String, _
FieldName As String, _
ControlType As Integer)

On Error GoTo Err_Handler

Dim fld As DAO.Field

On Error GoTo Err_Handler_General

With CurrentDb.TableDefs(TableName)

Set fld = .Fields(FieldName)

On Error GoTo Err_Handler_Property
fld.Properties("DisplayControl") = ControlType
On Error GoTo Err_Handler_General

Set fld = Nothing

End With

Exit_Point:
Exit Sub

Err_Handler_Property:
If Err.Number = 3270 Then
' The property doesn't exist, so create it.
fld.Properties.Append _
fld.CreateProperty("DisplayControl", dbInteger, ControlType)
Resume Next
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point
End If

Err_Handler_General:
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
'------ end of **air code** ------

The procedure would be stored in a standard module, and you would call it
like this:

SetDisplayControl "CITY", "COMPLETE", 106

 

[John W. Vinson]

First, let me begin by saying that I don't know VB. I have been trying to do
this using a macro. I need to set the display control for a field in about a
100 tables. Currently, the Yes/No field has a Display Control of Text and I
need it to be a check box. When I created a macro using the following
actions, I get an error message that it doesn't recognize Tables!. Does this
mean that I can only use run code for forms and reports. Please excuse me if
the question is stupid. I am trying to muddle along until I can take a class.

Open Table:
City
Design View
Edit

Run Code:
Tables![CITY]![COMPLETE].CreateProperty("DisplayControl",[dbInteger],106)

Close Table:
City

Two concerns:

1. You shouldn't care how the field is displayed in a Table. Tables have one
purpose only: data storage! They're NOT designed, nor optimized, nor
appropriate for data interaction; that's what Forms are for.

Much bigger issue:

2. If you have 100 tables of basically the same structure, you've got a decent
Excel Workbook - but a very badly designed Access database. At a guess, you
need *one* table, with another field to indicate which of the 100 subsets of
data each record represents, and *one* Form based on that table to allow you
to work with the data.

What are these tables? Are you in fact using the Table datasheets to enter
data? If so how do you pick which table?

Before taking a class, you might want to look at some of the tutorials and
other resources at these sites; Crystal's video would be a good start.

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials