Updatable Table Default Values

P

Paul Simon

I'm designing a database which uses an Update Query to add records to a
Table, and need to easily update the Default Values for three fields in
the table at the start of each session (these fields are not ones that are
added by the Update Query).

What's the best way to do this ? I presume I would need to use some sort
of form!

I've done a Google search and thought I'd found the answer, which involved
using a Form with three Unbound Text boxes and a Macro which was supposed
to take the input from the Form and make it the Default Values for
Controls in another Form which included the fields from the table.

Unfotunately, this did'nt work.
 
W

Wayne Morgan

Are you using an Update query or an Append query?

One way to do this would be a calculated field in the Append Query.

Examples:
INSERT INTO Table1 ( Field2, Field4 )
SELECT "DefaultValue" AS Expr1, Table2.TestPercent
FROM Table2;

INSERT INTO Table1 ( Field2, Field4 )
SELECT Date() AS Expr1, Table2.TestPercent
FROM Table2;
 
P

Paul Simon

Wayne said:
Are you using an Update query or an Append query?
Click to expand...
One way to do this would be a calculated field in the Append Query.
Click to expand...
Examples:
INSERT INTO Table1 ( Field2, Field4 )
SELECT "DefaultValue" AS Expr1, Table2.TestPercent
FROM Table2;
Click to expand...
INSERT INTO Table1 ( Field2, Field4 )
SELECT Date() AS Expr1, Table2.TestPercent
FROM Table2;
Click to expand...

Wayne,

You've lost me there(but thanks for trying); I must'nt be up to your level
of skill.

I'm not using any query to update the Default Values in the table. I could
just open the Table in Table Design at the start of each session and
change the values by clicking in the Default Value line and re-entering
them, but I thought there might be a way of making this process slightly
easier by using a form.

Anybody got any ideas!
 
W

Wayne Morgan

You had stated that you were adding items to a table using an Update Query.
An Update Query would change data that is already there, an Append Query
would add items. You could set a "calculated field" to a constant value in
the query, which would set that field in the table to the constant value as
you add records. You could rewrite the SQL of the query to change these
values using a form and textboxes on the form to enter what you want the
values to be then run the query. The same thing could be done for an Update
Query. This would give you a constant value for that field during that
session of adding records. The result would therefore be the same as if you
didn't specify that field in the query, but instead allowed the default
value in the table to enter that value for you.
 
P

Paul Simon

Wayne said:
You had stated that you were adding items to a table using an Update Query.
Click to expand...

Thanks Wayne,
Yes, I'm using the Update Query to change blank fields to whatever
Parameter value I input (does that make sense now)?
An Update Query would change data that is already there, an Append Query
would add items. You could set a "calculated field" to a constant value in
the query, which would set that field in the table to the constant value as
you add records. You could rewrite the SQL of the query to change these
values using a form and textboxes on the form to enter what you want the
values to be then run the query. The same thing could be done for an Update
Query. This would give you a constant value for that field during that
session of adding records. The result would therefore be the same as if you
didn't specify that field in the query, but instead allowed the default
value in the table to enter that value for you.
Click to expand...

I'll have a think about what you've said and try it. Sounds good though.

Thanks again. I'll let you know how it goes.
 
Top