Display results of a query in a textbox

[binoj]

I have a form in which a lot of data is related with the case number. What I want to achieve is when the user enters the case number all related information should be pulled from the linked tables and displayed in the texboxes and it works fine if I change the textbox into listbox and make the result appear in the rowsource of the list box.

If I change the list box back to textbox,
and change the command in the code below
from
Me.CLAIMEDCASEISSUES.RowSource = StrSQL
to
Me.CLAIMEDCASEISSUES = StrSQL
in the textbox I get the actual select statement displayed. How to get the result of the query displayed there.

Give below is my code

Private Sub CLAIMEDCASEID_AfterUpdate()
Dim StrSQL As String
If Mid(Me.CLAIMEDCASEID, 4, 1) = "-" Then
'And Me.CLAIMEDCASEISSUES = Null Then
StrSQL = "Select get_hearingissues(ABSHEARINGCASE.HEARINGCASEID)from " _
& "ABSHEARINGCASE WHERE ABSHEARINGCASE.HEARINGCASEID = '" & Me.CLAIMEDCASEID & "'"
Me.CLAIMEDCASEISSUES.RowSource = StrSQL
Else
StrSQL = "Select get_appealissues(ABSAPPEALSCASE.APPEALCASEID)from " _
& "ABSAPPEALSCASE WHERE ABSAPPEALSCASE.APPEALCASEID = '" & Me.CLAIMEDCASEID & "'"
Me.CLAIMEDCASEISSUES.RowSource = StrSQL
End If
End Sub


Any suggestions

 

[Beetle]

I have a form in which a lot of data is related with the case number. What I want to achieve is when the user enters the case number all related information should be pulled from the linked tables and displayed in the texboxes and it works fine if I change the textbox into listbox and make the result appear in the rowsource of the list box.

If I change the list box back to textbox,
and change the command in the code below
from
Me.CLAIMEDCASEISSUES.RowSource = StrSQL
to
Me.CLAIMEDCASEISSUES = StrSQL
in the textbox I get the actual select statement displayed. How to get the result of the query displayed there.

You cannot use a text box in this manner. A list box is the proper type of control for this, unless what you really want is to display the value from only one field in the query, then you could display that vlue in a text box.

 

[binoj]

The result of the query is indeed a single value.

 

[binoj]

I couldn't get it without using recordset . Given below is the code that worked for me. Just putting it here in case it may help someone in the future.

Private Sub CLAIMEDCASEID_AfterUpdate()
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim StrSQL As String

Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset


If Mid(Me.CLAIMEDCASEID, 4, 1) = "-" Then
StrSQL = "Select get_hearingissues(ABSHEARINGCASE.HEARINGCASEID) as ISSUECODES from " _
& "ABSHEARINGCASE WHERE ABSHEARINGCASE.HEARINGCASEID = '" & Me.CLAIMEDCASEID & "'"
rs.Open StrSQL, cnn, adOpenForwardOnly, adLockReadOnly
Me.CLAIMEDCASEISSUES.Value = rs!ISSUECODES

Else

StrSQL = "Select get_appealissues(ABSAPPEALSCASE.APPEALCASEID) AS ISSUECODES from " _
& "ABSAPPEALSCASE WHERE ABSAPPEALSCASE.APPEALCASEID = '" & Me.CLAIMEDCASEID & "'"
rs.Open StrSQL, cnn, adOpenForwardOnly, adLockReadOnly
Me.CLAIMEDCASEISSUES.Value = rs!ISSUECODES


End If
End Sub