QueryResults >> msgbox (how the HECK do I do this?) 1

[EdwardMartinIII]

In my database, I have a Query, which produces a small 60-word string. It is composed on the fly and often changed. Each time it is changed, I want to pop up a msgbox with the RESULTS of the Query.

The Query is called "StoryRetrieve". When I execute it independently of my form, it produces exactly the string I want.

I've tried many things, but either the whole shebang bombs out in error or the msgbox pops up blank.

Thanks!

Edward

 

[PaulBricker]

Well you don't provide a lot of details but here's something to try. Put this in a Module.

Sub passmessage()
Dim mymessage as String
Dim rst as DAO.Recordset
Set rst = Currentdb.OpenRecordset("StoryRetrieve",dbopendynaset)
rst.MoveFirst
mymessage = rst![the field with the string]
MsgBox mymessage
set rst = Nothing
End Sub

Then call this Sub from the Click Event of your Button. This assumes that you only return one record when you open the query and the value you need is in that record.

HTH
Paul

 

[EdwardMartinIII]

Paul,

This crunches in the same place as before:
Dim rst As DAO.Recordset
"Compile error: User-defined type not defined"

If you want more details of the project, see thread 705-369757 ("query results to a text box&quot and thread 705-361651 ("Populating a Text Box with the results of a Query&quot.

...where I've been unable to solve the pesky thing...

thanks!

Edward

 

[PaulBricker]

I haven't read thru the other threads but that error sounds like a Library Reference problem. Open any module and make sure that the
Microsoft DAO 3.6 Object Library
is selected. Without it you can't create recordsets in DAO. In the mean time I'll try and find the other threads.

Paul

 

[EdwardMartinIII]

Well!

That got me much farther along, thanks!

Still not there yet, though.

Here's what I did:

Set rst = CurrentDb.OpenRecordset("StoryRetrieve", dbOpenDynaset)
rst.MoveFirst
MsgBox rst!Body
Me!Text_Story = rst!Body ' Doesn't work
Set rst = Nothing

The assignment line "Me!Text_Story = rst!Body" doesn't work yet, but I'm going to try different things there (advice would still be useful -- Text_Story is a Text Box). At least the Body text is something I can manipulate!

Cheers,

Edward

 

[PaulBricker]

If Me!Text_Story is a bound control then you might have problems doing this. If it's unbound it should work as written.

Paul

 

[EdwardMartinIII]

Thanks, Paul,

How peculiar! I was unable to find a property that indicated Bound or Unbound, but I deleted the text that appeared in the window at Design time and it was replaced with the word "Unbound". I tried it and it worked!

Now I get to figure out how to display the text as HTML. But first I wrestle with it for a while...

Cheers,

Edward
(a beginner, albeit eager)

 

[PaulBricker]

If the form is bound to a Table/Query, and the Control Source for the textbox is set to one of the Fields in that Table/Query, then it's a bound textbox. If it's not set to a particular Field then it's Unbound. There are some additional distinctions, but that's the general definition.

Paul