showing same field in 2 forms

  • Thread starter Thread starter RobG
  • Start date Start date
R

RobG

Hi all
I'm new to this so any help would be gratefull!!
i have a mileage subform with a calculated control in the
footer to give me the max mileage. i need that max
mileage in the main form in a field so i can then run a
query with. any sugestions would be apprieciated
Rob
Hope i explained that ok!!
 
Hi all
I'm new to this so any help would be gratefull!!
i have a mileage subform with a calculated control in the
footer to give me the max mileage. i need that max
mileage in the main form in a field so i can then run a
query with. any sugestions would be apprieciated
Rob
Hope i explained that ok!!
Click to expand...

Well, partly: "so I can run a query with"? How are you running the
query, and what difference does it make whether the control is on the
mainform or the subform?

You can put a Textbox on the mainform with its Control Source set to

=subformname.Form!textboxname

where subformname is the Name property *of the Subform control* on the
mainform (the box that contains the subform; its name may be the same
as the name of the form inside it but not necessarily), and
textboxname is the name of the calculated control in the footer.
 
Thanks for that John, that worked a treat!! the problem i
have now is how do i run a query??
the table i am using has a [next service mileage] field
in it but the current mileage (the one you explained how
to get) is only in the form of that table. i want to run
a query so that if the [current mileage] is less than 200
miles from the [next service mileage] then that record
will be displayed. i'm stuck because i can design the
query but only using the fields available from the table.
Hope that makes sense!!
Rob
 
Thanks for that John, that worked a treat!! the problem i
have now is how do i run a query??
the table i am using has a [next service mileage] field
in it but the current mileage (the one you explained how
to get) is only in the form of that table. i want to run
a query so that if the [current mileage] is less than 200
miles from the [next service mileage] then that record
will be displayed. i'm stuck because i can design the
query but only using the fields available from the table.
Hope that makes sense!!
Click to expand...

Not to me. All queries use fields from tables. You can use a Parameter
Query using

=[Forms]![NameOfAForm]![NameOfAControl]

as a criterion, so long as the form is open and the control has data
in it, but this doesn't seem appropriate.

What's the structure of your table? Just what are you trying to find?
Are [current milage] and [next service milage] fields in the table, or
calculated fields in the query, or calculated fields on the form, or
what?
 
-----Original Message-----
Thanks for that John, that worked a treat!! the problem i
have now is how do i run a query??
the table i am using has a [next service mileage] field
in it but the current mileage (the one you explained how
to get) is only in the form of that table. i want to run
a query so that if the [current mileage] is less than 200
miles from the [next service mileage] then that record
will be displayed. i'm stuck because i can design the
query but only using the fields available from the table.
Hope that makes sense!!
Click to expand...

Not to me. All queries use fields from tables. You can use a Parameter
Query using

=[Forms]![NameOfAForm]![NameOfAControl]

as a criterion, so long as the form is open and the control has data
in it, but this doesn't seem appropriate.

What's the structure of your table? Just what are you trying to find?
Are [current milage] and [next service milage] fields in the table, or
calculated fields in the query, or calculated fields on the form, or
what?


.
Thanks John
Click to expand...
heres the story!!!
i have two tables, 'vehicles' and 'mileages'.
vehicles has lots of fields including 'reg', 'purchase
date', 'service due date' and 'service due mileage'.

the mileage table has 'reg'(linked to the reg in the
other table), 'date' and 'speedo reading'. this is info
is in a aub form of the main vehicles form. in the
footer of the sub form i have a calculated box giving the
max[speedo reading] which makes that the current
mileage. as you explained i have now got a current
mileage in my main form along side the 'service due
mileage'.i need to stipulate in a query that if the
current mileage gets to within 200 miles of the 'service
due mileage' then the record will be displayed. i used
to do it using dates and that was easy but now i need to
bring this info up based on the mileage instead. is it
possible to have a field in a table that would display
the max speedo reading or can that only be obtained from
a control in a form???
Hope this makes sense!! i really do appreciate all your
help
Rob
 
i have two tables, 'vehicles' and 'mileages'.
vehicles has lots of fields including 'reg', 'purchase
date', 'service due date' and 'service due mileage'.

