TableDef Fields Caption

G

Gary D.

Not sure if this is the correct group for this question.

I am trying to create a table in VBA and create and define the table's
fields.

I can create the tabledef and the fields okay, but I want to add
certain properties to certain fields.

This is what I have so far...

Dim ldbCreateCtrls As Database, tdfCreateCtrls As TableDef
Dim fdlCreateCtrls As Field, prpCreateCtrls As Property

Set tdfCreateCtrls = ldbCreateCtrls.CreateTableDef("tbl_APP_CONTROLS")
Set fdlCreateCtrls = tdfCreateCtrls.CreateField("CtrlDBName", dbText,
30)
tdfCreateCtrls.Fields.Append fdlCreateCtrls
tdfCreateCtrls.Fields("CtrlDBName").DefaultValue = "database name"
tdfCreateCtrls.Fields("CtrlDBName").Required = True
tdfCreateCtrls.Fields("CtrlDBName").AllowZeroLength = False
ldbCreateCtrls.TableDefs.Append tdfCreateCtrls

and these are all effective.

However, I wish to edit a field's Caption, Format and Description.
But despite trying all manner of different code, I cannot get the
correct technique.

I've tried
Set prpCreateCtrls =
tdfCreateCtrls.Fields("CtrlDBName").CreateProperty("Caption", dbText,
"Database")
which works but then fails on
tdfCreateCtrls.Fields.Append prpCreateCtrls
which I understand, because Caption is already in the collection.

So what is the correct technique?
 
A

Allen Browne

Here's a piece of code that tests whether the property exists. If so, it
sets it to the value you want. If not, it creates and and initializes it
with the desired value.

Hope that's useful.

Function SetPropertyDAO(obj As Object, strPropertyName As String, _
intType As Integer, varValue As Variant, Optional strErrMsg As String) As
Boolean
On Error GoTo ErrHandler
'Purpose: Set a property for an object, creating if necessary.
'Arguments: obj = the object whose property should be set.
' strPropertyName = the name of the property to set.
' intType = the type of property (needed for creating)
' varValue = the value to set this property to.
' strErrMsg = string to append any error message to.

If HasProperty(obj, strPropertyName) Then
obj.Properties(strPropertyName) = varValue
Else
obj.Properties.Append obj.CreateProperty(strPropertyName, intType,
varValue)
End If
SetPropertyDAO = True

ExitHandler:
Exit Function

ErrHandler:
strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & _
" not set to " & varValue & ". Error " & Err.Number & " - " &
Err.Description & vbCrLf
Resume ExitHandler
End Function

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function
 
G

Gary D.

Here's a piece of code that tests whether the property exists. If so, it
sets it to the value you want. If not, it creates and and initializes it
with the desired value.

Hope that's useful.

Function SetPropertyDAO(obj As Object, strPropertyName As String, _
intType As Integer, varValue As Variant, Optional strErrMsg As String) As
Boolean
On Error GoTo ErrHandler
'Purpose: Set a property for an object, creating if necessary.
'Arguments: obj = the object whose property should be set.
' strPropertyName = the name of the property to set.
' intType = the type of property (needed for creating)
' varValue = the value to set this property to.
' strErrMsg = string to append any error message to.

If HasProperty(obj, strPropertyName) Then
obj.Properties(strPropertyName) = varValue
Else
obj.Properties.Append obj.CreateProperty(strPropertyName, intType,
varValue)
End If
SetPropertyDAO = True

ExitHandler:
Exit Function

ErrHandler:
strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & _
" not set to " & varValue & ". Error " & Err.Number & " - " &
Err.Description & vbCrLf
Resume ExitHandler
End Function

Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function
Click to expand...

Thanks Allen.

I've tried this but it doesn't work for a field's Caption, Format and
Description.

When I use it to try to set a field's Caption I get
CtrlDBName.Caption not set to testcaption. Error 3219 - Invalid
operation.

Any suggestions?
 
D

Douglas J. Steele

Gary D. said:
Thanks Allen.

I've tried this but it doesn't work for a field's Caption, Format and
Description.

When I use it to try to set a field's Caption I get
CtrlDBName.Caption not set to testcaption. Error 3219 - Invalid
operation.
Click to expand...

How are you calling Allen's function?
 
G

Gary D.

How are you calling Allen's function?
Click to expand...

Using the following...

