Featured free Microsoft Access tip from Eli Journals
Tip: Set the display control for dynamically created database fields
with DAO (2000/2002/2003) The ability to assign a display control to
a database field is a great Access feature. Unfortunately, when you
create databases and fields dynamically using CREATE TABLE, you can't
tell Access to use a specific display control, like a Combo Box or a
Check Box, in the SQL query.
However, you can use the CreateProperty() method of a DAO Field
object to complete this task.
To programmatically assign a display control to a database field, use
code similar to the following:
Sub SetDisplayField()
Dim dbs As DAO.Database
Dim p As DAO.Property
Dim f As DAO.Field
Dim sql As String
Set dbs = Application.CurrentDb
sql = "CREATE TABLE [TestTable] (" & _
"[RECORDNUM] AutoIncrement, " & _
"[DeleteMe] YESNO);"
dbs.Execute sql
Set f = dbs.TableDefs("TestTable"). _
Fields("DeleteMe")
Set p = f.CreateProperty("DisplayControl", _ dbInteger, 106)
f.Properties.Append p End Sub
This code creates a new table with a Yes/No field. Then, it uses DAO
to assign a Check Box as the field's display control using a property
Value of 106.
To create Text Box, List Box, or Combo Box display controls, you can
use a property Value of 109, 110, or 111, respectively. You may need
to set a reference to the DAO Object Library before testing this code.
>> Comment on this tip!
<http://www.elijournals.com/forums/viewforum.php?f=16> >> Offer
your own tip! <http://www.elijournals.com/forums/>
