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
thanks,
br
ASKER
Does that append the field or just add the default data?
thx
br
thx
br
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.
"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.
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(PassedPathNNa me, True)
Set tb = db.TableDefs("tblUserSetti ngs")
If tb.Fields.count < 17 Then '12-19-12 added
Set fld = tb.CreateField("TruckRowHa sh", 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( "TruckRowH ash")
fld.DefaultValue = 0
end if
thx
br
Dim db As DAO.Database
Dim tb As DAO.TableDef
Dim fld As DAO.field
Set db = OpenDatabase(PassedPathNNa
Set tb = db.TableDefs("tblUserSetti
If tb.Fields.count < 17 Then '12-19-12 added
Set fld = tb.CreateField("TruckRowHa
tb.Fields.Append fld
'When it gets to this line it says "Field not found in this collection"
Set fld = db.TableDefs("UserSettings
fld.DefaultValue = 0
end if
thx
br
Dim dbs As DAO.Database
Dim fld As DAO.Field
Set dbs = OpenDatabase("YourDBName")
Set fld = dbs.TableDefs("YourTableNa me").Field s("YourFie ldName")
fld.DefaultValue = 0 ' or whatever value
dbs.Close
Set fld = Nothing
Set dbs = Nothing
Dim fld As DAO.Field
Set dbs = OpenDatabase("YourDBName")
Set fld = dbs.TableDefs("YourTableNa
fld.DefaultValue = 0 ' or whatever value
dbs.Close
Set fld = Nothing
Set dbs = Nothing
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(" YourFieldN ame")
fld.DefaultValue = 0 ' or whatever value
.Close
End With
Set fld = Nothing
Set dbs = Nothing
Dim dbs As DAO.Database
Dim fld As DAO.Field
Set dbs = OpenDatabase("YourDBName")
With dbs
Set fld = .TableDefs("YourTableName"
fld.DefaultValue = 0 ' or whatever value
.Close
End With
Set fld = Nothing
Set dbs = Nothing
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
Thanks,
br
Try one or the other or both of the Refresh lines:
Set fld = tb.CreateField("TruckRowHa sh", dbText) '
tb.Fields.Append fld
db.TableDefs.Refresh
tb.Fields.Refresh
Set fld = tb.CreateField("TruckRowHa
tb.Fields.Append fld
db.TableDefs.Refresh
tb.Fields.Refresh
ASKER
Found the problem, I had left off the "tbl" prefix I was using with my table.
Thanks for your perseverance and help.
Brian
Thanks for your perseverance and help.
Brian
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
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
ASKER
I'm all set, it's working, thanks again.
Brian
Brian
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Dim dbs As Database
Dim fld As DAO.Field
Set dbs = CurrentDb
Set fld = dbs.TableDefs("YourTableNa
fld.DefaultValue = 0
Set fld = Nothing
Set dbs = Nothing
Short version:
CurrentDb("YourTableName")