Linking Results From a Query to a Control(Textbox) on a form?

[alexi]

Can this be done?

i have a form in which i enter new jobs. these job no's have corresponding tasks, and each of these can be ticked completed, invoiced etc. one of the fields though is an actual cost. now for me to get the actual cost i have to run another report i have(Job Actuals) to get the amount that task cost. Is it possible to get the query that runs my job actuals report, to spit out the result into a text box next to the corresponding task on my NewJobs Form? obviously i would need to replicate the report and rename it so i dont disturb the Job Actuals Report.

Any help would be greatly appreciated!

Thankyou

 

[llkhoutx]

You can't go directly from a report to a text box, how nice that would be.

On your requisite event, run your query with DAO code recordset and then store the field you want into the text box, eg,

dim db as dao.database
dim rs as dao.recordset
dim qdf as querydef
'Note that YourDuerySQLstring should return one record
set qdf=db.createquerydef("","YourDuerySQLstring")
set rs=qdf.openrecordset(dbopendynaset)
rs.movefirst
me!YourTextBox = rs.fields(i) 'where i=the column of your query holding the result you want
qdf.close
rs.close
db.close
set qdf=nothing
set rs=nothing
set db=nothing

 

[alexi]

so what your saying is the queryh would need to be run before openingg the report? or how would u automate that

 

[alexi]

help?

could someone help me with this? i dont understand how to do this. would it help uploading the database?

 

[llkhoutx]

You'd create a button or a form and then on the OnClick event for that button run the code that I previously posted.

The trick is the query result. It should generate one row and one or more fields. The fields are numbered 0 through i, with 0 being the first and i being the last, with n being the field number you want to store on the form.

rs.fields is the field who'se value is stored in the your textbox per my code.

 

[alexi]

insttead of a number of a record on a query? could it be the task number? which isnt the primary field?
so each time i run it, it gerts the approrate number for that task number?

 

[llkhoutx]

It doesn't matter what it is if it is in the result of the query. You said that that's what you wanted.

 

[alexi]

sorry i didnt explain myself very well
i need the criteria to be the task number if that makes sense?
on my form i have a task no, followed by other information including ActualCost, i need the actual cost to come off the query but only tat figure that is for the Task No?
do u understand what im saying? im not explainin my self very well

thanks for ur help btw

 

[llkhoutx]

Do you want to use the textbox as a criteria in your query? Or, do you want to store the query result in the textrbox?

 

[llkhoutx]

Do you want to use the textbox as a criteria in your query? Or, do you want to store the query result in the textrbox?

 

[alexi]

i want to use the textbox called TaskNo on my form as criteria in getting the information from the Query to store it in the textbox ActualCost?

 

[llkhoutx]

Post the SQL for your query and I'll are the criteria.

 

[alexi]

give me a sec and i'll post the database. i havnt written it in sql
give it 5

 

[llkhoutx]

Your query, returning one record, should be

If TaskNo is an integer or long or byte:

"SELECT ActualCost FROM YourTabnleName WHERE TaskNo =" & me!TaskNo

or

If TaskNo is a string:

"SELECT ActualCost FORM YourTabnleName WHERE TaskNo =" & CHR(34) & me!TaskNo & CHR(34)

 

[alexi]

the file is too big. let me get rid of some data

 

[llkhoutx]

Zip the file or it will always be too big.

 

[alexi]

give me a minute again sorry. im having trouble gettin it smaller

 

[alexi]

sorry it is too big i cant get it below a 1 meg zip file

 

[llkhoutx]

Try the sql statement I gave you with dao code previously posted.

 

[alexi]

ok. i will try it a little later and let you know.
thankyou for your time mate. much appreciated