Dim fldProp As Object
Set fldProp = tdfCreateCtrls.Fields("CtrlDBName")
strMsg = ""
Debug.Print SetPropertyDAO(fldProp, "Caption", dbText, "test caption",
strMsg)
Debug.Print (strMsg)

I also tried...
Dim fdlCreateCtrls As Field
Set fdlCreateCtrls = tdfCreateCtrls.CreateField("CtrlDBName", dbText,
30)
tdfCreateCtrls.Fields.Append fdlCreateCtrls
strMsg = ""
Debug.Print SetPropertyDAO(fdlCreateCtrls, "Caption", dbText, "test
Debug.Print (strMsg)
 
D

Douglas J. Steele

The 2nd approach should work.

You definitely want to declare the field object as a Field, not simply as an
Object. (actually, to be perfectly correct, you should declare it as a
DAO.Field)

You report getting an error back in strMsg. What does the function call
return?

Allen may have additional suggestions. Let's hope he sees this post...
 
A

Allen Browne

This example shows how to set the Caption in a single line from the
Immediate Window:

? SetPropertyDAO(dbEngine(0)(0).TableDefs("MyTable").Fields("MyField"),
"Caption", dbText, "My Field")

Make sure your fld has been appended to the Fields collection before you try
to set its properties like this.
 
G

Gary D.

This example shows how to set the Caption in a single line from the
Immediate Window:

? SetPropertyDAO(dbEngine(0)(0).TableDefs("MyTable").Fields("MyField"),
"Caption", dbText, "My Field")

Make sure your fld has been appended to the Fields collection before you try
to set its properties like this.
Click to expand...

Yes, I did try appending it, but received an error message - something
like 'invalid operation' (I'll check).

Having read thru documentation I saw mention that unless the Caption
(for example) has been set previously in table design view then it
can't be set in VBA.
 
G

Gary D.

The 2nd approach should work.

You definitely want to declare the field object as a Field, not simply as an
Object. (actually, to be perfectly correct, you should declare it as a
DAO.Field)

You report getting an error back in strMsg. What does the function call
return?

Allen may have additional suggestions. Let's hope he sees this post...
Click to expand...

I get the message...
CtrlDBName.Caption not set to test caption. Error 3219 - Invalid
operation.

and the function returns False.
 
D

Douglas J Steele

Gary D. said:
Yes, I did try appending it, but received an error message - something
like 'invalid operation' (I'll check).

Having read thru documentation I saw mention that unless the Caption
(for example) has been set previously in table design view then it
can't be set in VBA.
Click to expand...

That's true, but if you look at Allen's code, he's creating the property if
it doesn't already exist.
 
P

Paul Overway

Try this....

Dim fld as Field

Set fld = tdf.Fields("SomeField")

fld.Properties("Caption") = "Whatever"

If err.number <> 0 Then
'Must not have a caption property
fld.Properties.Append
fld.CreateProperty("Caption",dbText,"Whatever")
End if
 
G

Gary D.

Try this....

Dim fld as Field

Set fld = tdf.Fields("SomeField")

fld.Properties("Caption") = "Whatever"

If err.number <> 0 Then
'Must not have a caption property
fld.Properties.Append
fld.CreateProperty("Caption",dbText,"Whatever")
End if
Click to expand...

Thanks Paul.
I've tried it but...

Error 3270 - Property not found.
caused by the code -
fld.Properties("Caption") = "Whatever"

and even when error-trapped (On Error) it still doesn't add the
Caption.
 
P

Paul Overway

Sorry, you needed to add On Error Resume Next...revised:

Dim fld as Field

On error resume next

Set fld = tdf.Fields("SomeField")

fld.Properties("Caption") = "Whatever"

If err.number <> 0 Then
'Must not have a caption property
fld.Properties.Append
fld.CreateProperty("Caption",dbText,"Whatever")
End if
 
G

Gary D.

Success!

I suspect the reason the field append didn't work was because the
tabldefs collection had not been refreshed.

Once I moved the SetPropertyDAO() function after the
..TableDefs.Refresh it worked okay.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Appending a "Caption" Property to Fields in a recordset 8
Assign Index key to field programmatically 4
Access Access Table Array 1
Creating a table in code 2
how to make sure database is open in procedure 4
Help with Access VBA to create a new file and import textfile data 7
avoiding 31/12/1899 with empty dates 9
Creating relations between linked tables. 2

Top