Unbound field problems

[TinkerMan]

Hi all

Consider the following:
I have orders from an order table, a table for delivered items (as customer might not get wat is ordered due to various reasons), a client table and an item table.

My current problem revolves around the packers form, to enter what is delivered in a simple manner. The main form shows a customer and the sub form shows the delivery form. This delivery form shows fields for one customer, and the rows are "per item":
- Item name
- sum Ordered
- packed (unbound)
- delivered (checkbox)
- actual delivered
- Diff (between "sum ordered" and "actual delivered")

My first limitation is that the order numbers are obtained by crosstabbing and various unions and joins, so the rows are not directly updatable. The workflow I vision is:
1. The "packed" field should have a default value of "sum ordered"
2. User verifies or changes the packed field to the number of items being packed
3. User then clicks the "delivered" checkbox and the system inserts a row in the delivered table, refreshes the forrm and the "actual delivered" should be filled in as well as the "delivered" checkbox checked
4. At some point user wants to change delivered item and updates the "packed" field and the system changes the "delivered" checkbox to unchecked)
5 user verifies change of number and checks the "delivered" checkbox and the system updates the delivered record and does a refresh

At first I tried to have the "packed" constrol source to "iif(isNull([actual_delivered]);[sum_ordered];[actual_delivered]". This expression works, but when I try to change the value is says "form is read-only" (when I try to change some other field it says "Th recordset is not updateable"). I then took away the expression and it was updateable, but as soon as the field is updated, all the rows gets the same value.

I thught about setting the "packed" value in an event, but it seems as long as it is unbound, all rows will have the same value (tried on curent). To me it feels like a chicken and egg situation, which usually means something is not done right. I also believe that I can "fake" the checking of the checkbox by using a "mouse down" or "key down" to catch the user's intention and then update the underlaying values and do a refresh. It doesn't feel right, but that's the direction this is taking because of the uneditableness.

Has anybody got an idea of how to mix non-updateable and updatable fields in the same subform and have seperate values in an unboud field? or a bettwe way of doing this?

Thaks

 

[MarkK]

Consider tables and fields as follows:
Table: Customer; Fields: CustomerID, etc...
Table: Order; Fields: OrderID, CustomerID, etc...
Table: OrderDetail; Fields: OrderDetailID, OrderID, ItemID, QtyOrdered, QtyDelivered, etc...
Table: Item; Fields: ItemID, etc...

Consider forms as follows:
Form: Customer
Form: Order; SubForm: OrderDetail; LinkField: OrderID
Form: Item

Problems you appear to have:
Retrieving OrderID thru 'various crosstabs unions and joins' is getting in the way.
Items in an Item table, and the QtyOrdered (of items) that might be included in an OrderDetail are distinctly different things.
Delivered should apply to the whole order, not it's details.
Don't start a new table for things that simply have a different 'Status', for instance, don't make three tables for RedThings, BlueThings, and GreenThings, but rather, make one 'Things' table with a 'Colour' field.

If QtyOrdered and QtyDelivered are different, consider creating a new Order to handle the difference.
And these are not authouritative 'answers' but might offer you a place to start looking. Trust your sense, as you seem to, that if things are getting too complicated you're on the wrong track.

Cheers

 

[TinkerMan]

Thanks lagbolt

Yes, in a "normal" order scenarion your solution is the appripriate one, but I'm not sure if it applies in mine. I'm sorry for not giving this information in the original post, but I have some complicating factors.

Orders are not formalised in the normal sense, as customers do not pay per order, but received quantity. The orders are more like "request for items". In addition the orders are of two types (in the same table): "Static" - Has a start date and no end date (as in "I want x amount of item y on mondays until I change my mind". The second type is "extra" - Short-term modifications (Next week i need 5 items extra of item y, or less items (a negative order quantity). As you understand I therefore need to summarize the information. That is done first as crosstabs per order type, but as there might be "empty" rows for different items an outer join is required. As Access does not support this, I need to do first a right join, then a left join and finally a union of the two (Thanks for that Pat H.).

I realise that this is a complicated way of organizing orders, but whatever the customer says........and yes I know this often leads to problems

So as far as I can see, the ordered quantity has to be summarised and read-only. So my problem boils down to how do I combine this information together with updatable fields/rows?

An in case you're wondering who needs this kind of system, its a bakery

Any suggestion is appreciated

Thanks

 

[MarkK]

Tinker:
Not sure I can offer you much more then. I can't see how crosstabs or unions could be updateable since it seems that these both are necessarily summaries that obscure the origins of individual data points; one-way tickets as it were.

But it seems that regardless of whether 'customers' pay or not, there needs to be a way to clear an order or declare it completed or filled or shipped. Maybe you need another structure in which you define a template for a memorized or 'Standing Order' for a particular customer, and from this template a new Order is periodically created, say evey Monday.

Or each order could have an 'IsPeriodic' field, and when you declare it completed it inserts a copy of itself n days later.

Brainstorming...
G'luck...
Mark