why doesn't my table update from input in my form

[Guest]

I added a default value expression in a form a created based soley off 1
table. I can only seem to run queries off the table, and the default value i
set in the form is not showing up in the query... I dont have the option to
do a relationship between the 2 so what should i do?

 

[Guest]

Setting a Default Value at the form level sets a value for the *control*,
i.e., the textbox, combo box, etc. used for the input. If the control is
Bound to an underlying field, i.e., its ControlSource property is the name of
a field in the underlying table, this value will be stored to the field.
Outside the form, however, no Default value is enforced.

You can set the Default value in Table design view to ensure that future
records get the value if no other is entered. For existing records where no
value exists, I suggest you do an update query that searches by the criteria
"Is Null" and updates it to your default.

Hope that helps.
Sprinks

 

[Joan Wild]

I added a default value expression in a form a created based soley
off 1 table.

So you set the default value in the form, but not the table.

I can only seem to run queries off the table,

I'm not sure what you mean; what else would you base a query on (except
another query or table)?

and the
default value i set in the form is not showing up in the query

Naturally, since you only set the default in the form. If you set the
default in the table that the query is based on, you'll see the default.
Note that a default only applies to new records, not existing ones.

.... I

dont have the option to do a relationship between the 2 so what
should i do?

Perhaps explain what you want to do. I'm guessing that you want to set a
default value on a field, and you want this default to show up in your query
and in your form. Is that correct? Do you really want a default for only
new records, or do you want to update existing records as well?

 

[Guest]

It seems logical, i mean i understand what your saying. But for some reason
when i use the same expression in the default value of the table that i used
in the form it doesn't recognize the fields, even though both colums exist in
the table and the form... i'm pretty novic-y so hope you don't mind answering
my questions I appreciate your quick responses

 

[Guest]

I have created a form that is based on 1 table, the table and the form have
the same field names. In the form, i set a default value in one field (field
1) that is a built expression which adds 90 days to another field( field 2)
in the form. In the table, i am unable to build the same expression because
1. i am not given an option to include another field in the build expression
wizard that comes up in the default value poertion of the table. and 2.
because i get an error message that says access doesn't recognize that field
as being real. I hope it makes more sense.

 

[Guest]

Are you saying that you're Default Value expression refers to other fields?
If so, while I didn't find any documentation saying that you couldn't do that
at the table level, I was unable to do it with a simple example.

Moreover, do you wish to allow the calculation to be overwritten by the
user? If not, you would be better off without the field entirely, and simply
calculate it as a calculated field in a query, and display the result in an
unbound textbox on your form.

If, however, you wish to assign a DefaultValue using an expression involving
one or more fields and allow the user to overwrite it, I suggest you assign
the value explicitly in the AfterUpdate event procedure of all fields
involved in the calculation:

Me![YourTextbox] = {Your Expression}

If that does not help, please post more detail, including the names of all
fields, the expression you wish to assign, and any code behind these fields.

Sprinks

 

[Joan Wild]

I have created a form that is based on 1 table, the table and the
form have the same field names. In the form, i set a default value in
one field (field 1) that is a built expression which adds 90 days to
another field( field 2) in the form. In the table, i am unable to
build the same expression because
1. i am not given an option to include another field in the build
expression wizard that comes up in the default value poertion of the
table. and 2. because i get an error message that says access doesn't
recognize that field as being real. I hope it makes more sense.

That is true, you can't use many expressions in a table default value.
However, since this is a calculated result, you shouldn't need to store it
at all in your table. You can calculate it any time you need it.

 

[Guest]

ok i think what i really need is to run a calculation in a query didn't
know you could do that... here's the situation

i have a database of clients for whom i find employment. It is necessary for
me to find out , once they are placed, when they have been on the job for 90
days. in the form i have "client placement date" as a field and "90 day" the
first field is obviously updatable, but not the 90 day field. Are you saying
i can run a calculated query inside my form? I would also like a report to
show client name ,contact info, and 90 day field.

p.s is that a glich in access, not being able to refer to another field in
the expression builder of a table? or is it just not posssible to do?

Are you saying that you're Default Value expression refers to other fields?
If so, while I didn't find any documentation saying that you couldn't do that
at the table level, I was unable to do it with a simple example.

Moreover, do you wish to allow the calculation to be overwritten by the
user? If not, you would be better off without the field entirely, and simply
calculate it as a calculated field in a query, and display the result in an
unbound textbox on your form.

If, however, you wish to assign a DefaultValue using an expression involving
one or more fields and allow the user to overwrite it, I suggest you assign
the value explicitly in the AfterUpdate event procedure of all fields
involved in the calculation:

Me![YourTextbox] = {Your Expression}

If that does not help, please post more detail, including the names of all
fields, the expression you wish to assign, and any code behind these fields.

Sprinks

It seems logical, i mean i understand what your saying. But for some reason
when i use the same expression in the default value of the table that i used
in the form it doesn't recognize the fields, even though both colums exist in
the table and the form... i'm pretty novic-y so hope you don't mind answering
my questions I appreciate your quick responses

 

