Populating a Text Box with the results of a Query

[EdwardMartinIII]

If this has been answered elsewhere, please point me in the right direction -- I've searched the FAQ and the forum and I THOUGHT I'd pretty much exhausted the examples in Access.

I have a form.
On the form is a list box "StoryListBox"
StoryListBox contains a list of Story Titles culled from the one table in the file
On the form is a text box "Text_Story"
What I would LIKE is when I click on one of the stories listed in "StoryListBox", a query is built to extract the body of the story from the table and display it in "Text_Story".

This is what I have so far:

Private Sub StoryListBox_Click()
  Dim Location, SQLString, qdf, dbs
  Location = StoryListBox.ListIndex
  Label13.Caption = StoryListBox.ItemData(Location) ' Print the Title
  ' Build the Query String
    SQLString = "SELECT [Table1].[Body (HTML)] FROM Table1 WHERE [Table1].[Title]=" & Chr(34) & StoryListBox.ItemData(Location) & Chr(34) & ";"
  ' Create the dbQuery
    Set qdf = CurrentDb.CreateQueryDef("StoryRetrieve", SQLString)
  Label16.Caption = SQLString ' So I can see my SQL string
  ' Populate Text_Story.text via StoryRetrieve
  ' Delete the Query
  DoCmd.DeleteObject acQuery, "StoryRetrieve"
End Sub
[\code]

Label16 is how I peek at the SQLString as I construct it.  The SQLString seems to do just fine and the Query gets built hunky dory.  When I manually run the Query, it works great each time, so I believe my query is formulated correctly.

When I try

[code]Me!Text_Story.Text = StoryRetrieve[\code]

I receive an error that reads [i]Run-time error '2185': You can't reference a property or method for a control unless the control has the focus.[/i]

