Querry in form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ED007 - 30 Jun 2006 19:11 GMT

If I place a querry in a form, can I update entries in the querry as
displsyed the from and have the data tables update as well?

Reply to this Message

Jeff L - 30 Jun 2006 19:28 GMT

Yes you can, but this will not always work for every query. For
example, queries with outer joins in them are not updateable. If you
are able to update a value in a query, it will update the value in the
table as well.

Reply to this Message

ED007 - 30 Jun 2006 19:45 GMT

HMMM

Sorry for hte dumb quesiton but what is an outer join?

I have three tables LOT,SAMPLE,TESTVALUE

LOT
LOT - PRIMARY KEY
OTHER DETAILS

SAMPLE
SAMPLE # - PRIMARY KEY
LOT - FORIEGN KEY
SAMPLE CODE

TEST
TEST # - PRIMARY KEY
SAMPLE # -FORIEGN KEY
TEST VALUE

I have a form for entering the lot data that has a sub form for entring the
sample code and a sub form on that sub form for entering the test value.

I can have multiple test values for each sample code and multiple sample
codes for each lot.

The set up works well except the user must constantly enter data and use the
mouse to switch between sub sub forms.

I was hoping to write a querry that had LOT, SAMPLE CODE, TEST VALUE. I
want to put this into a sinlge sub for that shows only sampe code and test
value linked to the main form by LOT. The user could then enter data in the
table without the mouse.

Is this possible?

> Yes you can, but this will not always work for every query. For
> example, queries with outer joins in them are not updateable. If you
> are able to update a value in a query, it will update the value in the
> table as well.

Reply to this Message

Jeff L - 30 Jun 2006 20:17 GMT

An outer join is a type of join where you are including all values from
one table and only the values from a second table where the joined
fields are equal. This type of query is not updateable because the
second table may not have a corresponding record to the first table.

An inner join is where you are getting values from both tables, but
only when the joined values are equal. In most cases (notice I said
MOST cases), these types of queries are updateable.

As far as I can tell by your example, if you join your primary keys
with your Foreign Keys, your query should be updateable.

Reply to this Message

ED007 - 30 Jun 2006 20:55 GMT

Jeff L:

Thanks for the clarification. I tried it and I can get some of the fields
to uptdate but not all It depends on what I include in the original querry.
It just seems tha ther has got to be a better way than the sub form within a
sub form that I am using now. Is it possible to present an excell file, have
the user typein the data in a normal way and then have code on the form that
pulls it apart and dumps it into the correct tables?

Ed

> An outer join is a type of join where you are including all values from
> one table and only the values from a second table where the joined
[quoted text clipped - 7 lines]
> As far as I can tell by your example, if you join your primary keys
> with your Foreign Keys, your query should be updateable.

Reply to this Message

Jeff L - 30 Jun 2006 21:54 GMT

I don't know the answer to that. Sorry.

> Jeff L:
>
[quoted text clipped - 18 lines]
> > As far as I can tell by your example, if you join your primary keys
> > with your Foreign Keys, your query should be updateable.

Reply to this Message