Displaying query results in a text box within a form

[donnie051280]

I have created a query (Part1) which displays the following fields (REF, JOBNUM and DESCRIPTION). I have created a button within the form (Trial1) which runs the query to display these results. A prompt table appears with the results when you click the button. BUT i want these results displayed within a text field.
How can i display the results?
thanks

 

[SarahG]

You could create another form with this query as it's recordsource. Then either open this form when the button is clicked, or insert this form as a sub-form in your existing form.
HTH

 

[donnie051280]

thanks for the help
i will try that
but is there anyway the results can be placed in a text field on the same form as the button.

i have tried entering into the control source of the text field = dlookup("exp","Part1").. but im confused as to what the expression would be.. as i have never used that before

 

[SarahG]

Hi ,
I assume you have a separate text box for each field outputted in the query. In the OnClick event for the subform control, insert the code:

me.txtField1 = me.subformcontrol.form!textbox1
me.txtField2 = me.subformcontrol.form!textbox2

etc

If you want all fields in one text box, then:

me.txtField1 = me.subformcontrol.form!textbox1 & "," & me.subformcontrol.form!textbox2

 

[donnie051280]

Hi again
im sorry to bother you, im quite new at access so im sorry if i sound so confused

Basically want i want is a form which displays the results of a query. When i enter the job number in a text field(textfield2) and exit the text box i want the results (job number, ref and description)in three text fields(textfield3,4,5). My query does present all the fields but when i put a macro to run the query on exit it just opens a datasheet. I tried opening a subform(subform1) which you suggested, it shows the fields of the results i want in a datasheet view inside the form. But it doesnt load the results when i exit the text field as it only takes me to a datasheet. The only time the sub form has worked was when i first exit the textfield and the datasheet arises, once i close the datasheet the results are dislpayed in the subform. But if i try this again with a new number the datasheet loads up but when i exit the datasheet the old job number is in the sub form not the new one. so basically i dont want the datasheet to appear, only want the results in the sub form or three different text fields if thats easier.
im sorry if this sounds confusing..

thanks again
don

 

[SarahG]

Hi Donnie,
sorry for the delay. Also, I didn't realise you were using macros. The 'Onclick' event that I referred to in my last post is the code equivalent of your macro.
It sounds like you have the subform working, the only thing that you need to do is change some code behind the button you press. In design view, display the properties for the command button, and in the 'OnClick' entry choose code instead of macro. It will bring up the code window, and display the bones of a procedure, something like this:

Private Sub cmdQuery_Click()

End Sub

You then insert the relevant code to read like this:

Private Sub cmdQuery_Click()
me.subcontrolname.requery
End Sub

where cmdQuery is the name of your command button, and subcontrolname is the name of the subform control in your form.
Hope this helps.

 

[donnie051280]

thanks for the help
i ended up using your first idea and created a sub form which shows the query results

But my new problem is

my form displays the query results, it shows a number of fields such as JobNo, Description and Customer.. all this data has come from different tables

i want to create a button which saves these fields into a new table..

i created a new table called stockcontrol.. with the following fields.. JobNumber, Description, Customer and Date/Time(Primary Key).. . So i want the fields that are displayed in the subform added to this new table.. There is currently no relationship between the new table and the query results..
would i have to create a new query which saves this data to a new table?? im not sure how to do it