Can this be done in code, or is a change in design required?

[Corey-g via AccessMonster.com]

Hi All,

I am having some difficulties in figuring out how to implement an order entry
system. I have been working on this for quite some time now, and have lots
of useful pieces built, but I'm still struggling with this part.

I am using A2K3 with an Oracle BE - and I have created code to link all of
the back-end tables when the app launches (Thanks to Doug Steele and Dev
Ashish for code samples). Originally I was trying to do this dns-less and
without linking, but I have since changed as this proved to be way to
difficult (as others had suggested it would be). So now that I have the
linked tables, I have set the control source of my order entry form to the
oracle table, and the fields to the appropriate fields.

Now the issue is that I had placed the items to be ordered in a subform
(actually, the users picks an items from the main form and click a button to
add it to the order), but how do I put the order id into the subform as well?
As the main form is bound, I can get the Order ID once the order is committed
- but that doesn't work in that I don't want to commit the order until the
items have been selected.

Table Design:
tblOrder_Info
Order_ID
User_ID
Requestor_ID
Requestor_Type_ID
Date_Ordered
Date_Submitted

tblOrder_Item
Order_Item_ID
Order_ID
Part_Number
Qty_Ordered
Qty_Received

I also have some other pieces that are similar (like a Notes and Status
tables), so I thought if I could get this the rest would work the same...

Any thoughts or suggestions would be greatly appreciated...

Thanks,

Corey

 

[Corey-g via AccessMonster.com]

Anyone??? Please...

I have since tried to change the way this works, but not having any luck. I
tried to move the form / subform onto a new form and tab control. So the
first page ot the TC has all of the Order Header info (Who / What / When /
Where), and the second page has the Order items (What). Because of the table
design, I need to know what order the items belong to (I think that this is
pretty standard). So how then do i determine what the order id is so that I
can populate the FK of the ORder ITem table?

The Order ID is populated by a before insert trigger in Oracle. I had
thought that when the user moved to the second page, the Order ID would be
available - but I haven't been able to get this working either.

Thanks again,

Corey