By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,705 Members | 1,279 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,705 IT Pros & Developers. It's quick & easy.

Link Table Field Name (Caption Property) to TextBox on Form

P: 9
Hello Good Morning,

I am currently trying to change the Caption Property value within a table by allowing the user to type into a textbox and then click on the Submit button to change the Caption for the field within the VB Script.

Basically I have a form called "Enter_Table_Headings". Located on the form I have a textbox labeled "CapChange". The table is Named "tblInUnit" and the database is labeled fin_db.

I've been bashing my head on this problem all week it's the last thing I need to do Please Help!
May 22 '07 #1
Share this Question
Share on Google+
15 Replies

P: 9
Hello Good Morning,

I am currently trying to change the Caption Property value within a table by allowing the user to type into a textbox and then click on the Submit button to change the Caption for the field within the VB Script.

Basically I have a form called "Enter_Table_Headings". Located on the form I have a textbox labeled "CapChange". The table is Named "tblInUnit" and the database is labeled fin_db.

I've been bashing my head on this problem all week it's the last thing I need to do Please Help!
I thought I should add what code as was using as well.

Here is the code for the button:

Dim dbs As DAO.Database
Set dbs = DBEngine(0).CurrentDb
Dim tdfNew As TableDef
Dim strTableName As String
Set dbs = DBEngine(0).CurrentDb
Set tdfNew = dbs.TableDefs("INSERVICE")
SetAccessProperty tdfNew.Fields("INSERVICE"), "Caption" = CapChange

Here is the Module:

Function SetAccessProperty(obj As Object, _
strName As String, varSetting As Variant) As Boolean
Dim prp As Property
Const conPropNotFound As Integer = 3270


obj.Properties(strName) = varSetting
obj.Properties.Refresh
SetAccessProperty = True
Set varSetting = CapChange.Value


ExitSetAccessProperty:
Exit Function
End Function
May 22 '07 #2

puppydogbuddy
Expert 100+
P: 1,923
I thought I should add what code as was using as well.

Here is the code for the button:

Dim dbs As DAO.Database
Set dbs = DBEngine(0).CurrentDb
Dim tdfNew As TableDef
Dim strTableName As String
Set dbs = DBEngine(0).CurrentDb
Set tdfNew = dbs.TableDefs("INSERVICE")
SetAccessProperty tdfNew.Fields("INSERVICE"), "Caption" = CapChange

Here is the Module:

Function SetAccessProperty(obj As Object, _
strName As String, varSetting As Variant) As Boolean
Dim prp As Property
Const conPropNotFound As Integer = 3270


obj.Properties(strName) = varSetting
obj.Properties.Refresh
SetAccessProperty = True
Set varSetting = CapChange.Value


ExitSetAccessProperty:
Exit Function
End Function

[size=3][font=Times New Roman]<<<The table is Named "tblInUnit">>>> appears to be in conflict with your set statements below:[/font][/size]

[size=3][font=Times New Roman] [/font][/size]

[size=3][font=Times New Roman]Set tdfNew = dbs.TableDefs("INSERVICE")
SetAccessProperty tdfNew.Fields("INSERVICE"), "Caption" = CapChange[font=Tahoma][/font][/font][/size]
May 22 '07 #3

P: 9
Ive rectified the conflict but I'm still getting a user-type error. I can't even trouble shoot this until I get past this error. If you want to throw out my code and start anew that would be fine too. Thank you.
May 22 '07 #4

puppydogbuddy
Expert 100+
P: 1,923
Ive rectified the conflict but I'm still getting a user-type error. I can't even trouble shoot this until I get past this error. If you want to throw out my code and start anew that would be fine too. Thank you.
Ajani,

Since you mentioned it, I thought I would try writing my own code as shown below. It is not tested or compiled. Before you can try to compile it, you have to change the generic references I made to the form and control names. Hopefully, the code will be easier for you to compile and execute than your code. At least you have something to compare to.

