how do you change the default value of a field in a table from VBA? 1

[Eric6]

hello,
(its me again

i was wonderring how i could change the default value
of a text field in a table from VBA...

the text field is 5 char. long
and represents a fiscal year (ei: this year is 02/03)

so if i dont want the program to be absolete next year
i need to give the user a method of changing the default
date from a form

i already have a "year list" form made
that lists all the years the program uses...
i was wonderring if i could just make a check box or something like that, that would simply change the default
property in the table

i found some code that hints on how i could do this but i dont know how to use it...

here is some code i found on the internet

Function SetDefaultValueInTable( _ strTable As String, _ strColumnName As String)
_ As Boolean ' ' Example usage:
bRetVal=SetDefaultValueInTable("tblYourTableName","YourColumnName&quot ' Dim ThisDB
As Database

On Error Resume Next Set ThisDB = CurrentDb With
ThisDB.TableDefs(strTable).Fields(strColumnName) Err.Clear
.Properties("DefaultValue&quot = 0 'If Err.Number = 3270 Then ' .Properties.Append
.CreateProperty("DefaultValue", dbInteger) ' .Properties.Refresh 'End If End With
Set ThisDB = Nothing

SetDefaultValueInTable = (Err = 0)

End Function

but i dont know how to use it
it doesn't make sence to me

isn't there just a tablename.fieldname.defaultvalue = something property i could set in VBA?

thank you in advance,
Eric

 

[RickSpr]

Your sample code seems to have lost its line breaks. Putting them back in, and eliminating some comments, gives the following:

Function SetDefaultValueInTable( _
             strTable As String, _ 
             strColumnName As String) _ 
             As Boolean 
' 
' Example usage: bRetVal=SetDefaultValueInTable ("tblYourTableName","YourColumnName")
'
    Dim ThisDB As Database

    On Error Resume Next
    Set ThisDB = CurrentDb
    With ThisDB.TableDefs(strTable).Fields(strColumnName)
        Err.Clear
        .Properties("DefaultValue") = 0 
     End With
     Set ThisDB = Nothing

     SetDefaultValueInTable = (Err = 0)

End Function

To use this, you'd need to modify the line ".Properties("DefaultValue&quot = 0". The final 0 is the new default value. You'd want to substitute your new default date for it. Or, you could add an argument so the calling procedure can pass the new default value, and use the argument in place of the 0. Watch out, though: The DefaultValue property setting is a little different from most. You'd probably have to convert the argument date into a string, surrounded by "#" characters, before you assign it to the DefaultValue property. Rick Sprague

 

[Eric6]

thank you very much
it makes much more sence now
(i'm still not sure what the Error code does but whatever..)
it's working perfectly now

Eric