Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

dao default field value.

Posted on 2012-12-22
12
Medium Priority
?
453 Views
Last Modified: 2012-12-23
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
0
Comment
Question by:brayle
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 75
ID: 38716021
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
0
 

Author Comment

by:brayle
ID: 38716055
Does that append the field or just add the default data?
thx
br
0
 
LVL 75
ID: 38716067
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:brayle
ID: 38716068
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
0
 
LVL 75
ID: 38716070
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
0
 
LVL 75
ID: 38716078
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
0
 

Author Comment

by:brayle
ID: 38716100
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
0
 
LVL 75
ID: 38716109
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
0
 

Author Comment

by:brayle
ID: 38716131
Found the problem, I had left off the "tbl" prefix I was using with my table.
Thanks for your perseverance and help.
Brian
0
 
LVL 75
ID: 38716134
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:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Access_Coding-Macros/Q_23927409.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
0
 

Author Comment

by:brayle
ID: 38716148
I'm all set, it's working, thanks again.
Brian
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 2000 total points
ID: 38716482
You are welcome. Don't forget to close the question :-)

mx
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Suggested Courses

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question