Forms in datasheet view and tables

Guest · Jun 10, 2004

Hi,
I wanted to have fields in a table that displayed different things (such as 'update') according to the date value of other fields in the database. I could see no way of doing this so I created a 'form' based on the table, and entered the formulas into that. It works perfectly but now I want the information to be in the table, but it isn't linked and I don't know how to change it back, or make the table update based on the form.
When I say form I don't mean a table in 'form' view for data entry, I mean on the menu with table, query,report,etc, there where it says 'forms'. It has a different sort of icon.

I want to run queries and reports on my form but Access will only report and query on tables and queries.

Basically when I click 'datasheet view' on my form, that is what I want my table to be. Or I want to keep it as a form but be able to auery it. I hope this makes sense!!!!!
Thank you!

Wayne Morgan · Jun 10, 2004

The easy answer is DON'T. Tables are just for holding data, as you stated,
you did the calculation in a form. Except for a few special cases, don't
attempt to store calculated data in a table, just calculate it when you need
it. This can be done in a query, form, or report using calculated fields or
controls. You have seen how to do this on a form, a report will be similar.
To create a calculated field in a query, open the query in design mode. In
the design grid, type in a name for the calculated field in the Field box
instead of choosing a field from the drop down. Follow this with the
calculation. The result of this calculation can even be used to "filter" the
query by adding criteria to this calculated field.

Example:
MyCalculatedField: IIf([Table1].[Field1]=1, "One", "Not One")

This will return the text One or Not One as the value for the field named
MyCalculatedField when you open the query. If you add criteria

"One"

It will only return records where the value of the calculated field is One.

In SQL view, this query would look something like:
SELECT IIf([Table1].[Field1]=1,"One","Not One") AS MyCalculatedField
FROM Table1
WHERE IIf([Table1].[Field1]=1,"One","Not One")="One";

--
Wayne Morgan
Microsoft Access MVP

Ian AFFS said:
Hi,
I wanted to have fields in a table that displayed different things (such

as 'update') according to the date value of other fields in the database. I
could see no way of doing this so I created a 'form' based on the table, and
entered the formulas into that. It works perfectly but now I want the
information to be in the table, but it isn't linked and I don't know how to
change it back, or make the table update based on the form.

When I say form I don't mean a table in 'form' view for data entry, I mean

on the menu with table, query,report,etc, there where it says 'forms'. It
has a different sort of icon.

I want to run queries and reports on my form but Access will only report

and query on tables and queries.

Basically when I click 'datasheet view' on my form, that is what I want my

table to be. Or I want to keep it as a form but be able to auery it. I hope
this makes sense!!!!!

Allen Browne · Jun 10, 2004

Ian, the best idea will be to move the calcuations out of the ControlSource
of the text boxes on your form, and put them into the fields of a query.
Access will then generate them as needed, and the calculations can never be
wrong (as they could be if you tried to store them in a table).

For more information, see:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ian AFFS said:
I wanted to have fields in a table that displayed different things (such