Can I set the DefaultValue of a field in a table directly?

  • Thread starter Thread starter Jim Thacker
  • Start date Start date
Sorry Roger, I didn't make my question clear.
I want to change the DefaultValue of a table field when I change the value
of a textbox (using the AfterUpdate Event) in an unrelated form.
 
Unless you open a table in Design View, either physically or in VBA code,
and save it, there is no way to change the DefaultValue of a field. The
problem is that as soon as you dirty a form with a new record, you must
first save that record before you can save the database, and no one else can
be in the database while you are saving it.

You can change the DefaultValue of a textbox on a form, but it won't "stick"
unless you save it in the form's design view.

I sense that there is an ulterior motive for your request, so it might be a
good idea to tell us what your are trying to accomplish. Perhaps there is a
better way to do it.
 
Well, yes you can, but I generally don't advise it. Here are two routines:

Function SetAccessProperty(obj As Object, strName As String, _
intType As Integer, varSetting As Variant) As Boolean
Dim prp As Property
Const conPropNotFound As Integer = 3270

On Error GoTo ErrorSetAccessProperty
' Explicitly refer to Properties collection.
obj.Properties(strName) = varSetting
obj.Properties.Refresh
SetAccessProperty = True

ExitSetAccessProperty:
Exit Function

ErrorSetAccessProperty:
If Err = conPropNotFound Then

' Create property, denote type, and set initial value.
Set prp = obj.CreateProperty(strName, intType, varSetting)
' Append Property object to Properties collection.
obj.Properties.Append prp
obj.Properties.Refresh
SetAccessProperty = True
Resume ExitSetAccessProperty
Else
MsgBox Err & ": " & vbCrLf & Err.Description
SetAccessProperty = False
Resume ExitSetAccessProperty
End If
End Function



Sub ModifyDefaultValue(tablename As String, _
fieldname As String, _
fieldvalue As Variant)
Dim dbs As Database, tdf As TableDef, fld As Field
Dim blnReturn As Boolean

' Return reference to current database.
Set dbs = CurrentDb
' Return reference to table and field.
Set tdf = dbs.TableDefs(tablename)
Set fld = tdf.Fields(fieldname)
' Call SetAccessProperty function.
blnReturn = SetAccessProperty(fld, _
"DefaultValue", dbLong, fieldvalue)
' Evaluate return value.
If blnReturn = True Then
Debug.Print "Property set successfully."
Else
Debug.Print "Property not set successfully."

End If
End Sub


'You could call the preceding function with a procedure such as the
following:
Sub test()
Call ModifyDefaultValue("FieldFormatTable", "Field1", 100)
End Sub


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
I found the method!
1) I have a form that sets the value of BP in the TextBox named "BP"
2) Upon entering the value in the TextBox, I want that value to update the
DefaultValue of the BP field in Table "TAB"

Here is the Code:

Private Sub BP_AfterUpdate()
Dim BP_Val As Byte
Dim dbsST As Database
Set dbsST = CurrentDb
Dim currTable As TableDef

BP_Val = Form!BP

Set currTable = dbsST.TableDefs!TAB
currTable.Fields!BP.DefaultValue = BP_Val

End Sub
 
Back
Top