qerry reference

  • Thread starter Thread starter David
  • Start date Start date
D

David

I am having problems getting the results of a query into a form, I have
tried the expression builder and through vba.
The form's data refers to 1 table,the query is in another, when you use the
wizard it will not let you use a query based on the same table that the form
gets its data from.
What I am trying to do is to get the max of a column that exist before
updating
the table is "p1t" the field on that table is "emr" the query is "pmax"
field of "maxofemr"
Can someone help show me what to do to get this on the form?
 
David said:
I am having problems getting the results of a query into a form, I
have tried the expression builder and through vba.
The form's data refers to 1 table,the query is in another, when you
use the wizard it will not let you use a query based on the same
table that the form gets its data from.
What I am trying to do is to get the max of a column that exist before
updating
the table is "p1t" the field on that table is "emr" the query is
"pmax" field of "maxofemr"
Can someone help show me what to do to get this on the form?
Click to expand...

I'm sure you can do this, either by using DLookup to get the value from
the query or by joining the query with the table in an appropriate way,
but I can't figure out from your message exactly what you're dealing
with. Could you describe the form, the table, and the query in more
detail, and what you want to see on the form?
 
I am having problems getting the results of a query into a form, I have
tried the expression builder and through vba.
The form's data refers to 1 table,the query is in another, when you use the
wizard it will not let you use a query based on the same table that the form
gets its data from.
What I am trying to do is to get the max of a column that exist before
updating
the table is "p1t" the field on that table is "emr" the query is "pmax"
field of "maxofemr"
Can someone help show me what to do to get this on the form?
Click to expand...

You can get rid of the query altogether, by using the DMax() function
as the Control Source of a textbox on the form. Open the form in
design view, and select the textbox where you want to show the maximum
value of emr; set its Control Source property to

=DMax("[emr]", "[plt]", <optional criteria>)

If you want the maximum value in the entire table leave off the third
argument; if you want the maximum value over some range of records,
put a criterion such as

"[xyz] = " & [txtXYZ]

to restrict the list of records considered to those which have the
same value of XYZ as the value shown on the form in textbox txtXYZ.
 
Dirk
The form;
has 7 textboxes bound to "p1t" table;
There are two for info "date" = date(), and pump=default of 1
This is the data entry part of the form with texboxes where you would enter
info into
"emr","cost","cbmr","cemr","cpg".
There is 1 textbox(text22) that subtracts "cbmr" from "cemr"
There is 1 textbox(text28) that adds "cost" to ("cpg" * "text22")
The last Textbox(text52) that I am trying to get to work, will get the last
record entered for "emr" and subtract it from "emr"(what they just entered)
to get a total difference from the previous entry and now(non-editable)

"pl1" table;
has the following fields;
emr,bmr,cost,date,cbmr,cemr,cpg,date,pump

"pl1query" query;
has 1 field and it is "maxofemr"
it fiqures the max of emr
thanks for responding
 
David said:
Dirk
The form;
has 7 textboxes bound to "p1t" table;
There are two for info "date" = date(), and pump=default of 1
This is the data entry part of the form with texboxes where you would
enter info into
"emr","cost","cbmr","cemr","cpg".
There is 1 textbox(text22) that subtracts "cbmr" from "cemr"
There is 1 textbox(text28) that adds "cost" to ("cpg" * "text22")
The last Textbox(text52) that I am trying to get to work, will get
the last record entered for "emr" and subtract it from "emr"(what
they just entered) to get a total difference from the previous entry
and now(non-editable)

"pl1" table;
has the following fields;
emr,bmr,cost,date,cbmr,cemr,cpg,date,pump

"pl1query" query;
has 1 field and it is "maxofemr"
it fiqures the max of emr
thanks for responding
Click to expand...

From the looks of things, John Vinson has already got you to a good
working solution, so I'm not going to pursue this. If that turns out
not to be the case, post back.
 
John
I put =DMax("[emr]", "[plt]") in the control sorce of that textbox and I
get #error
Any Ideals
 
Went back after I posted the previous about the error and I found the
culprit. In there you had [plt] instead of [p1t], its supposed to be a one
instead of L
Changed it and it worked
Thanks for the help!
 
Went back after I posted the previous about the error and I found the
culprit. In there you had [plt] instead of [p1t], its supposed to be a one
instead of L
Click to expand...

<g> Looks the same to me... maybe I should change newsreader fonts!

Glad it's working.
 
Back
Top