Am I insane?

[Ason1976]

2 things--> I have 55 fields(i am considering adding more) in a query based on two tables. Could this be a problem?

Secondly, my database has Tables-
Customers,
CUSTID(autonumber)

Orders,
ORDERID(autonumber)
CUSTID

Order Details,
ORDERDETAILID(autonumber)
ORDERID

Product Details,
PRODUCTDETAILID(autonumber)
ORDERDETAILID

My Customers(Single form) is my main form, Orders(single form) is a subform and OrderDetails(continous form) is a subform within orders. Product Details pops up with a command button(on Order Details).

Now let me just specify that I have read throught the majority of the threads in this awesome site but i cannot get anything to work for me.

User enters Customers form and starts an Order. When they click on the Order Details, how can I make the OrderID enter in automatically and how does this work for a pop up-
forms![Order Details]!{OrderDetailID]--->fills in forms![ProductDetails]!{OrderDetailID].

Will this work using continous forms?

Any help would be GREATLY appreciated.

Thank you in advance

ason1976

 

[Trendsetter]

Lot 1)
2 tables averaging 27 fields each is a lot but not impossible - personally I find that most tables can be kept under 15 fields and some only have 2 or 3... but it is not something that I am pedantic about and don't wish to get into long debate about!

Lot 2)
I feel that your logic is slightly awry!
Customer - his own form with sub for Contacts .. maybe
Orders - own form - Order information - reference CustID and CustName - subform for Order Items which would be fed from the Products tables via combos or listboxes.

 

[Ason1976]

Actually, Trendsetter, alot of my query fields are expressions.

Lot 2
I am designing this DB as a POS system, so the customers needs it setup up as too see the customers and the orders simultaneously.

But that doesnt solve my conundrum of how to populate a box with the corresponding field from the master form.

ason1976

 

[Trendsetter]

On a pop up form in the Open_Form event you can enter

Me.ProductDetails.OrderDetailID = forms![Order Details]![OrderDetailID]

or in the Default Value of the OrderDetailsID on the Product Details form enter:
Forms![Order Details]![OrderDetailID]

 

[KornGeek]

I agree with Trendsetter that your logic might be slightly awry.

I would suggest having the orders be the main form, then you can have one subform showing the customer info, and another showing the order details.

 

[Ason1976]

KornGeek

My relationships are set up Customer(one) to (many)Orders(one) to (many)Order Details(one) to Product Details.

So in my way of thinking, having a Customers Subform kind of ruins the chain.

Could you elaborate?

Trendsetter, I will attempt and reply?
How would it work if the OrderDetailsID already contains a value?

Thansk for your advice

ason1976

hope I'm not rambling

 

[Ason1976]

Hi,

I have got the pop up to carry the value I need(thank you again).
But I cannot get the OrderID value from the Orderdetails subform(continous) to populate with the OrderID from the main form.

I am stumped

ason1976

hope i'm not rambling

 

[danvlas]

If you have room on your main form, you can include the ProductDetails as a subform and 'synchronize' it with the record in the OrderDetails subform.

On the main form create a calculated text box and name it LinkBox.
ControlSource:
=[FirstSubForm].Form![SecondSubform].Form![OrderDetailID]

Then, for the ProductDetails subform:
LinkChildFields: OrderDetailID
LinkMasterFields: LinkBox

If you do NOT have space on the form and wish to use a 'popup form', open it filtered:

DoCmd.OpenForm "FormName", acNormal, , "OrderDetailID = " & [FirstSubForm].Form![SecondSubform].Form![OrderDetailID]

(I assumed the button is on the main form)

Good luck



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Ason1976]

Hi People,


Here are my Forms

--------------------------------
|CUSTOMERS | ORDERs |
|(main) |(sub) |
| | |
| |___________
| |Order Details |
| |(sub of orders) |
| | |
| | |
________________________________


I have tried everything suggested to carry the ORDERID value from ORDERS to ORDER DETAILS.

HAlf the Time nothing happens and half the time I get macro errors( can't find macroname.macrosyntax).

Is my problem --Orders is a Single Form, where Order Details is a continous form.

Bashing my head on the keyboard

ason1976

hope i'm not rambling

 

[KornGeek]

Let me see if I can elaborate on my idea.
Even though each customer can have multiple orders, you're only actually concerned about the current order.

Each order has one customer, so it is a simple matter to get this customer's data. In fact, you don't even need to make it a subform. You can create a query that grabs the current order data and the corresponding customer's data. You could then use this query as the data source for the form.

So, here are (slightly) visual interpretations of what I'm describing.

1) (with Customer as a subform)
OrderData Form
| |
| V
V Details
Customer

2) (with Customer data merged)
OrderData (with Customer info) Form
|
V
Details

I hope this makes it a little bit clearer. I believe that the form wizard supports this easily.