Udating a table

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Phil - 20 Mar 2007 00:57 GMT

I have built a database who's analogy is similar to MS' Forms II How to use
Subforms. My problem is that in the two part form I can add a new Order
record, but when I had the Order Detail instead of it adding Chai Tea to the
Order Detail it adds another Chai Tea to the Products table. Where is the
control to change what table the product goes in? Ideally I'd like a drop
down box to select the item, but one step at a time.

Thanks.

Newbie

Reply to this Message

John W. Vinson - 20 Mar 2007 03:40 GMT

>I have built a database who's analogy is similar to MS' Forms II How to use
>Subforms. My problem is that in the two part form I can add a new Order
>record, but when I had the Order Detail instead of it adding Chai Tea to the
>Order Detail it adds another Chai Tea to the Products table. Where is the
>control to change what table the product goes in? Ideally I'd like a drop
>down box to select the item, but one step at a time.

Sounds like you've based your Subform on the Products table, rather than on
the OrderDetails table. What is the suborm's Recordsource? What's the Control
Source of the combo box?

John W. Vinson [MVP]

Reply to this Message

Phil - 20 Mar 2007 04:02 GMT

Here's what the Record Source is:

SELECT Operations.Operation, [Parts Operations].PartNumber
FROM Operations LEFT JOIN [Parts Operations] ON Operations.OperationsID =
[Parts Operations].OperationsID;

Operations is equivilant to "Products" and Parts Operations is equivilant to
"Order Detail"

Because i'm probably confusing you, the goal of this part of the DB is I
have lots of parts (table = Parts) and I have a few machining operations I
can do on each part (table = Operations). I have another table (Parts
Operations) which identifies which machining operations a particular part
requires. Everything appears to work in all other parts of the DB except I
want to be able to use a form that calls up parts so I can enter in the
machining they need. If I enter it directly in the Parts Operations table
all is OK. I hope I haven't confused you more.

Thanks.

> >I have built a database who's analogy is similar to MS' Forms II How to use
> >Subforms. My problem is that in the two part form I can add a new Order
[quoted text clipped - 8 lines]
>
> John W. Vinson [MVP]

Reply to this Message

John W. Vinson - 20 Mar 2007 05:10 GMT

>Here's what the Record Source is:
>
[quoted text clipped - 4 lines]
>Operations is equivilant to "Products" and Parts Operations is equivilant to
>"Order Detail"

Well... you're updating Operations with this query, not Parts Operations. Why
is the subform based on a two-table query? Normally the Form would be based on
the "One" side of the relationship, and the subform on the "Many" - neither
form would be based on a query joining the two.

John W. Vinson [MVP]

Reply to this Message