Help: How Do I set a default FORM value based on SQL Query?

[Guest]

I want to set a default value in a form to update a table (Lets call it
TABLE_A). The value is the maximum of a single column in another table. (Lets
call it TABLE_B).

I know SQL very well but not VB. I set up the following query:
SELECT MAX(Col_1) AS (MaxCol1) FROM TABLE_A.

I then saved the query as SELMaxCol1
In the form design mode I went to "properties" for the field in question. I
went to expression builder and linked to my query. This produced the
following expression for default value:
=[SELMaxCol1]![MaxCol1]

When I use the form I get a #Name? error. I have changed all the names to
both match and deliberately not match for uniqueness. What is happening here?
Why is this simple process so difficult?

 

[Guest]

Wild guessing follows:

The default property requires a scalar value. When you put in your
expression, Access doesn't know how to run the query and return the single
value. A better approach would be to use a function like DMax(). Access likes
DMax. They went to High School together. Try this:

=DMax("[Col1]","Table_A")

If you want a Where clause, add it at the end like this:
=DMax("[Col1]","Table_A", "[Col2] = " & SomeValueOrReference)

HTH,
Barry

 

[Guest]

I tried using both MAX and DMAX. Both gave me the #name? error.

Based on your reply I tried using DMAX again and the exact same syntax (I do
not need a WHERE clause so used the simple expression you showed first.)
Unfortunately I still get the #name? error.

Thanks very much for trying anyway. It is much appreciated. If you have any
other suggestions please let me know.

Wild guessing follows:

The default property requires a scalar value. When you put in your
expression, Access doesn't know how to run the query and return the single
value. A better approach would be to use a function like DMax(). Access likes
DMax. They went to High School together. Try this:

=DMax("[Col1]","Table_A")

If you want a Where clause, add it at the end like this:
=DMax("[Col1]","Table_A", "[Col2] = " & SomeValueOrReference)

HTH,
Barry

I want to set a default value in a form to update a table (Lets call it
TABLE_A). The value is the maximum of a single column in another table. (Lets
call it TABLE_B).

I know SQL very well but not VB. I set up the following query:
SELECT MAX(Col_1) AS (MaxCol1) FROM TABLE_A.

I then saved the query as SELMaxCol1
In the form design mode I went to "properties" for the field in question. I
went to expression builder and linked to my query. This produced the
following expression for default value:
=[SELMaxCol1]![MaxCol1]

When I use the form I get a #Name? error. I have changed all the names to
both match and deliberately not match for uniqueness. What is happening here?
Why is this simple process so difficult?

 

[Guest]

Since the DMax is relatively stand-alone (no reference to controls on the
for), you could see if it works or not by pasting the expression into the
immediate window in the VBE:
?Dmax("blah...
This will tell you if it's the DMax function failing, or something else on
your form. The immediate window is a good place to fine-tune the expression
before putting it in the form.

Barry

I tried using both MAX and DMAX. Both gave me the #name? error.

Based on your reply I tried using DMAX again and the exact same syntax (I do
not need a WHERE clause so used the simple expression you showed first.)
Unfortunately I still get the #name? error.

Thanks very much for trying anyway. It is much appreciated. If you have any
other suggestions please let me know.

Wild guessing follows:

The default property requires a scalar value. When you put in your
expression, Access doesn't know how to run the query and return the single
value. A better approach would be to use a function like DMax(). Access likes
DMax. They went to High School together. Try this:

=DMax("[Col1]","Table_A")

If you want a Where clause, add it at the end like this:
=DMax("[Col1]","Table_A", "[Col2] = " & SomeValueOrReference)

HTH,
Barry

I want to set a default value in a form to update a table (Lets call it
TABLE_A). The value is the maximum of a single column in another table. (Lets
call it TABLE_B).

I know SQL very well but not VB. I set up the following query:
SELECT MAX(Col_1) AS (MaxCol1) FROM TABLE_A.

I then saved the query as SELMaxCol1
In the form design mode I went to "properties" for the field in question. I
went to expression builder and linked to my query. This produced the
following expression for default value:
=[SELMaxCol1]![MaxCol1]

When I use the form I get a #Name? error. I have changed all the names to
both match and deliberately not match for uniqueness. What is happening here?
Why is this simple process so difficult?