How to convert records in expression fields in Forms to a table?

[tan]

I inherit an old simple database written in Access 2000 with many forms and
queries that contain either "expression fields" or refer to a non existing
table names. I have few questions:
1. There is a form "ADD/EDIT customer list" where users enter customer
inmformation such as company name, address, phone. In the Design view, the
properties of these fields showed the ControlSource to itself! Where are
these records saved to because there was no table referred to?
2. How can I add more fields into an existing tables?

Thank you,
TAN

 

[Allen Browne]

Calculated fields do not exist in any table.

For details on how and when to store calculated results, see:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

You can add more fields to a table by opening it in design view. You will
then need to add those fields to the queries, forms, and reports that use
that table also.

 

[bcap]

1. For a bound control, the ControlSource property *always* refers to the
field. To discover which table the form is bound to, you need to look at
the RecordSource property of the form (with the form open in design view,
note that there is a square within a square at the extreme top left of the
window; double-click this to see the form's properties).

2. In the database window, click "Tables". Find the table to which you want
to add fields, right-click it, and choose "Design View".

 

[tan]

Thanks for the quick responses.
1. A non existing table name specified (called Customers") to the
recordsource of the Form. I can't seem to find it anywhere in the database
but the data is there! It couldn't be hidden, could it?

2. Adding more fields in the existing table is another problem. I can't
seem to be able to do so to the current database even though with an
identical copy to avoid interruption of user's operations. Could you assist
me in the process? Could I use split table method?

 

[Linq Adams via AccessMonster.com]

As to your statement

"In the Design view, the properties of these fields showed the ControlSource
to itself! Where are these records saved to because there was no table
referred to?"

I assume you mean that the control name is identical to the Control Source.
This is normal behavior if the Wizard was used to create the form, or if the
fields were dragged from the Field List to the form. Although identical, one
is actually the control's name on the form and the other is the name of the
field in the underlying table. It's really a good idea to change the name of
the control to something else, as there are times when having these names
identical can cause problem; Access can get confused as to which one you're
actually referring to.

To find out the table/query the control's data is bound to, you need to
select the form itself, goto Properties - Data and see what the Record Source
is.

Allen's advice on the rest of your post is, of course, spot on!

 

[bcap]

1. It's probably a query. Click on "Queries" in the database window, and
see if there's one named "Customers". If so, open it in design view to see
what table(s) the data is coming from. It *might* be a hidden table - on
the menu bar, click Tools-Options-View and tick the "Hidden Objects" check
box.

2. You will certainly have problems adding fields to a table if you can't
find the table! Or, if you are talking about another table, in what sense
can you not add fields to it? What happens when you try?

 

[tan]

1. I did make sure the hidden objects are shown. A query called "Customer
Query": in Design View, there is a "Customers" table without field names
inside the table box; whereas 2 field names are displayed under the table
view: Expr1: customers.customers.ID and customers.customers.company.

When I double click the Query, the Access gave an error: "The MS Jet DB
engine can't find input table or query "Customers". Make sure it exists and
name spelled correctly!"

I've noticed, when user used "Find" to find a customer name, the system
responded "can't find such name" but it is in the list! This problem
happened to another form and query.

2. I'd probably separate this question to another discussion! I was trying
to add fields to an existing table, not the above table.

Your questions and suggestions are extremely helpful because I'd have
opportunity to review the database again and again to understand more about
this database. Thank you very much for all of your insights.

 

[Bob Quintal]

Thanks for the quick responses.
1. A non existing table name specified (called Customers") to the
recordsource of the Form. I can't seem to find it anywhere in the
database but the data is there! It couldn't be hidden, could it?

It could. from the menu, check Tools-> Options-> View tab--> Show ->
Hidden Objects. You can then unselect the Hidden property of the
table.

Alternatively, Customers could be a select query, and queries can be
hidden as well.

2. Adding more fields in the existing table is another problem.
I can't seem to be able to do so to the current database even
though with an identical copy to avoid interruption of user's
operations. Could you assist me in the process? Could I use
split table method?

You have to find the table first.
There is a limit of 255 columns per table or query.

 

[bcap]

It certainly sounds like your Customers table is missing. You said earlier
that "the data is there". What exactly did you mean by that? What data
were you referring to, and how do you know it is "there"?

 

[tan]

The users are still able to add or modify records. If I open the form, it
does show the current record that was being modified and I can add new
record. But if I open the design (by exporting out this database to a
working one), there is no "Customers" table as it referred to! I am really
puzzled!

 

[tan]

Hi Bob,
I did make sure the objects shown but still couldn't find either table or
query that referred to "customers"!
TAN

 

[bcap]

Sorry, I'm really confused now. Are you saying that you export your objects
to a *different* database before looking at their design? Why? If the
"Customers" table is in database A, it's no great surprise that you don't
find it if you look for it in database B.

 

[tan]

I might use a wrong approach to open this database, didn't I? The database
was opened by users using a main switchboard and the Access menus were mostly
disabled except few record editing menus remain; I couldn't open it in design
mode. What I initially did was creating a new db, got the external db (which
is the working db being used by users), and pulled all objects and data into
this new db to work from there. Did I use a wrong method? I know that I
can't use link table feature because I will not be able to alter the existing
tables.

 

[bcap]

OK, if I were you I would start from scratch with the original database. If
you hold down the "shift" key when opening it, this should bypass the
start-up form and make the database window, and the object designs,
available to you. I hope that, if you do this, things will immediately
become a lot clearer for you!

 

[tan]

Thank you "bcap". I'll try that way with Access 2000. I did SHIFT key with
Access 2003 and 2007 but they don't seem to work!
TAN

 

[Bob Quintal]

Hi Bob,
I did make sure the objects shown but still couldn't find either
table or query that referred to "customers"!
TAN

The form or report that shows you the data may have code to create a
link to another .mdb or to sql server. You can check the form's
recordsource property, and the event code for on open, on load, and
maybe aa few others.

Q