Update Query

N

Nick1966

Hi

I need to be able to create a query or event procedure that will update the
status of outstanding orders on Access 2000. Current situation relies on
user closing Orders when all of the Order Details are complete. I have tried
an Update query but that closes the Order when any of the Order Details are
complete, I need to run it so that it doesn't close the Order until All
Order Details are completed - hope that made sense.


Nick
 
J

John Vinson

Hi

I need to be able to create a query or event procedure that will update the
status of outstanding orders on Access 2000. Current situation relies on
user closing Orders when all of the Order Details are complete. I have tried
an Update query but that closes the Order when any of the Order Details are
complete, I need to run it so that it doesn't close the Order until All
Order Details are completed - hope that made sense.
Click to expand...

If you're using the standard form, with Orders on the mainform and
Details on a subform, this can be rather difficult. In order to
maintain referential integrity, Access must save the record into the
Order table FIRST, so that there is a record there for the
OrderDetails to link to. One idea might be to have a Yes/No field
"Complete" in the Orders table, and provide the user with a checkbox
or command button to indicate that they have entered all the needed
details - otherwise there is no simple way for Access to know that
there might not be more details coming.

Could you explain the user interface? Ordinarily one would not need
any update queries - are your users entering data in tables directly?

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
N

Nick1966

Hi John

Thank you for your reply.

The situation at the moment is that Orders are on the Main Form and the
Order Details are on a Sub Form. On the Sub Form the user has a checkbox to
tick when the detail is completed, once all the details are complete then
the user checks a tick box on the Main Form to complete the Order.

Before I copied the Database to the user I tested the system with various
scenarios and found no problem and I provided reports to enable the user to
check if Orders had been completed in error without all the details being
complete and vice-versa. The user has reported that the Order has been
closed on occasions without the user ticking the checkbox, to solve this
"problem" I wanted to remove the need for the user to check the close Order
box on completion of the details section hence my query.

Is it possible to match the number of rows available in the subform to the
number enter on the order. This may make it easier to check that all details
have been entered on the subform before closing the order i.e. if the Order
states 5 samples then subform displays only 5 rows to enter details.

Thanks

Nick
 
J

John Vinson

Is it possible to match the number of rows available in the subform to the
number enter on the order.
Click to expand...

Not readily, and not desirably. The detail records are NOT stored on
the subform; they're stored in a Table, and tables have no record
numbers. Besides, it seems rather backwards to require that every
order must have five (never four, never six) detail items - it defeats
the whole flexibility of a relational database! Is that in fact the
case? If a customer orders seven items, do you want to arbitrarily
create two orders (for five and two items, with three blank "dummy"
items on the second order)?

I would suggest perhaps having a check at a later stage, for instance
before you *print* the order. You could have a check such as:

If DLookUp("[OrderComplete", "Orders", "[OrderID] = " & [OrderID]) _
= False Then
MsgBox "Only complete orders can be printed", vbOKOnly
Cancel = True
End If
If DCount("*", "[OrderDetails]", "[OrderID] = " & [OrderID]) > _
DCount("*", "[OrderDetails]", "[OrderID] = " & [OrderID] _
& " AND ItemComplete = True") Then
MsgBox "Not all details were completed", vbOKOnly
Cancel = True
End If

in the Report's Open event.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Top