Solved

Setting field properties using VBA code

Posted on 2006-06-19
24
758 Views
Last Modified: 2012-05-05
Hi

Thanks to Experts I have half of my problem sorted.

This code below Adds a field to a table when my application is opened.

But what I need it to do now is set the field properties.
I have two diferent field types im adding to a table

     Text :       Display Control          set to       Combobox
                     Row Source type       set to       Table/Query
                     Row Source              set to       TblStockList
                     Bound Column           set to       2
                     Column Count            set to       2

     Number :   Field Size                  set to       Single
                     Format                      set to       Fixed
                     Decimal Places           set to       3
                     Default Value             set to       0

Here is the code used to add the field 'Test' to table 'TblBuildingSizeDetails1'  

Can anyone please help by showing me how to add these properties to my Field 'Test' (Both ways, Number & Text)

Thanking you


    Rob.
0
Comment
Question by:RobJanine
  • 8
  • 8
  • 6
24 Comments
 

Author Comment

by:RobJanine
ID: 16932486
Sorry  

heres the code:

    If (DLookup("[VersionNumber]", "BackEndVersion") = 1#) Then
   
    sSql = "update BackEndVersion set VersionNumber = 1.1"
    DoCmd.RunSQL sSql
   
    Dim db As dao.Database
    Dim tbl As dao.TableDef
    Dim col As Object
   
    Set db = dao.OpenDatabase("J:\DBS QTCv2_be.MDB")
    Set tbl = db.TableDefs("TblBuildingSizeDetails1")
    Set col = tbl.CreateField("Test", DB_TEXT)
    tbl.Fields.Append col
   
    db.Close
    Set db = Nothing
   
    DoCmd.DeleteObject acTable, "TblBuildingSizeDetails1"
    DoCmd.TransferDatabase acLink, "Microsoft Access", "J:\DBS QTCv2_be.MDB", acTable, "TblBuildingSizeDetails1", "TblBuildingSizeDetails1", False
           
    End If
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16932743
RobJanine

to add text, u already have it

Set col = tbl.CreateField("Test", DB_TEXT)

to make it into a number, try this

DB_INTEGER

u can modify existing fields by changing the type

tbl.Fields("Test").Type = DB_INTEGER

0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16932761
urm, setting the Type may not work, I believe it has to be done a different way
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16932781
single is
DB_SINGLE

hang on whilst I try remember the other field settings
Ive done it before, just cant remember exact syntax
0
 

Author Comment

by:RobJanine
ID: 16932808
OK, thanks
Yeah the text properties need to be set to 'Combox and Row source to 'Table Query' etc etc

Cheers
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16932815
Sorry, Ive got to come back to this
problem at work
0
 

Author Comment

by:RobJanine
ID: 16932826
No worries

Cheers
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16933670
Hi RobJanine,

I think this is it, well its the best I  could do quickly for you

after you create and append your field, i.e.

tbl.Fields.Append col

try this

'String field
tbl.Fields("Test").Properties("DisplayControl").Value = 111
tbl.Fields("Test").Properties("RowSourceType").Value = "Table/Query"
tbl.Fields("Test").Properties("RowSource").Value = "TblStockList"
tbl.Fields("Test").Properties("BoundColumn").Value = 2
tbl.Fields("Test").Properties("ColumnCount").Value = 2
tbl.Fields("Test").Properties("LimitToList").Value = True


'Number field - note u create it using dbSingle or DB_SINGLE
tblFields("TestNum").Properties("DecimalPlaces") = 3
tblFields("TestNum").Properties("Format") = "Fixed"



Note, u may be able to make use of col instead of tblFields.("Test"), this is because col is set to that
0
 
LVL 58

Accepted Solution

by:
harfang earned 250 total points
ID: 16934341
Hello RobJanine

Here is my module dealing with properties. You will need it, or something similar, for this project.

-------------------------------------------------------------------------------------------------------->8----
Option Compare Database
Option Explicit

Function GetProperty(DAO_Object As Object, Name As String)
'
' Returns named property of the DAO object,
' or Null on error or property not found.
'
On Error Resume Next
    GetProperty = DAO_Object.Properties(Name)
    If Err Then
        Err.Clear
        DAO_Object.Properties.Refresh
        GetProperty = DAO_Object.Properties(Name)
        If Err Then Err.Clear: GetProperty = Null
    End If
   
End Function

Function SetProperty( _
    DAO_Object As Object, _
    Name As String, _
    Optional Value As Variant = Null, _
    Optional DataType As DAO.DataTypeEnum = dbText _
    ) As Boolean
'
' Sets the property or creates a new property
' If called without a value, the property is deleted.
'
    Dim prp As DAO.Property

' ongoing error checking:
On Error Resume Next

    ' delete property?
    If IsNull(Value) Then
        DAO_Object.Properties.Delete Name
        SetProperty = (Err = 0)
        DAO_Object.Properties.Refresh
        Err.Clear
        Exit Function
    End If

    ' get the property if it exists:
    Set prp = DAO_Object.Properties(Name)
    If prp Is Nothing Then
        Err.Clear
        ' create a new one.
        Set prp = DAO_Object.CreateProperty(Name, DataType, CStr(Value))
        If prp Is Nothing Then
            Err.Clear
            Exit Function
        End If
        ' and append
        DAO_Object.Properties.Append prp
        If Err Then Err.Clear: Exit Function
        DAO_Object.Properties.Refresh
       
    ElseIf prp.Value <> Value Then
        ' set to new value
        prp.Value = Value
        If Err Then
            Err.Clear
            Exit Function
        End If
    End If
   
    SetProperty = True

End Function
-------------------------------------------------------------------------------------------------------->8----

You can then (following rockiroads' code above) do something like this:

    Set fld = tbl.Fields("Test")
    SetProperty fld, "DisplayControl", acComboBox, dbLong
    SetProperty fld, "RowSourceType", "Table/Query", dbText
    SetProperty fld, "RowSource", "tblStockList", dbText

etc. You can create a field manually and then explore the properties collection of that field to get all the names, values and types of the needed properties...

    Dim prp As DAO.Property
    With CurrentDb
        With .TableDefs("tblTestTable")
            With .Fields("tblMyNewField")
                For Each prp In .Properties
                    Debug.Print prp.Type, prp.Name, prp.Value
                Next prp
            End With
        End With
    End With

In this particular case, you can also just go ahead an create these properties using .CreateProperty, because you know they do not exist at that point. However, you might later need to make changes to properties...

Cheers!

(°v°)
0
 

Author Comment

by:RobJanine
ID: 16939862
Hi
Cant seem to get your code to work Rockiroads get an error "Property not found"

and as for Harfang, I cant understand how your code works, can you help with .CreateProperty method?? as this code will only ever run once and allways on a new field

Cheers  

   Rob.
0
 
LVL 58

Expert Comment

by:harfang
ID: 16939992
Like this?

    With <your field object>
        .Properties("DisplayControl") = acComboBox
        .Properties.Append .CreateProperty("RowSourceType", dbText, "Table/Query")
        .Properties.Append .CreateProperty("RowSource", dbMemo, "Customers")
        .Properties.Append .CreateProperty("BoundColumn", dbInteger, 2)
        .Properties.Append .CreateProperty("ColumnCount", dbInteger, 2)
    End With

(°v°)
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:RobJanine
ID: 16940015
Sorry.. but where is this code run?
I need it to run after the other code that creates the field 'Test' in table 'TblBuildingSizeDetails1'
The code is run on load of the applications Switchboard



Rob.
0
 
LVL 58

Expert Comment

by:harfang
ID: 16940047
Well, following {http:/Q_21890268.html#16929594}, I suppose you now have a centralized function that performs the following checks:
 • is a BE present and linked?
 • (if not, either link to one or create a new one and link to it)
 • what is the version number of the BE?
 • if it's too low, warn user, create a backup, and run all needed upgrade functions, in sequence

So I guess this belongs to your Upgrade_10_11() function, no?

(°v°)
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16940072
Hi Rob, if the property is not found, then you have to create it.
Its much like when you set/create properties when disabling bypass fix.

I think you should only need to do it for displayccontrols
e.g.

col.Properties("DisplayControl").Value = 111
If Err.Number = 3270 Then

    Set prp = db.CreateProperty("DisplayControl", dbInteger, 111)
   
    col.Properties.Append prp
End If

col.Properties("RowSourceType").Value = "Table/Query"
col.Properties("RowSource").Value = "TblStockList"
col.Properties("BoundColumn").Value = 2
col.Properties("ColumnCount").Value = 2
col.Properties("LimitToList").Value = True


0
 
LVL 65

Expert Comment

by:rockiroads
ID: 16940078
let me put the proper code in

dim pro as dao.property


on error resume next

col.Properties("DisplayControl").Value = 111
If Err.Number >0 Then
    if Err.Number = 3270
        Err.Clear
        Set prp = db.CreateProperty("DisplayControl", dbInteger, 111)
        if Err.Number > 0 Then
            msgbox "Failed to create Property. " & Err.Description
        Else
            col.Properties.Append prp
        End if
    Else
        Msgbox "Failed to set property. " & err.Description
    End If
End If


Once u set it to be of type combobox, then the other properties should get created. Its then just a matter of setting them
0
 

Author Comment

by:RobJanine
ID: 16940133
It worked Rockiroads, Made it into a combobox, but have added the other properties code to code above and it errored.
Where should the other code go, and does it need to be '.CreateProperty' or 'Col.Properties'

Cheers   Rob.
0
 
LVL 58

Expert Comment

by:harfang
ID: 16940163
rockiroads,

> Once u set it to be of type combobox, then the other properties should get created.

I'm afraid not. This happens in the table design assistant, but not when you change the control type through code.

RobJanine,

This is why it's much easier to use a wrapper function to set and create properties, like those I provided at {http:#16934341}, and much like in rockiroads' last comment...

Cheers!
(°v°)
0
 

Author Comment

by:RobJanine
ID: 16940245
Sorry guys, This is harder than I thought it would be, im not to good on VBA.
Anyway, this is what I have got so far :
it's changing to 'combobox' but 'RowSource' etc is not working

Thanks sooo much for your help  

 If (DLookup("[VersionNumber]", "BackEndVERSION") = 1#) Then
   
    sSql = "Update BackEndVERSION Set VersionNumber = 1.1"

    DoCmd.RunSQL sSql
   
    Dim db As dao.Database
    Dim tbl As dao.TableDef
    Dim col As Object
   
    Set db = dao.OpenDatabase("J:\DBS QTCv2_be.MDB")
    Set tbl = db.TableDefs("TblBuildingSizeDetails1")
    Set col = tbl.CreateField("Test", DB_TEXT)
    tbl.Fields.Append col
   
       
    Dim pro As dao.Property

    On Error Resume Next

    col.Properties("DisplayControl").Value = 111

    If Err.Number > 0 Then
        If Err.Number = 3270 Then
            Err.Clear
           Set prp = db.CreateProperty("DisplayControl", dbInteger, 111)

           If Err.Number > 0 Then
               MsgBox "Failed to create Property. " & Err.Description
           Else
                col.Properties.Append prp
            End If
        Else
            MsgBox "Failed to set property. " & Err.Description
        End If
    End If
   
    col.Properties("RowSourceType").Value = "Table/Query"
    col.Properties("RowSource").Value = "TblStockList"
    col.Properties("BoundColumn").Value = 2
    col.Properties("ColumnCount").Value = 2
    col.Properties("LimitToList").Value = True

    DoCmd.DeleteObject acTable, "TblBuildingSizeDetails1"
    DoCmd.TransferDatabase acLink, "Microsoft Access", "J:\DBS QTCv2_be.MDB", acTable, "TblBuildingSizeDetails1", "TblBuildingSizeDetails1", False
   
    db.Close
    Set db = Nothing
                           
    End If

Cheers
Rob.
0
 
LVL 58

Expert Comment

by:harfang
ID: 16940324
Rob, I'll leave you in the expert hands of rockiroads. I get the feeling you don't even read my comments.

Success with your application!
(°v°)
0
 

Author Comment

by:RobJanine
ID: 16940358
Sorry Harang, Was reading your comments but to be honest I couldnt understand the code, Im not very good with VBA, only use it for simple commands etc Normally use Queries.
Thanks for your help anyway.
Rob.
0
 
LVL 58

Expert Comment

by:harfang
ID: 16940437
No problem. Good luck!
(°v°)
0
 
LVL 65

Assisted Solution

by:rockiroads
rockiroads earned 250 total points
ID: 16949862
Harfang, regarding my comment - "Once u set it to be of type combobox, then the other properties should get created", yes your right, though I found that some defaults are set so no need to create all of them. Thanks for this though

Hi RobJanine,

Ive had another look at it seems some properties have to be created. Harfang's idea of using a function is the best way as you want to avoid unnecessary duplication of code.



This I used to test and worked fine

   
   
'So this is part of the code where u created the field
    Set col = tbl.CreateField("Test", DB_TEXT)
    tbl.Fields.Append col
   
'Now we set properties
'We pass in the db object (one for the remote db)
'Pass in the field object (one we defined as col)
'Pass in the name of the property we want to set
'Pass in that property's datatype
'Pass in the value for that property

    SetAndCreateProperty db, col, "DisplayControl", dbInteger, 111
    SetAndCreateProperty db, col, "RowSource", dbText, "TblStockList"
    SetAndCreateProperty db, col, "BoundColumn", dbInteger, 2
    SetAndCreateProperty db, col, "ColumnCount", dbInteger, 2


And the function SetAndCreateProperty is ...


Public Function SetAndCreateProperty(ByRef db As DAO.Database, ByRef col As Object, ByVal sProperty As String, ByVal iType As Integer, ByVal vValue) As Boolean

    Dim prp As DAO.Property
   
   
    On Error Resume Next

    'Assume the worst    
    SetAndCreateProperty = False
   
    Err.Clear
    col.Properties(sProperty).Value = vValue
    'if property does not exist, lets create it
    If Err.Number = 3270 Then
        Err.Clear
        Set prp = db.CreateProperty(sProperty, iType, vValue)
        If Err.Number > 0 Then
            MsgBox "Failed to create property " & sProperty & vbCrLf & Err.Description
        Else
            col.Properties.Append prp
            col.Properties(sProperty).Value = vValue
            SetAndCreateProperty = True
        End If
    ElseIf Err.Number > 0 Then
        MsgBox "Failed to assign property " & sProperty & vbCrLf & Err.Description
    Else
        SetAndCreateProperty = True
    End If
End Function





I found that RowSourceType defaults to Table/Query so  there is no need to set it, but if u want, u can just call SetAndCreateProperty

0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now