Deposit value in Text Box from form code module SQL Query

  • Thread starter Thread starter Greg Green
  • Start date Start date
G

Greg Green

I'd like to write a query within the code module of a form, then have the
query's result (a single value) appear in a text box. Setting the text box's
Value property to the query doesn't run the query or retrieve the value to
the TextBox. Setting a QueryDef variable to the SQL String doesn't do it.
How is this done?

Thanks in advance.
 
An SQL select statement returns a recordset not an individual text value.

You could use an SQL statement with an ADO open recordset function
then get the value of the column or field you want and set the textbox equal
to that value.

Here is how you could do this with ADO code.

Dim rst As New ADODB.Recordset
Dim txtSQL As String

txtSQL = "Select [YourFieldNameHere] from [YourTable] Where
[YourFieldNameHere] = " 'SOMEVALUE' "
rst.Open txtSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

YourTextBox = rst("YourFieldNameHere")
rst.close
Set rst =Nothing

Hope this helps.

Bill
 
Try setting the focus to the textBox first then setting the text property
Like this


me.txtBox.setfocus
me.txtBox.text = rst("YourFieldNameHere")

Bill


Greg Green said:
Hi Bill

Thanks for the input. This gets very close, but after considerable wrangling
still hangs at the line

YourTextBox = rst("YourFieldNameHere")

With the error "You can't assign a value to this object".

I've tried many versions of the syntax i.e.:

me.txtBox.value = rst("YourFieldNameHere")
me.txtBox = rst("YourFieldNameHere")

No syntax seems to get past that error. It's a valid SQL String, I tested
the SQL in the query designer.

Greg



Bill said:
An SQL select statement returns a recordset not an individual text value.

You could use an SQL statement with an ADO open recordset function
then get the value of the column or field you want and set the textbox equal
to that value.

Here is how you could do this with ADO code.

Dim rst As New ADODB.Recordset
Dim txtSQL As String

txtSQL = "Select [YourFieldNameHere] from [YourTable] Where
[YourFieldNameHere] = " 'SOMEVALUE' "
rst.Open txtSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

YourTextBox = rst("YourFieldNameHere")
rst.close
Set rst =Nothing

Hope this helps.

Bill


Greg Green said:
I'd like to write a query within the code module of a form, then have the
query's result (a single value) appear in a text box. Setting the text box's
Value property to the query doesn't run the query or retrieve the
Click to expand...
Click to expand...
value
to
Click to expand...
Click to expand...
 
Hi Bill

I remember the pesky SetFocus issue with the text property - I tried that
and now it says the property is read only and can't be set. When I try to
set the TextBox control's Value property to the rst result, as I mentioned
it says "You can't assign a value to this object" so I think the control
must be under a lock of some type at run-time. The code compiles, but the
compiled does miss things. I'll investigate further around the locking
issue.

Thanks for the help, if you have any ideas give a shout. - Greg

Bill said:
Try setting the focus to the textBox first then setting the text property
Like this


me.txtBox.setfocus
me.txtBox.text = rst("YourFieldNameHere")

Bill


Greg Green said:
Hi Bill

Thanks for the input. This gets very close, but after considerable wrangling
still hangs at the line

YourTextBox = rst("YourFieldNameHere")

With the error "You can't assign a value to this object".

I've tried many versions of the syntax i.e.:

me.txtBox.value = rst("YourFieldNameHere")
me.txtBox = rst("YourFieldNameHere")

No syntax seems to get past that error. It's a valid SQL String, I tested
the SQL in the query designer.

Greg



Bill said:
An SQL select statement returns a recordset not an individual text value.

You could use an SQL statement with an ADO open recordset function
then get the value of the column or field you want and set the textbox equal
to that value.

Here is how you could do this with ADO code.

Dim rst As New ADODB.Recordset
Dim txtSQL As String

txtSQL = "Select [YourFieldNameHere] from [YourTable] Where
[YourFieldNameHere] = " 'SOMEVALUE' "
rst.Open txtSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

YourTextBox = rst("YourFieldNameHere")
rst.close
Set rst =Nothing

Hope this helps.

Bill


I'd like to write a query within the code module of a form, then
Click to expand...
have
the
query's result (a single value) appear in a text box. Setting the text
box's
Value property to the query doesn't run the query or retrieve the
Click to expand...
value
to
the TextBox. Setting a QueryDef variable to the SQL String doesn't
Click to expand...
do
it.
How is this done?

Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
Bill

Got it. The query string I am using references a global date value obtained
from a Global Variable set in a code module. This global date was parsed
correctly when run in the query designer, but incorrectly (required # date#
format) when run as a SQL string within a code module.

So things now work like a charm. Thanks, Greg


Greg Green said:
Hi Bill

I remember the pesky SetFocus issue with the text property - I tried that
and now it says the property is read only and can't be set. When I try to
set the TextBox control's Value property to the rst result, as I mentioned
it says "You can't assign a value to this object" so I think the control
must be under a lock of some type at run-time. The code compiles, but the
compiled does miss things. I'll investigate further around the locking
issue.

Thanks for the help, if you have any ideas give a shout. - Greg

Bill said:
Try setting the focus to the textBox first then setting the text property
Like this


me.txtBox.setfocus
me.txtBox.text = rst("YourFieldNameHere")

Bill


Greg Green said:
Hi Bill

Thanks for the input. This gets very close, but after considerable wrangling
still hangs at the line

YourTextBox = rst("YourFieldNameHere")

With the error "You can't assign a value to this object".

I've tried many versions of the syntax i.e.:

me.txtBox.value = rst("YourFieldNameHere")
me.txtBox = rst("YourFieldNameHere")

No syntax seems to get past that error. It's a valid SQL String, I tested
the SQL in the query designer.

Greg



An SQL select statement returns a recordset not an individual text value.

You could use an SQL statement with an ADO open recordset function
then get the value of the column or field you want and set the textbox
equal
to that value.

Here is how you could do this with ADO code.

Dim rst As New ADODB.Recordset
Dim txtSQL As String

txtSQL = "Select [YourFieldNameHere] from [YourTable] Where
[YourFieldNameHere] = " 'SOMEVALUE' "
rst.Open txtSQL, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

YourTextBox = rst("YourFieldNameHere")
rst.close
Set rst =Nothing

Hope this helps.

Bill


I'd like to write a query within the code module of a form, then have
the
query's result (a single value) appear in a text box. Setting the text
box's
Value property to the query doesn't run the query or retrieve the value
to
the TextBox. Setting a QueryDef variable to the SQL String
Click to expand...
Click to expand...
doesn't
do
Click to expand...
Click to expand...
 
Back
Top