Now, I already have a line (which I've deleted from my sample listing) that changes the background color of "Text_Story" and it seems to be able to do so just fine, so I'm not entirely sure why it suddenly loses focus (this isn't one of those ADD controls, is it?)

Then I tried 

[code]Me!Text_Story.DefaultValue = StoryRetrieve[\code]

It [i]doesn't[/i] crash, but populates the window with 

[i]#Name?[/i]

as does

[code]Me!Text_Story.DefaultValue = (StoryRetrieve)[\code]

This seems embarrasingly fundamental and I'll admit this is my first attempt at making a database and a little form, so I might be missing something profoundly simple.  With that in mind, I sure would appreciate someone pointing me in the right direction!

Thanks!

Edward

 

[EdwardMartinIII]

It seems closer to use

Me!Text_Story.Value
[\code]

instead of

[code]
Me!Text_Story.Text
[\code]

but although I can insert strings in there, I still can't seem to figure out how to put in the results of a Query.  When I try

[code]
Me!Text_Story.Value = StoryRetrieve
[\code]

then the thing goes blank upon running.

Any advice?  Am I on a useful track?

Thanks!

Edward

 

[ds2728]

Hello,

I have done this often, although I do not use qdf. I build my sql string then open a recordset, then set a variable or or story_text on your form equal to the field value of your choice, then close the recordset. This eliminates having to delete the qry. and should make it alittle easier. Also instead of using & chr(34) & in your building the qry you can replace that with a single quote and again make it easier. I used to do the same as you and have since switched to the single quote.

Following is sample code I tested.

Dim db As Database
Dim rs As Object
Dim con As Object
Dim strSql As String
Set db = CurrentDb


strSql = "SELECT * FROM [design srb] "
strSql = strSql & "WHERE (([design srb].bearing_size) "
strsql = strsql & "= '" & Me!Text0.Value & "') "

Set con = Application.CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset&quot
rs.Open strSql, con, 1 ' 1 = adOpenKeyset
Me!Text2 = rs!IRD01
rs.Close


where me!text0.value is your storylistbox. and bearing_size would be equal to your storyname, I think

text2 would equal your story text to be displayed.

I tested this and it worked great.

Good Luck

Dave

 

[EdwardMartinIII]

Thanks Dave,

I think I've translated your code correctly (although I could have screwed something up) to:

  Dim Location
  Dim db As Database
  Dim rs As Object
  Dim con As Object
  Set db = CurrentDb

  Location = StoryListBox.ListIndex
  Label13.Caption = StoryListBox.ItemData(Location)
    SQLString = "SELECT [Table1].[Body (HTML)] FROM Table1 WHERE [Table1].[Title]=" & Chr(34) & StoryListBox.ItemData(Location) & Chr(34) & ";"
    Set con = Application.CurrentProject.Connection
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open SQLString, con, 1   ' 1 = adOpenKeyset
        Me!Text_Story = rs!IRD01
    rs.Close
[\code]
When I try to execute it, I receive a
[code]
Compile error: User-defined type not defined
[\code]
on the line "Dim db As Database"

So, I tried making "db" a variant by not specifying a type.  I'm not even sure why it's there, 'cause I don't use it in the code (neither did you, near as I could tell).

Then, the error is
[code]
Run-time error '3265': Item cannot be found in the collection corresponding to the requested name or ordinal
[\code]
on the line "Me!Text_Story = rs!IRD01"

So, I guess I'm still open to suggestions.

If I only am displaying a single item from the db, by the way, why would I want to retrieve a recordset instead of composing a query for the specific thing I wanted, firing off the query, then deleting the query?  I ask so I can better understand possible answers.

Thanks!

Edward

 

[EdwardMartinIII]

My latest attempt was

Me!Text_Story = (StoryRetrieve)
[\code]
Which I really figured would work, but it just made the window as featureless as my local cineplex.

So, I've a string of failures on my belt here.

Am I really trying something so weird as to populate a textbox with the results of a query?!

Hoping for advice...!

Thanks,

Edward

 

[EdwardMartinIII]

Still can't get a darn thing working.

I've tried

Me!Text_Story = DoCmd.RunSQL "SELECT [Table1].[Body] FROM Table1 WHERE [Table1].[Title]='Bad Boys';"
[\code]
which gave me a "compile error -- expected end of statement" which puzzles me mightily because I believe I followed the HELP example closely.

I tried all of the following (when they fail, I comment them out rather than delete them so I don't waste my time trying something I've already tried), with no success:
[code]
    ' Me!Text_Story.DefaultValue = StoryRetrieve ' fails
    ' Me!Text_Story.DefaultValue = (StoryRetrieve) ' fails
    ' Me!Text_Story.DefaultValue = "StoryRetrieve" ' fails
    ' Me!Text_Story.ControlSource = SQLString ' does not work
    ' Me!Text_Story.ControlSource = "StoryRetrieve" ' produces "#Name?"
    ' Me!Text_Story = DoCmd.OpenQuery(StoryRetrieve, acViewNormal, acReadOnly) ' Error expected function or variable
    ' Me!Text_Story = DoCmd.RunSQL(StoryRetrieve) ' Error expected function or variable
    ' Me!Text_Story = DoCmd.RunSQL (SQLString) ' Error expected function or variable
    ' Me!Text_Story = DoCmd.RunSQL "SELECT [Table1].[Body] FROM Table1 WHERE [Table1].[Title]='Bad Boys';" ' doesn't appear to be liked by the compiler
    ' Me!Text_Story.Value = SQLString ' just prints SQLString, but seems closer
    ' Me!Text_Story.Value = StoryRetrieve ' results in a blank box
    ' Me!Text_Story.Value = (StoryRetrieve) ' results in a blank box
    ' Me!Text_Story.Value = "StoryRetrieve" ' prints "StoryRetrieve" in the box
    ' Me!Text_Story.Value = CurrentDb.Execute("StoryRetrieve") ' Expected function or variable
    ' Me!Text_Story.Text = StoryRetrieve ' produces a runtime error 2185
    ' Me!Text_Story = StoryRetrieve ' produced a blank screen
    ' Me!Text_Story = "StoryRetrieve" ' prints "StoryRetrieve" in the window.
    ' Me!Text_Story = (StoryRetrieve) ' prints nothing in the window
[\code]

Just because I'm not even sure I'm able to get the results of the query OUT, I tried
[code]
    Response = MsgBox((StoryRetrieve), vbInformation, "Look at this")
[\code]
And all I get is a blank window.

I would REALLY apreciate a suggestion or bit of advice.

How can I get the results of a Query to display in a text box?  Or anywhere, frankly?

Thanks!

Edward