Assistance with creating a field

  • Thread starter Thread starter Dan Thomson
  • Start date Start date
D

Dan Thomson

I figured out how to create a new field using .CreateField

But this doesn't appear to allow me to set various attributes/properties
other than type and length.

Can someone point me in the right direction on how to create a new field
with the following characteristics:

Display Control: Combo Box
Row Source Type: Table/Query
Row Source: Select * FROM tblBogusName;
Bound Column: 1
Column Count: 2
Column Widths: 0";1"
List Width: 1"
Limit To List: Yes

I'm trying to develop something that will work in all Access versions from
Access 2000 to current.

Thanks,

Dan
 
A combobox is not a field, it is a control. A field exists in a table. A
combobox exists on a form or report and may have a control source that is a
field in a table.

So, first question: Are you trying to create a control on a form or a field in a table?
 
I want to create a field in a table.

Except for the name, these listed characteristics are from an existing field
as shown in design view.
I want to create new fileds using vba that have similar characteristics.
 
Here is a sample code:

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

Set db = CurrentDb
Set tdf = db.TableDefs("YourTable")
'here you create the new field
Set fld = tdf.CreateField("fldComboBox", dbText)
tdf.Fields.Append fld

' then you need to create the properties you talked about because
' they are not built-in properties
Set prp = fld.CreateProperty("DisplayControl", dbInteger, acComboBox)
fld.Properties.Append prp
Set prp = fld.CreateProperty("RowSourceType", dbText, "Table/Query")
fld.Properties.Append prp
Set prp = fld.CreateProperty("RowSource", dbMemo, "Select *
tblBogusName;")
fld.Properties.Append prp
Set prp = fld.CreateProperty("BoundColumn", dbInteger, 1)
fld.Properties.Append prp
Set prp = fld.CreateProperty("ColumnCount", dbInteger, 2)
fld.Properties.Append prp
Set prp = fld.CreateProperty("ColumnWidths", dbText, "0;1441")
fld.Properties.Append prp
Set prp = fld.CreateProperty("ListWidth", dbText, "1441twips")
fld.Properties.Append prp
Set prp = fld.CreateProperty("LimitToList", dbBoolean, True)
fld.Properties.Append prp

Set prp = Nothing
Set fld = Nothing
Set tdf = Nothing
Set db = Nothing

HTH

--
Saludos desde Barcelona
Juan M. Afan de Ribera
<MVP Ms Access>
http://www.juanmafan.tk
http://www.clikear.com/webs4/juanmafan
 
Thanks...Works great

another question:

How can I enumerate the properties of a field that already exists?

The Help had this sample, but it bombed on line 2 and 3.
For Each prpLoop In .Properties
If prpLoop <> "" Then
MsgBox prpLoop.Name & " = " & prpLoop
End If
Next prpLoop



I modified the sample as follows. It displays the property name, but I'd
also like to get the property value.

Dim prpLoop As Property
 
If you want to get the value of some of the field's properties you will get
an error (as in the case of the Value property). So, you better write your
code this other way, for example:

On Error GoTo err_prpLoop
For Each prpLoop In .Properties
If prpLoop <> "" Then
MsgBox prpLoop.Name & " = " & prpLoop
End If
Next prpLoop

err_prpLoop:

MsgBox prpLoop.Name & " = Error: " & Err.Description
Resume Next

or, if you are not really interested in the errors, you can use this other
one:

On Error Resume Next
For Each prpLoop In .Properties
If prpLoop <> "" Then
MsgBox prpLoop.Name & " = " & prpLoop
End If
Next prpLoop

I'm not really sure, but I think this error (number 3219, I'm sorry, but I
cannot find it in Access VB help) is caused for the fact that DAO property
is the same in various objects (database, field, recordset).

HTH

--
Saludos desde Barcelona
Juan M. Afan de Ribera
<MVP Ms Access>
http://www.juanmafan.tk
http://www.clikear.com/webs4/juanmafan
 
Thanks for the reply. I figoured out the error thingy just a little bit ago
but am only now able to reply to the group.

Yea, I'm not interested in capturing the error within this loop. So I
implemented something like your example #2 and reset my On Error statement
when I exited the For loop so I could continue to process errors.

Nothing lke a little help from my friend "On Error Resume Next"

Thanks again

Dan
 
Back
Top