Add field to table using VBA and modify it

[snoopy2003]

Hello,

I am trying to add a field to a table and modify it to:
1. display as combobox
2. set rowSourceType to value list


3. set RowSourceType to 'Value List'
4. set RowSource to "Active" or "Inactive" values

My code does add the field by does not modify it's properties.

 If Not (FieldExists("Status", "tblMytbl")) Then
    strToDB = fGetLinkPath("tblMytbl")
    Set db = OpenDatabase(strToDB, False, False, ";pwd=xxx")
    Set nt = db.TableDefs("tblMytbl")
    Set fl = nt.CreateField("Status", dbText)
 
    nt.Fields.Append fl
    nt.Fields.Refresh
 
    Set fl = nt.Fields("Status")
    Set pr = fl.CreateProperty("DisplayControl", dbText, 111)
    fl.Properties.Append pr
    'fl.Properties.Append fl.CreateProperty("RowSourceType", "Value List")
     'fl.Properties.Append fl.CreateProperty("RowSource", "Active,Inactive")      
    nt.Fields.Refresh
 
    db.Close
    Set db = Nothing
 
End If

Can you help me ?

Thank you for your help

[ajetrumpet]

I think you might be confused (or maybe I am). This:

Set fl = nt.CreateField("Status", dbText)

is missing an optional argument, which is apparently included here as the last one:

Set pr = fl.CreateProperty("DisplayControl", dbText, 111)

what it looks like is that you are trying to use the property collection to append an argument that represents the length of the field that you just appended to your table.

try dropping your property-related code and then re-write your CreateField code:

Set fl = nt.CreateField("Status", dbText, 111)

by the way, this is vb6 code (or it looks like it). VBA is the same thing, but other than me, you might have gotten a quicker answer on a vb forum.

[ssanfu]

Hello,

I am trying to add a field to a table and modify it to:
1. display as combobox
2. set rowSourceType to value list
3. set RowSourceType to 'Value List'
4. set RowSource to "Active" or "Inactive" values

From your code, it looks like you are trying to create a lookup FIELD in a table. This is a bad idea. See http://access.mvps.org/access/lookupfields.htm

Unless the MDB is just for you, all data entry/edits is better done thru forms. When you use a combo box control on a form, you still have to recreate the value list.

[snoopy2003]

Hello,

Thank you for your answer. When I used:

Set fl = nt.CreateField("Status", dbText, 111)

I got a 'Status' text field with length = 111

I wish to set Display Control properties of this field to 'Combo box'
Row Source Type to 'Value List'
Row Source to "Active,Inactive"

How can I do it using VBA ?

Can you help me ?

Thank you

[ajetrumpet]

Hello,

Thank you for your answer. When I used:

Set fl = nt.CreateField("Status", dbText, 111)

I got a 'Status' text field with length = 111

I wish to set Display Control properties of this field to 'Combo box'
Row Source Type to 'Value List'
Row Source to "Active,Inactive"

Got it. And check out the post from the other person. Programming standards in Access don't like combo boxes as displays. Trust me. The reason can be found on the net. Professionals don't do this for a reason, and it's become standard.

But back to point of getting what you want. Look up in VBA help, things like "tabledef.properties" and "tabledef.attributes". These pages usually have functions like this on them:

Sub Attributesy()
'properties
   Dim dbsNorthwind As Database
   Dim fldLoop As Field
   Dim relLoop As Relation
   Dim tdfloop As TableDef
   Dim i As Integer
   Dim j As Integer

   Set dbsNorthwind = CurrentDb

   With dbsNorthwind

      ' Display the attributes of a TableDef object's
      ' fields.
   For i = 0 To dbsNorthwind.TableDefs.Count - 1
      Debug.Print "Properties of fields in " & _
         .TableDefs(i).Name & " table:"
      For Each fldLoop In .TableDefs(i).Fields
         For j = 0 To fldLoop.Properties.Count - 1
            If fldLoop.Properties(j).Name = "DisplayControl" Then
               Debug.Print "  " & fldLoop.Name & ": " & _
                  fldLoop.Properties(j).Name & ", " & CStr(fldLoop.Properties(j).Value)
            End If
         Next j
      Next fldLoop
   Next i
      .Close
   End With

End Sub

put that in your database and run it. Ignore the syntax and name. It will give you the values of all the display controls from all the fields in all your tables. But what you can learn from this is how to assign these values to table objects when you run code. For instance, in vba help under "the field object" page, one of the bullet points says this:

Determine what types of functionality are supported for a given field by using the Attributes property and Properties collection.

So you can see if the display control property of fields are READ/WRITE or just READ this way. But you don't have to do that. Just try to assign a number (111 is combo box, which you obviously already know) to .fields(j).properties("DisplayControl") and if it throws an error then obviously you know that the property is READ ONLY in vba. Hence, you can't do what you want to do. The only way then is manually through the interface.

That's how you get things like this done, and learn something in the process!

[snoopy2003]

Thank you for your detailed explanation and quick response.
I appreciate your support.

As you recommended - I will consider to avoid using combo box in my tables definition in the future.

Thank you for your time and for your help