Adding Tables To Forms

[Kerri]

I'm having an issue adding tables to forms (or rather adding information
sources). I know the help says to go to builder and add tables, but my
builder only includes expressions, macro, and code. Should I be using one of
those to add tables to my form or is there something missing?

 

[Ben]

Hi Kerri,

I think you've misunderstood how access works...

Rather than thinking in terms of "adding" tables to forms, Access working
with "bindings".

Every form has a property called "RecordSource" which tells Access where the
data the form displays comes from. This may be the name of a table or query
stored in your database, or an SQL statement which selects data from the
tables and queries.

Once you have "bound" a data source to the form, you can then "bind"
controls to fields within the datasource. For example you can create a
textbox and set the "ControlSource" property to any field within the data
source. Access will help you with drop down lists of available fields.

Have a play with this stuff, then try out some Access tutorials - there are
millions of free ones on the web
http://www.google.com/search?hl=en&lr=&q=microsoft+access+tutorials...

Good luck,

Ben.

 

[Kerri]

Yes, I understand that. When I created the form the source was several
tables, but the field list only shows one even though all the table
categories appeared o n the form. I'm not understand why there is only one
table in my field list. It sems as if the form contains all of the fields of
the tables but not their references. For example, if my form contains address
and organization interest: If I change the source to the address table then
the organization is errored, but if I change the source for organizations via
the properities field then the address table is errored. Yet when I created
the form via the wizard I included both of those tables as the source.

 

[Ben]

Hi Kerri,

Okay - with you. The only way you can select multiple tables in the Form
Wizard is if a relationship between them is defined in the Relationships
layout on the tools menu - otherwise it would have given you an error (or
maybe not in earlier versions of access??) I take it this didn't happen,
which means something wierd is happening. I suspect that Access has
included all the fields you've requested on the form but could not work out
how to join the tables, so the recordsource of your form (which should be
SQL and include a JOIN keyword) is instead a simple table. You can change
this table to each of the two tables you specified, and will get half the
fields working.

The upshot is you're not getting the behaviour you want - it didn't work -
the relationship is invalid and the resultant form datasource is not a valid
JOIN between the two tables.

I would recommend starting again. This time, define a query yourself,
joining data from the two tables. Once you are satisfied with the dataset
this query produces, redesign your form (using the Form Wizard if you must!
Yuk!) but this time basing the form on the query, rather than selecting
fields from multiple tables. This should keep things simple and (hopefully)
working.

Let me know how it goes!

All the best,

Ben.


You cannot bind a form to two tables without using SQL.