Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 952
  • Last Modified:

Setting field properties using VBA code

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
RobJanine
Asked:
RobJanine
  • 8
  • 8
  • 6
2 Solutions
 
RobJanineAuthor Commented:
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
 
rockiroadsCommented:
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
 
rockiroadsCommented:
urm, setting the Type may not work, I believe it has to be done a different way
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
rockiroadsCommented:
single is
DB_SINGLE

hang on whilst I try remember the other field settings
Ive done it before, just cant remember exact syntax
0
 
RobJanineAuthor Commented:
OK, thanks
Yeah the text properties need to be set to 'Combox and Row source to 'Table Query' etc etc

Cheers
0
 
rockiroadsCommented:
Sorry, Ive got to come back to this
problem at work
0
 
RobJanineAuthor Commented:
No worries

Cheers
0
 
rockiroadsCommented:
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
 
harfangCommented:
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
 
RobJanineAuthor Commented:
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
 
harfangCommented:
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
 
RobJanineAuthor Commented:
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
 
harfangCommented:
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
 
rockiroadsCommented:
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
 
rockiroadsCommented:
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
 
RobJanineAuthor Commented:
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
 
harfangCommented:
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
 
RobJanineAuthor Commented:
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
 
harfangCommented:
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
 
RobJanineAuthor Commented:
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
 
harfangCommented:
No problem. Good luck!
(°v°)
0
 
rockiroadsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 8
  • 8
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now