how do you bound a textbox control propertiy to a query field

[DogLover2006]

Here is what I did I made a button create a textbox not I want it to bound the box to the query field that matches the string here is my code:

Dim frm As Form, ctlDefault As Control, ctlNew As Control
Dim intLtxt1 As Integer, intLeft, intTop1, intTop2, intNum, intSum, intNewLeft, intName
intLeft = 8760
intTop1 = 1140
intTop2 = 720
intSum = 10440 - 8760
intNum = intLeft + intSum
intLtxt1 = intNum
intNewLeft = intLeft + intSum + intSum
intName = Year(Now()) + 1
strName = intName
DoCmd.OpenForm "Temp_Federal", acDesign
Set frm = Forms!Temp_Federal
frm.RecordSource = "dbs.QueryDefs!qryTmpFed"
'Forms!Temp_Federal.RecordSource = dbs.QueryDefs!qryTmpFed
' Return Control object representing default Text box.
DoCmd.RunMacro "RenameTable"
frm.RecordSource = "dbs.QueryDefs!qryTemp_Federal"
' Form!Temp_Federal.RecordSource = dbs.QueryDefs!qryTemp_Federal
Set ctlDefault = frm.DefaultControl(acTextBox)
' Set some default properties.
With ctlDefault
.FontWeight = 700
.FontSize = 12
.Width = 1440
.Height = 300
.ControlSource = "dbs.QueryDefs!qryTemp_Federal!Field(strName)"
End With
' Create new text box.
Set ctlNew = CreateControl(frm.Name, acTextBox, , , , intLtxt1, intTop1)
' Set control's caption.
ctlNew.Name = intName

Set ctlDefault = frm.DefaultControl(acLabel)
' Set some default properties.
With ctlDefault
.FontWeight = 700
.FontSize = 12
.Width = 1440
.Height = 300
.TextAlign = 2
End With
' Create new lable.
Set ctlNew = CreateControl(frm.Name, acLabel, , , , intLtxt1, intTop2)
' Set control's caption.
ctlNew.Caption = intName
' Restore form.

DoCmd.Restore
DoCmd.RunMacro "saveform"
DoCmd.DeleteObject acTable, "TmpYears"
DoCmd.DeleteObject acQuery, "qryTmpFed"

 

[jebry]

Hi!

The property you need to set is ControlSource.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[DogLover2006]

jebry,

That is correct. I just don't know what I am doing wrong.

 

[jebry]

Hi!

Sorry missed that part of your code:

.ControlSource = dbs.QueryDefs!qryTemp_Federal!Field(strName).Name

You need to leave out the quotes, here too:

frm.RecordSource = dbs.QueryDefs!qryTemp_Federal.Name

Now you are setting them to the name of the query and the name of the field. Before you were setting them to a literal string.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[DogLover2006]

jebry,

I sending it the query named qryTemp_Federal and the field name is what ever the strName is. So if the strName is 2007 then that is the field name it should use for the ControlSource.

 

[jebry]

Then like this:

.ControlSource = strName

You need to leave out the quotes, here too:

frm.RecordSource = "qryTemp_Federal"

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[DogLover2006]

jebry,

the quotes, here works if you take them out you get a message type mismatch:

frm.RecordSource = "qryTemp_Federal"


but this does not:

.ControlSource = strName

 

[DogLover2006]

Does anyone have the answer to this?

 

[jebry]

Hi again!

The general idea here is this:

.ControlSource = "YourFieldName"

I assumed that you had the field name stored in strName. If not then you need to put the correct field name as a string.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[DogLover2006]

jebry ,

Yes, the strName is the field name. For example
strName = 2007

But the ControlSource = strName does not work It keeps telling me object not found or you have entered an expression that has an invalid reference to the property controlsource.

 

[jebry]

Hi!

Use the ctlNew.ControlSource = strName. ControlSource is not a default property.

Sorry that I missed that in your code earlier.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[DogLover2006]

jebry,

Thank you that worked great.