Command Button - Query - Textbox

Hi anyone

can it be done to click a Command Button - Run a Query - and place one fields info in a Textbox on the Command Button form. The forms record source has nothing to do with the query. I can get the button to run the query ok, but I can't get the field information into the textbox. That is without doing a copy and paste with CTRL + C and CTRL + V. This works but it is pretty primitive looking. I would like it if the query wasn't even visible.

regards
admoller
admollerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

simonbennettCommented:
Ok,

Create a text box (unbound) called txtResult. Create a button called cmdQuery. Call your query qryResult. Place this code on the onclick event of cmdQuery. I have assumed the following:
1)Your form is called frmForm
2)The field in the query you want to display is called Result

    Dim rstRes As Recordset
    Dim varResult As Variant
   
    Set rstRes = CurrentDb.OpenRecordset("qryQuery", dbOpenSnapshot)
    If rstRes.RecordCount > 0 Then
        varResult = rstRes("Result")
    Else
        varResult = "No Data Found"
    End If

    Forms("frmForm")("txtResult") = varResult
   
Let me know if you need more info.
HTH
Simon
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brewdogCommented:
You could also hard-code the ControlSource of your text box to:

=DLookup("YourField", "YourQuery")

I'm not sure if you need some criteria in there, but that is the gist . . .

brewdog
0
admollerAuthor Commented:
Thanks simonbennett and brewdog

I think simonbennetts method is the closer solution. However it crash's on the line Set rstRes = ... with an error message that there are too few parameters, Expected 1. Any ideas what that is about - when I remove the error capture in the procedure the debug error message is the same with run-time error 3061 added.

regards
admoller
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

brewdogCommented:
it sounds like your query expects information to be passed in from the user, or your form, or somewhere. Is that right?
0
admollerAuthor Commented:
When the command button clicks I had it docmd run a query and it would then do a whole lot of adding and dividing to get a percentage for me. That worked fine, (the query) it will produce results, eg the query when run returns customerId, lastName and 15.00 and display this in datasheet view. However this isn't really what I would like - as I then have to cut and paste (15.00) into the textbox. The information for the query comes from forms/tables linked with customerId.

regards
admoller
0
simonbennettCommented:
Hello again

The error is because this line

Set rstRes = CurrentDb.OpenRecordset("qryQuery", dbOpenSnapshot)

should contain the name of your query e.g.

Set rstRes = CurrentDb.OpenRecordset("<insert your query name here>", dbOpenSnapshot)

Now what is the name of the field that contains 15:00????? This should be placed in this line

varResult = rstRes("<insert field name here>")

Let me know if you need more help.

Thanks

Simon
0
JimMorganCommented:
admoller:  I do this quite often when I need the output of a query to be used somewhere else.

Make the query a MakeTable query.  Have it create a temporary table, tblTempWhatEver.  Behind the command button enter this code:

   CurrentDB.Execute "qryToRun"
   FieldTarget = DLookup("Field", "tblTempWhatEver")

Of course you can flesh this out by making the table once, then first deleting the contents of the table and then appending the query output to the table.  By using Execute, any warning messages will not be shown.  Further more you could fine tune the lookup if more than one record would be produced in the query.

The nice thing is if other field values are going to used later, the data is already in the table and you don't have to run the query again.

Jim
0
admollerAuthor Commented:
Thanks Simon your code put me on the right track, I just altered it to accommadate a QueryDef and Dynaset and Bob's your uncle.

I'm going to back everything up now!

Happy Y2K
admoller
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.