Displaying max value from a table on a form, based on drop down option on the same form

[missb]

I have created a tabbed controlled form and on one of the tabs, a value is selected from a drop down list, then basic information relating to that value is automatically displayed. then new information is added and all the new data is collected in a table, this works beautifully, however the issue i'm having is this. I need to run a query based on that table. Basically look at the value selected in the drop down(unit#), compare to tables'(unit#) if they match then display max value of column 3. Seems simple enough right!, well for the life of me I cannot get it to work keep getting error #name reference errors......... please help.



I have included a snapshot like I said basic stuff, just stuck

[ranman256]

Are you SURE the combo name is UNIT#?
The query that pulls the MAX value, must reference the form object name NOT the field name. (but they could be the same)
so the criteria for [unit#]
...where [unit#] = forms!frmFormName![unit#]

usu I name my combo boxs cboState, or cboUnit,

[missb]

the formula looks like this,
=IIf([Data_Vehicle(s) Query]![UN_Unit]=[Form_Data_Unit],[Data_Vehicle(s) Query]![Max Of Data_Km's],"") i built it in the expression builder so would assume id have the correct names?

Ive confirm the query is pulling the correct information, see below

[June7]

I never use the expression builder.

An = sign would be used in textbox ControlSource, not in query if the expression is to construct a field.

Exactly where is that expression used? In expression in textbox cannot refer directly to a table or query object.

I presume that query is NOT the form RecordSource.

[missb]

The query is not the record source, for the form, no, this is:

SELECT Un_units.UN_Unit, Un_units.Un_Info, Un_units.UN_Vin, Un_units.UN_Plate, Un_units.UN_Fuel, Un_units.UN_Color, Un_units.UN_Ownership, [Data_Vehicle(s)].Data_Unit, [Data_Vehicle(s)].Data_Vendor, [Data_Vehicle(s)].[Data_Invoice#], [Data_Vehicle(s)].Data_Cost, [Data_Vehicle(s)].Data_Warrenty, [Data_Vehicle(s)].Data_Comments, [Data_Vehicle(s)].[Data_Km's], [Data_Vehicle(s)].Data_Date, [Data_Vehicle(s)].Data_OilChange, [Data_Vehicle(s)].[Data_Oil Filter], [Data_Vehicle(s)].Data_Lubricants, [Data_Vehicle(s)].[Data_Air Filter], [Data_Vehicle(s)].Data_Wipers, [Data_Vehicle(s)].Data_WiperFluid, [Data_Vehicle(s)].Data_Lights, [Data_Vehicle(s)].Data_Tires, [Data_Vehicle(s)].Data_LugNuts, [Data_Vehicle(s)].Data_Steering, [Data_Vehicle(s)].Data_SteeringFluid, [Data_Vehicle(s)].Data_Brakes, [Data_Vehicle(s)].Data_Suspension, [Data_Vehicle(s)].Data_DriveShaft, [Data_Vehicle(s)].Data_TireChange, [Data_Vehicle(s)].[Data_Diff/TC Oil], [Data_Vehicle(s)].Data_FuelFilter, [Data_Vehicle(s)].Data_TrannyFlush FROM Un_units INNER JOIN [Data_Vehicle(s)] ON Un_units.ID = [Data_Vehicle(s)].Data_Unit;.


the expression is being used in a text box ControlSource as displayed below

[June7]

As noted, that expression cannot refer directly to table or query object. I really don't understand what you are trying to do. Your statement "Basically look at the value selected in the drop down(unit#), compare to tables'(unit#) if they match then display max value of column 3." does not make sense to me.

[ssanfu]

Your naming convention is very poor. Object names should only be letters, numbers and maybe the underscore. NO spaces, punctuation or special characters
Examples:
Table name "Data_Vehicle(s)" has parenthesis
Field name "[Data_Invoice#]" has special character
Field name "[Data_Km's]" has apostrophe
Field name "[Data_Diff/TC Oil]" has space and slash

=IIf([Data_Vehicle(s) Query]![UN_Unit]=[Form_Data_Unit],[Data_Vehicle(s) Query]![Max Of Data_Km's],"")

=IF( condition, Value if TRUE, Value if FALSE)

Condition: [Data_Vehicle(s) Query]![UN_Unit]=[Form_Data_Unit], <<-What is [Form_Data_Unit]? Do you have a control named "[Form_Data_Unit]"?
Value if TRUE:[Data_Vehicle(s) Query]![Max Of Data_Km's] <<- as noted, can't refer to table/query data this way.
Value if FALSE: ""

In any case, formula probably won't return what you want.
As much as I try not to use aggregate functions, in this case you probably should use the DMax() function.

The picture in post #3 concerns me. It shows that the PK field "Un_units.ID" (another poor field name) is related to the field "[Data_Vehicle(s)].Data_Unit".
Is "Un_units.ID" an Autonumber type field?
(I would have named the PK field "UnitID_PK" and the FK field "UnitID_FK". Easier to know what is the PK field and what are the FK fields)


-----------------
Not trying to burn you, but it is easier to create forms/queries/reports if there is a normalized table structure..

Just my $0.02.........good luck with your project.

[missb]

The whole point of this was to get input on how i can get my form to display the information I need.

I have a table i run a form off, within that form there is a drop down option (vehicle units)
I need a formula that will look at the option selected (from the drop down, in the form) say unit A is selected, then go into a table find all the (unit A's) in column 1, and find the max value of column 2 (Km's), and show the results on the form somewhere.
I am trying to find the last reported km's to know when the next oil change is.

If i cant refer directly to a table or query? what is the best way to do this. Ive never used a DMax() function before........

PK ID's are auto named based of the table name, as well I have always labeled (Source_Column_Formula) to me that's pretty clear and very easy to find or reference to, the key is being consistent with labeling. max data, was to show the formula i tried that didn't work, it isn't sticking around.
Excuse the frustration, but it seems instead of asking questions to help me, there is more picking at how i labeled things and the clarity of my question, instead of suggestions.

[June7]

I would be happy to suggest a solution if I fully understood the requirement.

If you want to pull data from a table or query that is not included in the form's RecordSource, options are:

1. domain aggregate function (DLookup, DSum, DMax, DMin, etc) which can be used in form's RecordSource query or in a textbox ControlSource expression

2. VBA code which could use recordset or domain aggregate

Search Access Help or web for lots of guidance on how to use these functions.

[missb]

and i am not sure how I can clarify anymore then i have..... i guess ill try access help

[June7]

There are also SQL aggregate functions (Sum, Max, Min, etc) which you seem to have used in a query to calculate a field [Max of Data_Km's]. If you want to pull data from that query onto form, that will require one of the methods I described.

Where I get lost is the comparison you want to do "look at the value selected in the drop down(unit#), compare to tables'(unit#)". If that table is not the form's RecordSource, how do you expect to have the table value available for comparison? And which record of the table? Maybe in a textbox on form:

=IIf([comboboxname]=[fieldname], DMax("[Max Of Data_Km's]", "queryname", "some criteria here"), Null)

As suggested, do some research on use of domain aggregates for details on the function arguments.