You have to place this function in a standard module.....and call it from your form with the required arguments. Hope this helps.
-------------------------------------------------------------------------------------
[CODE

[font=Tahoma][size=3]Public Function SetTableFieldCaption(sTable As String, sFld As String) As Boolean[/size][/font]

[font=Tahoma][size=3] On Error Resume Next[/size][/font]

[font=Tahoma][size=3] [/size][/font]

[font=Tahoma][size=3] Dim dbs As DAO.Database[/size][/font]

[font=Tahoma][size=3] Dim tdf As DAO.TableDef[/size][/font]

[font=Tahoma][size=3] Dim fld As DAO.Field[/size][/font]

[font=Tahoma][size=3] Dim prp As DAO.Property[/size][/font]

[font=Tahoma][size=3] [/size][/font]

[font=Tahoma][size=3] Dim strCaption As String[/size][/font]

[font=Tahoma][size=3] [/size][/font]

[font=Tahoma][size=3] Set dbs = CurrentDb[/size][/font]

[font=Tahoma][size=3] Set tdf = dbs.TableDefs(sTable)[/size][/font]

[font=Tahoma][size=3] [/size][/font]

[font=Tahoma][size=3] ‘capture the Caption Change value from the textbox on the form[/size][/font]

[font=Tahoma][size=3] strCaption = Forms!YourFormName!ChangeCaption.Value[/size][/font]

[font=Tahoma][size=3] [/size][/font]

[font=Tahoma][size=3] ' Loop through all the fields in the table until match found to Form sFld [/size][/font]

[font=Tahoma][size=3] [/size][/font]

[font=Tahoma][size=3] For Each fld In tdf.Fields[/size][/font]

[font=Tahoma][size=3] If fld.Name = sFld Then [/size][/font]

[font=Tahoma][size=3] ' Set the field caption property[/size][/font]

[font=Tahoma][size=3] fld.Properties("Caption") = strCaption[/size][/font]

[font=Tahoma][size=3] End If[/size][/font]

[font=Tahoma][size=3] [/size][/font]

[font=Tahoma][size=3] ' If the property didn't exist, there will have been an error and[/size][/font]

[font=Tahoma][size=3] ' it needs to be added.[/size][/font]

[font=Tahoma][size=3] If Err.Number = 0 Then[/size][/font]

[font=Tahoma][size=3] ' No problem. Property existed and the value was set.[/size][/font]

[font=Tahoma][size=3] ElseIf Err.Number = 3270 Then[/size][/font]

[font=Tahoma][size=3] ' This error means the property was not found. We need to create it.[/size][/font]

[font=Tahoma][size=3] Err.Clear[/size][/font]

[font=Tahoma][size=3] [/size][/font]

[font=Tahoma][size=3] Set prp = fld.CreateProperty("Caption", dbText, strCaption)[/size][/font]

[font=Tahoma][size=3] fld.Properties.Append prp[/size][/font]

[font=Tahoma][size=3] [/size][/font]

[font=Tahoma][size=3] If Err.Number <> 0 Then[/size][/font]

[font=Tahoma][size=3] MsgBox Err.Description, vbExclamation, "Error"[/size][/font]

[font=Tahoma][size=3] End If[/size][/font]

[font=Tahoma][size=3] Else[/size][/font]

[font=Tahoma][size=3] ' Not sure what the error was. Report to user.[/size][/font]

[font=Tahoma][size=3] MsgBox Err.Description, vbExclamation, "Error"[/size][/font]

[font=Tahoma][size=3] End If[/size][/font]

[font=Tahoma][size=3] Next[/size][/font]

[font=Tahoma][size=3] [/size][/font]

[font=Tahoma][size=3] Set prp = Nothing[/size][/font]

[font=Tahoma][size=3] Set tdf = Nothing[/size][/font]

[font=Tahoma][size=3] Set dbs = Nothing[/size][/font]

[font=Tahoma][size=3]End Function[/size][/font]



[/code]
May 23 '07 #5

P: 9
Now we are getting somewhere. Thank you. Ok right now the code is searching for a table field labeled "ChangeCaption". If I add a field to the table "InUnit" and label it ChangeCaption it will find that. It then returns a Value cannot be Null. So I think it might then be searching the table for a particular value.

Am I missing a Set statement somewhere in order for it to set the Caption property to the Textbox (labeled "ChangeCaption") ??

Thanks again for your help on this.


This is what I have:

OnClick for button =SetTableFieldCaption("InUnit","ChangeCaption")


Option Compare Database
Option Explicit




Public Function SetTableFieldCaption(sTable As String, sFld As String) As Boolean

On Error Resume Next


Dim dbs As DAO.Database

Dim tdf As DAO.TableDef

Dim fld As DAO.Field

Dim prp As DAO.Property



Dim strCaption As String



Set dbs = CurrentDb

Set tdf = dbs.TableDefs(sTable)



'capture the Caption Change value from the textbox on the form

strCaption = Forms!Enter_Invoice_Detail!ChangeCaption.Value



' Loop through all the fields in the table until match found to Form sFld



For Each fld In tdf.Fields

If fld.Name = sFld Then

' Set the field caption property

fld.Properties("Caption") = strCaption

End If



' If the property didn't exist, there will have been an error and

' it needs to be added.

If Err.Number = 0 Then

' No problem. Property existed and the value was set.

ElseIf Err.Number = 3270 Then

' This error means the property was not found. We need to create it.

Err.Clear



Set prp = fld.CreateProperty("Caption", dbText, strCaption)

fld.Properties.Append prp



If Err.Number <> 0 Then

MsgBox Err.Description, vbExclamation, "Error"

End If

Else

' Not sure what the error was. Report to user.

MsgBox Err.Description, vbExclamation, "Error"

End If

Next



Set prp = Nothing

Set tdf = Nothing

Set dbs = Nothing

End Function
May 23 '07 #6

puppydogbuddy
Expert 100+
P: 1,923
[font=Tahoma][size=3]Adjani,[/size][/font]

[font=Tahoma][size=3] [/size][/font]

[font=Tahoma][size=3]You should be ok after fixes 1 and 2 below:[/size][/font]

[font=Tahoma][size=3]1. You supplied the wrong info to the sFld argument in your call statement.[/size][/font]

[font=Times New Roman][size=3]OnClick for button =SetTableFieldCaption("InUnit","ChangeCaption")[/size][/font]

[size=3][font=Times New Roman] [/font][/size]

[size=3][font=Times New Roman]sTable and sFld should be the table and field whose caption you want to change to the value in the ChangeOptions textbox.[font=Tahoma][/font][/font][/size]

[font=Tahoma][size=3] [/size][/font]

[font=Tahoma][size=3]2. Leave the “ChangeOptions” textbox unbound [/size][/font]

[font=Tahoma][size=3] [/size][/font]

[font=Tahoma][size=3]As shown below, the code sets the caption property for he field in the call statement to the value in the “ChangeOptions” textbox. [/size][/font]

[font=Times New Roman][size=3]'capture the Caption Change value from the textbox on the form
strCaption = Forms!Enter_Invoice_Detail!ChangeCaption.Value[/size][/font]

[size=3][font=Times New Roman] [/font][/size]

[size=3][font=Times New Roman]For Each fld In tdf.Fields
If fld.Name = sFld Then
' Set the field caption property
fld.Properties("Caption") = strCaption
End If[font=Tahoma][/font][/font][/size]
May 23 '07 #7

P: 9
Closer still... I made the modifications now I recieve :

"Invalid use of Null"

"User defined properties do not support a Null Value"

This appears for every field within the table.


The call statement for the button now reads

=SetTableFieldCaption("InUnit","INSERVICE")

INSERVICE being the name of one of the fields within table InUnit.

Thanks again for your help!
May 23 '07 #8

puppydogbuddy
Expert 100+
P: 1,923
Closer still... I made the modifications now I recieve :

"Invalid use of Null"

"User defined properties do not support a Null Value"

This appears for every field within the table.


The call statement for the button now reads

=SetTableFieldCaption("InUnit","INSERVICE")

INSERVICE being the name of one of the fields within table InUnit.

Thanks again for your help!
Try changing Call statement to make sFld a variant and see what happens:
[font=Tahoma]Public Function SetTableFieldCaption(sTable As String, sFld As Variant) As Boolean[/font]
[font=Tahoma][/font]
[font=Tahoma]and change fld loop to bypass nulls:[/font]
[font=Tahoma][/font]
[font=Tahoma][font=Times New Roman][size=3]For Each fld In tdf.Fields
If IsNull(fld.Name) Then[/size][/font]

[size=3][font=Times New Roman] Next[/font][/size]

[font=Times New Roman][size=3]Else [/size][/font]

[size=3][font=Times New Roman] If fld.Name = sFld Then
' Set the field caption property
fld.Properties("Caption") = strCaption[/font][/size]

[font=Times New Roman][size=3]End If[/size][/font]

[/font]
[font=Tahoma][/font]
[font=Tahoma][/font]
May 23 '07 #9

P: 9
Alrighty Done.

Now I'm getting ..

Object Variable or With object Variable not Set.
May 23 '07 #10

puppydogbuddy
Expert 100+
P: 1,923
Alrighty Done.

Now I'm getting ..

Object Variable or With object Variable not Set.
Can you find which line is highlighted? You can do the debugging as follows:

[font='Times New Roman']Put the word STOP in the code line before the looping code starts. Then run your code again. When the code executes, it will literally open the VBA window up and highlight the line. From there, you can "step" through the code. Use <F8> for this. It will advance the code, one line at a time. As you pause on each line, if you move your cursor over and hover over your variables and values, Access will pop-up values in yellow baloon boxes. You can check your code in this way to make sure that everything is working as it should. [/font]
May 23 '07 #11

P: 9
Ok .This is the line that has the issue.

If fld.Name = sFld Then

To be more precise fld.Name
May 23 '07 #12

puppydogbuddy
Expert 100+
P: 1,923
Ok .This is the line that has the issue.

If fld.Name = sFld Then

To be more precise fld.Name

Hmmm, the only thing that comes to mind at the moment:
Try changing the two Next statements in the code to>>> Next fld
May 23 '07 #13

P: 9
I fixed it and now I know what has been giving me fits. First of all I did something really dumb.

Problem # 1:

The Textbox while it was unbound it's filter lookup was set to Database Default so it kept checking the database for CaptionChange not finding it and giving NULL.
So that was changed.

Now here is the code for anyone else that may end up with this need:

Option Compare Database
Option Explicit





Public Function SetTableFieldCaption(sTable As String, sFld As Variant) As Boolean

'On Error Resume Next


Dim dbs As DAO.Database

Dim tdf As DAO.TableDef

Dim fld As DAO.Field

Dim prp As DAO.Property



Dim strCaption As String



Set dbs = CurrentDb

Set tdf = dbs.TableDefs(sTable)


'Forms("Enter_Invoice_Detail").ChangeCaption.Value = "Test"




'capture the Caption Change value from the textbox on the form

strCaption = IIf(IsNull(Forms("Enter_Invoice_Detail").Change.Va lue) = True, "NULL", Forms!Enter_Invoice_Detail!Change.Value)




' Loop through all the fields in the table until match found to Form sFld





For Each fld In tdf.Fields
If IsNull(fld.Name) Then
End If
If fld.Name = sFld Then
' Set the field caption property
fld.Properties("Caption") = strCaption
Exit For
End If
Next












' If the property didn't exist, there will have been an error and

' it needs to be added.

' If Err.Number = 0 Then
'
' ' No problem. Property existed and the value was set.
'
' ElseIf Err.Number = 3270 Then
'
' ' This error means the property was not found. We need to create it.
'
' Err.Clear
'
'
'
' Set prp = fld.CreateProperty("Caption", dbText, strCaption)
'
' fld.Properties.Append prp
'
'
'
' If Err.Number <> 0 Then
'
' MsgBox Err.Description, vbExclamation, "Error"
'
' End If
'
' Else
'
' ' Not sure what the error was. Report to user.
'
' MsgBox Err.Description, vbExclamation, "Error"
'
' End If






Set prp = Nothing

Set tdf = Nothing

Set dbs = Nothing

End Function
May 23 '07 #14

P: 9
I fixed it and now I know what has been giving me fits. First of all I did something really dumb.

Problem # 1:

The Textbox while it was unbound it's filter lookup was set to Database Default so it kept checking the database for CaptionChange not finding it and giving NULL.
So that was changed.

Now here is the code for anyone else that may end up with this need:

Option Compare Database
Option Explicit


Public Function SetTableFieldCaption(sTable As String, sFld As Variant) As Boolean

'On Error Resume Next

Dim dbs As DAO.Database

Dim tdf As DAO.TableDef

Dim fld As DAO.Field

Dim prp As DAO.Property


Dim strCaption As String



Set dbs = CurrentDb

Set tdf = dbs.TableDefs(sTable)


'Forms("Enter_Invoice_Detail").ChangeCaption.Value = "Test"


'capture the Caption Change value from the textbox on the form

strCaption = IIf(IsNull(Forms("Enter_Invoice_Detail").Change.Va lue) = True, "NULL", Forms!Enter_Invoice_Detail!Change.Value)


' Loop through all the fields in the table until match found to Form sFld



For Each fld In tdf.Fields
If IsNull(fld.Name) Then
End If
If fld.Name = sFld Then
' Set the field caption property
fld.Properties("Caption") = strCaption
Exit For
End If
Next


' If the property didn't exist, there will have been an error and

' it needs to be added.

' If Err.Number = 0 Then
'
' ' No problem. Property existed and the value was set.
'
' ElseIf Err.Number = 3270 Then
'
' ' This error means the property was not found. We need to create it.
'
' Err.Clear
'
'
'
' Set prp = fld.CreateProperty("Caption", dbText, strCaption)
'
' fld.Properties.Append prp
'
'
'
' If Err.Number <> 0 Then
'
' MsgBox Err.Description, vbExclamation, "Error"
'
' End If
'
' Else
'
' ' Not sure what the error was. Report to user.
'
' MsgBox Err.Description, vbExclamation, "Error"
'
' End If






Set prp = Nothing

Set tdf = Nothing

Set dbs = Nothing

End Function


Thank you very much for your help I couldn't have done it without you!
May 23 '07 #15

puppydogbuddy
Expert 100+
P: 1,923
Thank you very much for your help I couldn't have done it without you!
You are very welcome. I am glad I could help.
May 24 '07 #16

Post your reply

Sign in to post your reply or Sign up for a free account.