[Guest]

Cyd,

It's not a glitch; in virtually all cases, it is undesirable to store
calculated values because 1) they waste disk space; 2) retrieving a stored
value is much slower than calculating it on the fly; and most importantly, 3)
you need VBA code to store it, since you can set a control's ControlSource to
a field OR to an expression, but not both. This then results in the
possibility of the data being incorrect, if the fields involved in the
calculation are changed outside the context of your form where the code
resides.

This is an issue related to database normalization, IMO the most important
concept to master if you intend to develop applications, otherwise, you'll be
spinning your wheels. See the references below for further information.

You don't "run a calculated query" inside your form, per se. A form is
based on a Recordset, which is either a table or a query. So, create a query
that includes all of your fields, and then enter a new field with the
calculated expression in the Field: row of the query. The syntax is
fieldname:expression, e.g.,

ExtendedPrice:[Qty]*[UnitPrice]

Save the query, and set your form's RecordSource property to the name of the
query. You can then place this calculated field on your form.

Getting Started:
http://www.mvps.org/access/tencommandments.htm

ACC: Database Normalization Basics
http://support.microsoft.com/?id=100139
http://support.microsoft.com/?id=209534
http://support.microsoft.com/?id=283878

Database Normalization Tips by Luke Chung
http://www.fmsinc.com/tpapers/genaccess/databasenorm.html

Support WebCast: Database Normalization Basics
http://support.microsoft.com/default.aspx?scid=/servicedesks/webcasts/wc060600/wcblurb060600.asp

Database Normalization:
http://burks.bton.ac.uk/burks/foldoc/35/28.htm

5 Rules of Database Normalization:
http://www.datamodel.org/NormalizationRules.html

"Understanding Relational Database Design" Document Available in Download
Center:
http://support.microsoft.com/?id=283698
http://support.microsoft.com/?id=164172

ACC2000: "Understanding Relational Database Design"
http://support.microsoft.com/?id=234208

Fundamentals of Relational Database Design:
http://support.microsoft.com/?id=129519

Database Deisgn Principles:
http://msdn.microsoft.com/library/en-us/dndbdes/html/ch04DDP.asp


Sprinks

ok i think what i really need is to run a calculation in a query didn't
know you could do that... here's the situation

i have a database of clients for whom i find employment. It is necessary for
me to find out , once they are placed, when they have been on the job for 90
days. in the form i have "client placement date" as a field and "90 day" the
first field is obviously updatable, but not the 90 day field. Are you saying
i can run a calculated query inside my form? I would also like a report to
show client name ,contact info, and 90 day field.

p.s is that a glich in access, not being able to refer to another field in
the expression builder of a table? or is it just not posssible to do?

Are you saying that you're Default Value expression refers to other fields?
If so, while I didn't find any documentation saying that you couldn't do that
at the table level, I was unable to do it with a simple example.

Moreover, do you wish to allow the calculation to be overwritten by the
user? If not, you would be better off without the field entirely, and simply
calculate it as a calculated field in a query, and display the result in an
unbound textbox on your form.

If, however, you wish to assign a DefaultValue using an expression involving
one or more fields and allow the user to overwrite it, I suggest you assign
the value explicitly in the AfterUpdate event procedure of all fields
involved in the calculation:

Me![YourTextbox] = {Your Expression}

If that does not help, please post more detail, including the names of all
fields, the expression you wish to assign, and any code behind these fields.

Sprinks

It seems logical, i mean i understand what your saying. But for some reason
when i use the same expression in the default value of the table that i used
in the form it doesn't recognize the fields, even though both colums exist in
the table and the form... i'm pretty novic-y so hope you don't mind answering
my questions I appreciate your quick responses

 

[Guest]

Sprinks,

I wish to assign a default value using an expression and allow the user to
overwrite it. The expression I want as default is a field from a query. I
saw the afterupdate event procedure you wrote but did not quite understand
what you mean by "Your expression".

Me![txtApproved] = {what the user entered?}

Please help

Are you saying that you're Default Value expression refers to other fields?
If so, while I didn't find any documentation saying that you couldn't do that
at the table level, I was unable to do it with a simple example.

Moreover, do you wish to allow the calculation to be overwritten by the
user? If not, you would be better off without the field entirely, and simply
calculate it as a calculated field in a query, and display the result in an
unbound textbox on your form.

If, however, you wish to assign a DefaultValue using an expression involving
one or more fields and allow the user to overwrite it, I suggest you assign
the value explicitly in the AfterUpdate event procedure of all fields
involved in the calculation:

Me![YourTextbox] = {Your Expression}

If that does not help, please post more detail, including the names of all
fields, the expression you wish to assign, and any code behind these fields.

Sprinks

It seems logical, i mean i understand what your saying. But for some reason
when i use the same expression in the default value of the table that i used
in the form it doesn't recognize the fields, even though both colums exist in
the table and the form... i'm pretty novic-y so hope you don't mind answering
my questions I appreciate your quick responses