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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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 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.

Barry Gilbert said:
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

UKOH said:
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?
Click to expand...
Click to expand...
 
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

UKOH said:
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.

Barry Gilbert said:
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

UKOH said:
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?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top