Displaying a query result using setvalue?

[jonwebb]

Hi,

I'm having a problem I'm sure is obvious but I can't work it out.

I want to be able to push a button on a form, which will run a query and put the result (1 variable) into a text box on the same form. I've linked the button to a 'setValue' statement which I thought would pick up the named query, get the result, and put it into the text box but no joy.

The two lines of code I'm using within the setValue statement (via a macro) are

Item: [Forms]![frm1]![queriedText]
Expression: [qry1]![LastOfprojectCode]

Where frm1 is the name of my form, queriedText is the name of the box I want to put it in, and qry1 is the name of the query, and LastOfprojectCode is the name of the variable returned by the query.

My error message is:

"The object doesn't contain the automation object 'qry1.'"

...And a bit more decriptive text I can post if anyone needs it - I don't think it's anything useful

Am I doing the wrong thing using setValue? Is it easier to do via VB (which I have limited experience with).

Cheers in advance,
Jon

 

[Andrew Fergus]

Hi Jon,

I don't think you can reference the query directly the way you are trying to do it (someone please correct me if I am wrong).

There is however a way around it which I have tested. Using the variable and form names per your example, delete the setvalue command and use the following lines in your macro instead :

OpenQuery (qry1, view datasheet, read only)
RunCommand (Copy)
SelectObject (Form, frm1, No)
GoToControl ( [queriedText] <- including the square brackets)
RunCommand (Paste)
Close (Query, qry1, No)
GoToControl ( [the name you assigned to the button] )
StopMacro

An inelegant solution I know, but it works. The reason we can get away with this in this instance is because "qry1" only has one variable (being "projectCode") and one line (i.e. the max or last function per the total query) - so there can be no confusion as to which object is selected and copied when the query is opened.

There are other possible solutions that may use another form, but this is a quick and easy way of doing it given you are only after the one number.

HTH, Andrew.