Avatar of brayle
brayle
 asked on

dao default field value.

If a field does not exist in the current table I append it. But I would like to also provide a default value. How do I do that? Working in vb6.
thanks,
 br
Visual Basic ClassicMicrosoft Access

Avatar of undefined
Last Comment
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

8/22/2022 - Mon
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Full on code:

    Dim dbs As Database
    Dim fld As DAO.Field
    Set dbs = CurrentDb
    Set fld = dbs.TableDefs("YourTableName").Fields("YourFieldName")
    fld.DefaultValue = 0

    Set fld = Nothing
    Set dbs = Nothing

Short version:

CurrentDb("YourTableName").Fields("YourFieldName").DefaultValue = "SomeValue" ' text example
brayle

ASKER
Does that append the field or just add the default data?
thx
br
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Just adds the Default Value.

"But I would like to also provide a default value."

Also:

"Working in vb6."

You may need to use the OpenDatabase Method instead of CurrentDB.
Your help has saved me hundreds of hours of internet surfing.
fblack61
brayle

ASKER
Here I succeed in appending the field but then it does not do the default

Dim db As DAO.Database
Dim tb As DAO.TableDef
Dim fld As DAO.field

Set db = OpenDatabase(PassedPathNName, True)
Set tb = db.TableDefs("tblUserSettings")

      If tb.Fields.count < 17 Then '12-19-12 added
        Set fld = tb.CreateField("TruckRowHash", dbText) '
        tb.Fields.Append fld

'When it gets to this line it says "Field not found in this collection"
        Set fld = db.TableDefs("UserSettings").Fields("TruckRowHash")
        fld.DefaultValue = 0

    end if

thx
br
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Dim dbs As DAO.Database
    Dim fld As DAO.Field
    Set dbs = OpenDatabase("YourDBName")
    Set fld = dbs.TableDefs("YourTableName").Fields("YourFieldName")
    fld.DefaultValue = 0 ' or whatever value
    dbs.Close
    Set fld = Nothing
    Set dbs = Nothing
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Not sure what is going on ...  unless you have to Create the DefaultValue Property ... but I don't think so.

    Dim dbs As DAO.Database
    Dim fld As DAO.Field
    Set dbs = OpenDatabase("YourDBName")
    With dbs
        Set fld = .TableDefs("YourTableName").Fields("YourFieldName")
        fld.DefaultValue = 0 ' or whatever value
        .Close
    End With
    Set fld = Nothing
    Set dbs = Nothing
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
brayle

ASKER
I'm afraid the use of "With dbs" did not help. It acts like the appended field is not really ready for action.  I guess I will just try some other way unless you have other ideas.
Thanks,
br
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

Try one or the other or both of the Refresh lines:


        Set fld = tb.CreateField("TruckRowHash", dbText) '
        tb.Fields.Append fld
       db.TableDefs.Refresh
        tb.Fields.Refresh
brayle

ASKER
Found the problem, I had left off the "tbl" prefix I was using with my table.
Thanks for your perseverance and help.
Brian
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

I have to go, but ... If that did not work ... look up CreateProperty in vba Help ... and 'create' the DefaultValue property for that Field.

Also ... another approach is using the ALTER syntax ...
In fact ... see this:
https://www.experts-exchange.com/questions/23927409/'ALTER-TABLE-tblGLBudgetBalance-ALTER-COLUMN-intFiscalYear-NUMERIC-ADD-DEFAULT-WITH-0-'-What-syntax-wrong-in-statement-for-MS-Access.html?cid=295

Other Examples ... not directly related:

CurrentDb.Execute "ALTER TABLE tblGLBudgetBalance ALTER COLUMN  intFiscalYear number"

Public Function mChangeFieldType(sFld) As Boolean

    Dim tdf As TableDef, db As DAO.Database, fld As DAO.Field
    Set db = CurrentDb
    For Each tdf In db.TableDefs
        For Each fld In tdf.Fields
            If fld.Name = sFld Then
                If fld.Type = dbLong Then
                    CurrentDb.Execute "ALTER TABLE [" & tdf.Name & "] ALTER COLUMN " & sFld & " TEXT(55)"
                End If
            End If
        Next
    Next
brayle

ASKER
I'm all set, it's working, thanks again.
Brian
ASKER CERTIFIED SOLUTION
DatabaseMX (Joe Anderson - Former Microsoft Access MVP)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.