set or get default value in linked table

[clarimuse]

I need to retrieve the default value of one field in a linked table and if the default value has not been set then I need to set it.

How can I do this in code from the front end db.
thanks

 

[Zion7]

clarimuse,
referencing linked tables is done in the same manner as local tables.
Wheteher it be a recordset or tabldef, link tables are treated, as if they are local.

If txtState.DefaultValue = vbNullString Then
txtState.DefaultValue = "TX"
End If

If you're setting this from a "remote procedure", I think the tableDefs collection, would allow you to manipulate the property.

Maybe something like this...


Sub SingleTableFieldsProperties()
Dim fld As Field, td As TableDefs, prp As Property
Set td = CurrentDb.TableDefs
For Each fld In td("tblCountries").Fields
For Each prp In fld.Properties
Debug.Print prp.Name
If prp.Name = "DefaultValue" Then
Debug.Print prp.Value
End If
Next
Next
End Sub


Not sure how to set the value???

 

[VBslammer]

The schema of a linked table is read-only, but you can set the default value of the control that is bound to the field at runtime:

Private Sub Form_Load()
  If Me!FirstName.DefaultValue = Empty Then
    Me!FirstName.DefaultValue = "=" & Chr(34) & "John" & Chr(34)
  End If
End Sub

If you open the table using a direct connection as opposed to a local link, you can make design changes:

  Dim db As DAO.Database
  Set db = OpenDatabase("F:\apps\databases\accts.mdb")
  
  With db.TableDefs("TheTableName")
    .Fields("NumericField").Properties("DefaultValue") = 1
    .Fields("TextField").Properties("DefaultValue") = "=" & Chr(34) & "Whatever" & Chr(34)
  End With

  db.TableDefs.Refresh

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[clarimuse]

thanks VBSlammer - a modified version of your second bit of code worked a treat.