MS Access for a dummy

Hello,

After mastering Oracle and MySQL I decided to get back to the basics of
proprietary SQL databasing for my job and teach myself MS Access.

It has gone sluggish to the point where I have one question. In having
multiple tables talk to each other, via the "attractive mask" of a
form, does one table have to be populated with data for the form
records to work? I am running into problems with empty tables that are
tied to one form and the error message I receive when I try to save the
record relates to a table error.

I trust I've explained my issue clearly without the aid of having my
database with me.

Please advise.

Cheers,

Roger from Oz

re: MS Access for a dummy

It depends on the type of relationships your tables have to each other
for that error. For example you cannot save a table on the many side
of a one to many relationship if there is no corresponding record in
the one side of the relationship. If the table is dependent on another
via the relationship you would have trouble entering the information
without the corresponding record.

HTH
Mink

re: MS Access for a dummy

roger_is_in@yahoo.com wrote:
[color=blue]
> It has gone sluggish to the point where I have one question. In having
> multiple tables talk to each other, via the "attractive mask" of a
> form, does one table have to be populated with data for the form
> records to work? I am running into problems with empty tables that are
> tied to one form and the error message I receive when I try to save the
> record relates to a table error.[/color]

I'm really not sure what you're talking about. Are you referring to he
Access equivalent of outer joins: Where Tbl1_PK = Tbl2_FK(+) ? If so,
look up left and right joins in Access/jet. The query design screen is
a wonderful tool for figuring this stuff out and I wish there was an
Oracle equivalent somewhere.

If a bound form (a form which is "bound" to an SQL statement or saved
SQL statement, ie, a view, or "query" in Access) with bound controls
(text boxes, combo boxes, etc that are directly tied to or "bound" to
columns returned by the SQL) is tied to a recordsource (the SQL
statement I mentioned) that returns no records, ANY CONTROLS IN THE
DETAIL SECTION of the form will not display.

That's because the detail section itself is "bound" to the results of
the SQL.

Does this make any sense to you?
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto

re: MS Access for a dummy

Of course, when I log into this group I don't have my access dbase
open.

Let me try the issue in another way:

Say, for example, I have two tables. One named "Employees" the other
named "Location."
In the Employees table I just have columns named FirstName, LastName,
Address1, Address2, City, State, Zip, Title.
In the Location table I have columns named Lastname, Region, State,
Country.

And for the sake of Argument I have all of the Employees columns in a
form combined with Region and Country from the Location table. Without
Access having to create another sub-table mirroring on the the original
tables, is it possible to have the form be the source input vehicle to
add records and populate both tables or does one table have to already
contain records to match the form's records?

Perhaps this evening I'll clarify the exact error window I receive when
trying this. Otherwise, what good is a form/database if one table has
to always contain data? to me this is greatly redundant. Or, did I
miss the concept and have to have the "attractive mask" form be the
source (and sole) table populator?

re: MS Access for a dummy

There are a number of things that will make a query un-updateable. One
common one is that it does not contain the primary key of the table being
updated. I don't know which columns are the primary keys of the tables you
describe, nor how the tables are related (one-to-many, one-to-one, or no
relation at all -- if the last, then you can't update both from a bound
form).

Forms in Access will show no Controls for the Data if there are no records
AND if the form does not allow the user to add new records.

You also did not explain the Record Source for the Form or, indeed, whether
the Form is bound or unbound. If the latter, you can do anything you can
accomplish in code, but unbound forms for handling data are going to be, in
the long run, a _lot_ more work than bound forms.

Larry Linson
Microsoft Access MVP



<roger_is_in@yahoo.com> wrote in message
news:1103224742.735772.322250@f14g2000cwb.googlegr oups.com...[color=blue]
> Of course, when I log into this group I don't have my access dbase
> open.
>
> Let me try the issue in another way:
>
> Say, for example, I have two tables. One named "Employees" the other
> named "Location."
> In the Employees table I just have columns named FirstName, LastName,
> Address1, Address2, City, State, Zip, Title.
> In the Location table I have columns named Lastname, Region, State,
> Country.
>
> And for the sake of Argument I have all of the Employees columns in a
> form combined with Region and Country from the Location table. Without
> Access having to create another sub-table mirroring on the the original
> tables, is it possible to have the form be the source input vehicle to
> add records and populate both tables or does one table have to already
> contain records to match the form's records?
>
> Perhaps this evening I'll clarify the exact error window I receive when
> trying this. Otherwise, what good is a form/database if one table has
> to always contain data? to me this is greatly redundant. Or, did I
> miss the concept and have to have the "attractive mask" form be the
> source (and sole) table populator?
>[/color]