Can't get a query field to show in Form

[Guest]

I think I'm way over my head...
I have created a form with a sub form based on a table.
The values that I put in the table need to be averaged by field. ( sample#;
Type%; paper%; ,H2O%; and such) I have accomplished this by creating a
query grouping by the field Sample# and I get results.
I would like to place a text box with the result from each field (columns)
separately. When I try to place a unbound field on the form based on the
query, I get an error message or #name error.
I'm sure my methods are wrong but I just can't seem to get this thing going.
Any help would be much appreciated.

 

[Guest]

Hi Evert,

The trick is that you don't want an unbound field, you want a field that's
bound to the field in your query that has the value you want.

Damian.

 

[Guest]

Thanks Damian, this is what I've done
I created a text box and in the control source I've placed the following
through the Expresion builder.
=[qry percent]![Avg Of Type %]

But I still get an #Name? error

Is that what you meant by bound field box pointing to the field?

Thanks

 

[John W. Vinson]

Hi Evert,

The trick is that you don't want an unbound field, you want a field that's
bound to the field in your query that has the value you want.

Damian.

If you're trying to store derived data like this... DON'T.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

John W. Vinson [MVP]

 

[Guest]

Hi again,

Do you have the record source for your form set to [qry percent]? If so,
you simply need to set your field data source to be [Avg of Type %] (assuming
this is the name of a field in your query).

If you aren't using this query as the record source for your form, then you
might need to use dlookup to retrieve the value from the query.

HTH,

Damian.

Thanks Damian, this is what I've done
I created a text box and in the control source I've placed the following
through the Expresion builder.
=[qry percent]![Avg Of Type %]

But I still get an #Name? error

Is that what you meant by bound field box pointing to the field?

Thanks

Hi Evert,

The trick is that you don't want an unbound field, you want a field that's
bound to the field in your query that has the value you want.

Damian.

 

[Guest]

Thanks John, After reading your many Posts on the subject I'm following your
advise. The data can be "pulled" by queries if there's a need in the future.

 

[Guest]

My record source on the form is set to [tbl grading]
Is there a way to get the form to point to two sources at once so that I
don't have to deal with dlookup? (not too familiar with the command). Also
the subform source object is table.tbl sample.
Thanks again!

Hi again,

Do you have the record source for your form set to [qry percent]? If so,
you simply need to set your field data source to be [Avg of Type %] (assuming
this is the name of a field in your query).

If you aren't using this query as the record source for your form, then you
might need to use dlookup to retrieve the value from the query.

HTH,

Damian.

Thanks Damian, this is what I've done
I created a text box and in the control source I've placed the following
through the Expresion builder.
=[qry percent]![Avg Of Type %]

But I still get an #Name? error

Is that what you meant by bound field box pointing to the field?

Thanks

Hi Evert,

The trick is that you don't want an unbound field, you want a field that's
bound to the field in your query that has the value you want.

Damian.

:

I think I'm way over my head...
I have created a form with a sub form based on a table.
The values that I put in the table need to be averaged by field. ( sample#;
Type%; paper%; ,H2O%; and such) I have accomplished this by creating a
query grouping by the field Sample# and I get results.
I would like to place a text box with the result from each field (columns)
separately. When I try to place a unbound field on the form based on the
query, I get an error message or #name error.
I'm sure my methods are wrong but I just can't seem to get this thing going.
Any help would be much appreciated.

 

[Guest]

Hi Evert,

To get data from two or more tables, create a query.

Damian.

My record source on the form is set to [tbl grading]
Is there a way to get the form to point to two sources at once so that I
don't have to deal with dlookup? (not too familiar with the command). Also
the subform source object is table.tbl sample.
Thanks again!

Hi again,

Do you have the record source for your form set to [qry percent]? If so,
you simply need to set your field data source to be [Avg of Type %] (assuming
this is the name of a field in your query).

If you aren't using this query as the record source for your form, then you
might need to use dlookup to retrieve the value from the query.

HTH,

Damian.

Thanks Damian, this is what I've done
I created a text box and in the control source I've placed the following
through the Expresion builder.
=[qry percent]![Avg Of Type %]

But I still get an #Name? error

Is that what you meant by bound field box pointing to the field?

Thanks
:

Hi Evert,

The trick is that you don't want an unbound field, you want a field that's
bound to the field in your query that has the value you want.

Damian.

:

I think I'm way over my head...
I have created a form with a sub form based on a table.
The values that I put in the table need to be averaged by field. ( sample#;
Type%; paper%; ,H2O%; and such) I have accomplished this by creating a
query grouping by the field Sample# and I get results.
I would like to place a text box with the result from each field (columns)
separately. When I try to place a unbound field on the form based on the
query, I get an error message or #name error.
I'm sure my methods are wrong but I just can't seem to get this thing going.
Any help would be much appreciated.

 

[Guest]

Thanks Damian,
I got dlookup to work, now the problems is that I only get the top most
record.
=DLookUp("[qry percent]![Avg Of Type %]","[qry percent]","[Avg Of Type %]")
I haven't given it any criteria other than the field.

The query the data comes from is as follow:
SELECT DISTINCTROW [tbl sample].[PO#], [tbl grading].[Material Type],
Avg([tbl sample].[Type %]) AS [Avg Of Type %], Avg([tbl sample].[Paper %]) AS
[Avg Of Paper %], Avg([tbl sample].[Other materials]) AS [Avg Of Other
materials], Avg([tbl sample].[Other Plastics]) AS [Avg Of Other Plastics]
FROM [tbl grading] INNER JOIN [tbl sample] ON [tbl grading].[PO #] = [tbl
sample].[PO#]
GROUP BY [tbl sample].[PO#], [tbl grading].[Material Type];

I want to "tie" the result of Dlookup to the PO# so that it returns the
correct % for that PO. I haven't made any progress trying to do this.
Any suggestions?

Hi Evert,

To get data from two or more tables, create a query.

Damian.

My record source on the form is set to [tbl grading]
Is there a way to get the form to point to two sources at once so that I
don't have to deal with dlookup? (not too familiar with the command). Also
the subform source object is table.tbl sample.
Thanks again!

Hi again,

Do you have the record source for your form set to [qry percent]? If so,
you simply need to set your field data source to be [Avg of Type %] (assuming
this is the name of a field in your query).

If you aren't using this query as the record source for your form, then you
might need to use dlookup to retrieve the value from the query.

HTH,

Damian.

:

Thanks Damian, this is what I've done
I created a text box and in the control source I've placed the following
through the Expresion builder.
=[qry percent]![Avg Of Type %]

But I still get an #Name? error

Is that what you meant by bound field box pointing to the field?

Thanks
:

Hi Evert,

The trick is that you don't want an unbound field, you want a field that's
bound to the field in your query that has the value you want.

Damian.

:

I think I'm way over my head...
I have created a form with a sub form based on a table.
The values that I put in the table need to be averaged by field. ( sample#;
Type%; paper%; ,H2O%; and such) I have accomplished this by creating a
query grouping by the field Sample# and I get results.
I would like to place a text box with the result from each field (columns)
separately. When I try to place a unbound field on the form based on the
query, I get an error message or #name error.
I'm sure my methods are wrong but I just can't seem to get this thing going.
Any help would be much appreciated.