the mileage table has 'reg'(linked to the reg in the
other table), 'date' and 'speedo reading'. this is info
is in a aub form of the main vehicles form. in the
footer of the sub form i have a calculated box giving the
max[speedo reading] which makes that the current
mileage. as you explained i have now got a current
mileage in my main form along side the 'service due
mileage'.i need to stipulate in a query that if the
current mileage gets to within 200 miles of the 'service
due mileage' then the record will be displayed. i used
to do it using dates and that was easy but now i need to
bring this info up based on the mileage instead. is it
possible to have a field in a table that would display
the max speedo reading or can that only be obtained from
a control in a form???
Hope this makes sense!! i really do appreciate all your
help
Click to expand...

No, you DON'T need to use a Form to calculate a maximum! You can do it
directly in a Query, in a couple or three different ways!

A Totals query, grouping by reg and selecting Max([speedo reading]),
will get you the maximum. Try

SELECT [vehicles].[reg], Max([speedo reading]), [Service due milage]
FROM [vehicles] INNER JOIN [milage]
ON [vehicles].[reg] = [milage].[reg]
GROUP BY [vehicles].[reg], [vehicles].[Service due milage]
HAVING [vehicle due milage] - Max([speedo reading]) < 200;

The DMax() function also lets you look up a maximum value.
 
Thanks John
I think i know where your coming from but i cant get it
to produce the right results!! im using the following
statement in the criteria box of the '[NEXT SERVICE
MILAGE]. '<(SELECT Max([Speedo reading])+200 From
[mileages table])' and ive set the totals box of the
speedo reading to Max. the query has both the vehicles
and mileages table in it. its giving me all entries in
the table with a speedo reading!!! sorry to sound
confused but i really cant see where im going wrong!!
Rob
-----Original Message-----
i have two tables, 'vehicles' and 'mileages'.
vehicles has lots of fields including 'reg', 'purchase
date', 'service due date' and 'service due mileage'.

the mileage table has 'reg'(linked to the reg in the
other table), 'date' and 'speedo reading'. this is info
is in a aub form of the main vehicles form. in the
footer of the sub form i have a calculated box giving the
max[speedo reading] which makes that the current
mileage. as you explained i have now got a current
mileage in my main form along side the 'service due
mileage'.i need to stipulate in a query that if the
current mileage gets to within 200 miles of the 'service
due mileage' then the record will be displayed. i used
to do it using dates and that was easy but now i need to
bring this info up based on the mileage instead. is it
possible to have a field in a table that would display
the max speedo reading or can that only be obtained from
a control in a form???
Hope this makes sense!! i really do appreciate all your
help
Click to expand...

No, you DON'T need to use a Form to calculate a maximum! You can do it
directly in a Query, in a couple or three different ways!

A Totals query, grouping by reg and selecting Max ([speedo reading]),
will get you the maximum. Try

SELECT [vehicles].[reg], Max([speedo reading]), [Service due milage]
FROM [vehicles] INNER JOIN [milage]
ON [vehicles].[reg] = [milage].[reg]
GROUP BY [vehicles].[reg], [vehicles].[Service due milage]
HAVING [vehicle due milage] - Max([speedo reading]) < 200;

The DMax() function also lets you look up a maximum value.



.
Click to expand...
 
Thanks John
I think i know where your coming from but i cant get it
to produce the right results!! im using the following
statement in the criteria box of the '[NEXT SERVICE
MILAGE]. '<(SELECT Max([Speedo reading])+200 From
[mileages table])' and ive set the totals box of the
speedo reading to Max. the query has both the vehicles
and mileages table in it. its giving me all entries in
the table with a speedo reading!!!
Click to expand...

Please open the Query in SQL view and post the SQL text here; I'm not
sure exactly what you've got in the query, and this will clarify.
 
-----Original Message-----
Thanks John
I think i know where your coming from but i cant get it
to produce the right results!! im using the following
statement in the criteria box of the '[NEXT SERVICE
MILAGE]. '<(SELECT Max([Speedo reading])+200 From
[mileages table])' and ive set the totals box of the
speedo reading to Max. the query has both the vehicles
and mileages table in it. its giving me all entries in
the table with a speedo reading!!!
Click to expand...

Please open the Query in SQL view and post the SQL text here; I'm not
sure exactly what you've got in the query, and this will clarify.


.
Sorry John
Click to expand...
I think you have cracked it!!!
i was placing a date field in the query so it gave me a
max for that day so i was getting more than one reg
displayed. I think its working now so i'll play with
some data other the next few days and see what happens.
Hopefully that will be it so thanks very much for all
your help.
Rob
 
Back
Top