query results to a text box

[EdwardMartinIII]

I have a small database with a single short-story table, containing (among other fields): Title, Author, Body.

On a form, I list in a listbox all stories by Title.

When you click on a Title in the listbox, the code destroys a Query called RetrieveStory (it's a deliberate leftover from the previous search). Then it rebuilds a new Query of the same name, but with the different value for Title.

This query returns exactly the Body text of the story. I've tested it manually and it works great.

But I can't get the results of the Query to display anywhere programmatically. I can't get it to appear as a caption of a text field, or in a text box, or in a msgbox or anything.

I'm fairly new to Access, but I'm not new to searching for answers (I can't seem to find any relevant), nor am I new to trying stuff out and using the help files. I've a bit of VB experience, as well as lots of other languages. I can't even tell if the error is InabilityToCollectData or IgnoranceOfDisplayMethod.

I would be happy to post my code here if that'll help, but if anyone has any ideas, I would really appreciate knowin' 'em.

Thanks,

Edward

 

[PaulBricker]

I think what you have to do is use the query you've created to open the Recordset and then set the value of your textbox, msgbox...whatever to the recordset value. It would look like this

Dim rst as DAO.Recordset
Set rst = CurrentDb.OpenRecordset("RetrieveStory",dbOpenDyanset)
rst.MoveFirst
Forms!YourForm!YourTextbox = rst!YourQueryFieldNameHere
Set rst = Nothing

Tack this code onto the code that creates your query. Try it and post back if you have problems.

Paul

 

[EdwardMartinIII]

Thanks for writing back.

I added this:

    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("RetrieveStory", dbOpenDyanset)
    rst.MoveFirst
    ' Forms!YourForm!YourTextbox = rst!YourQueryFieldNameHere
    Me!Text_Story = rst!Body
    Set rst = Nothing

The first error produced "Compile error: User-defined type not defined" for the line Dim rst As DAO.Recordset.

So, I tried making it a variant by changing it to

Dim rst ' As DAO.Recordset

The next error was "Run-time error '3001': Invalid argument." for the line Set rst = CurrentDb.OpenRecordset("RetrieveStory", dbOpenDyanset) (I don't know what dbOpenDyanset is -- does it matter?)

So, that's where I'm at.

Did I do something wrong?

Cheers,

Edward