Set text box value with an SQL statement 1

[Dor100]

Access 97's Help article on ControlSource Property says you can set a text box value with an SQL statement, but it doesn't give an example of the syntax for this. I have tried numerous ways, including multiple quotation marks, equal signs and parentheses, and searched for the answer but come up dry. Does anyone know the syntax for doing this? In my current case, the record source for the report is simply a table, and I want to set a text equal to: "select count(field) from table where otherfield = "resolved";" Thanks.

 

[jebry]

Hi!

Use the DCount:

=DCount("Field", "Table", "OtherField = 'Resolved'&quot

hth


Jeff Bridgham
bridgham@purdue.edu

 

[CosmoKramer]

Try:

=DCount("[field]","table","otherfield = 'resolved'")

Hoc nomen meum verum non est.

 

[Dor100]

Thank you both. I did wind up using the dcount. However, this business of being able to use sql statements in form and report text boxes has been a source of frustration before, and now I'd very much like to find the answer. Combo boxes are no problem, it's text boxes that won't cooperate. If anyone knows what the syntax I would much appreciate it.

 

[CajunCenturion]

I too have wanted to do the same, but after several attempts, I decided to use a bigger hammer.

For what it's worth, I have a few text boxes on a form that get their individual from a table which is no way is related to the Record Source of the form. My solution was to set the Control Source of the text box to a Public Function:

=GetData("FieldName")

Then I wrote the following Public Function in a code module similar to the following:

Public Function GetData(rStr_FieldName As String) As String
   
   Dim lRst_WellData          As ADODB.Recordset
   Dim lStr_ThisValue         As String
   Dim lStr_SQLStmt           As String

   lStr_ThisValue = vbNullString
   Set lRst_WellData = New ADODB.Recordset
   lRst_WellData.ActiveConnection = CurrentProject.Connection
   lStr_SQLStmt = "SELECT " & rStr_FieldName & " FROM tblTableName"
   lRst_WellData.Open lStr_SQLStmt, , adOpenForwardOnly, adLockReadOnly
   If (lRst_WellData.State = adStateOpen) Then
      If ((lRst_WellData.BOF = False) And (lRst_WellData.EOF = False)) Then
         lRst_WellData.MoveFirst
         lStr_ThisValue = Nz(lRst_WellData.Fields(rStr_FieldName), vbNullString)
      End If
      lRst_WellData.Close
   End If

   If Not (lRst_WellData Is Nothing) Then
      If (lRst_WellData.State = adStateOpen) Then
         lRst_WellData.Close
      End If
      Set lRst_WellData = Nothing
   End If
   
   GetData = UCase(lStr_ThisValue)

End Function

I suspect that there may be a more direct approach, but this was the way I choose to solve the problem at the time, given current committments and such. Perhaps when I have more time, I'll continue to investigate, or maybe some other contributor may provide a better approach.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CosmoKramer]

Check out Access help for the DLookup function. The syntax is similar to DCount but it will return a single value, if present, otherwise it returns a Null....

Hoc nomen meum verum non est.

 

[dhookom]

I have never seen where Help suggests that you can use a SQL string as a Control Source. It can be used as a Record Source or Row Source.
If the domain/table/query is the same as the recordset of the report, you are wasting resources. The following takes advantage of the report's recordset.
=Sum(Abs([OtherField] = "Resolved&quot* [Field])

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Dor100]

In Acc 97, the Help page is titled as "ControlSource Property." The first few lines say,

"You can use the ControlSource property to specify what data appears in a control. You can display and edit data bound to a field in a table, query, or SQL statement."

There are times when I really want to do this, but so far it has eluded me.

I just found these new thread replies, so I haven't had an opportunity to check out CajunCenturion's rather intriguing (can we say 'understatement,' anyone?) reply, but it looks like one of those times when the Enterprise shows up when you're lost in the woods and forgot your compass. (Thanks, dude)

"For what it's worth, I have a few text boxes on a form that get their individual from a table which is no way is related to the Record Source of the form" For what it's worth? Can I send you the truck load of gold bars by UPS, or regular mail?

Thank you all for your help in this matter. If anyone comes across the info about SQL statements, that would be great, as I would still love to be able to do that, but there's a lot of great stuff here nonetheless.

 

[CajunCenturion]

If they're gold bars, just send the keys. I'll be glad to come and get the truck and drive it home.

Take care,

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[dhookom]

Just for clarification according to Help "You can use the ControlSource property to specify what data appears in a control. You can display and edit data bound to a field [/red] in a table, query, or SQL statement."

Bound to a field [/red]suggests a control source is the field from a table, query, or SQL statement. This is consistent with my experience.


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[CosmoKramer]

Dor100,

Once again, look into the DLookup function. It's the same as an SQL statement, but without the SELECT, FROM, and WHERE....

Hoc nomen meum verum non est.

 

[dhookom]

I agree with CosmoKramer that the simplest solution is DLookup(). DLookup() is similar to Cajun's code but allows specification of a table/query and a where clause.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[CajunCenturion]

Certainly the DLookup function is workable in many cases. But in some cases, depending on the complexity of the needed query, the DLookUp may not be powerful enough to meet the need.

Unfortunately, the example that I posted was a very simple SQL statement and as such, I fear was mis-understood. The purpose was to illustrate a technique whereby any SQL statement, regardless of how complex, with subsequent processing of the recordset can be used to extract a single value and bind that value to individual control.

I hope that we didn't get too wrapped up in a few trees and thereby missed the forest.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[dhookom]

faq701-4233 can return values from one or more records in any table. The code is used to concatenate values from multiple records but can just as easy return one value.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.