Access 2000 .adp
SQL 2000 backend
I have an order tracker. I am keeping track of the status of each order. So I have an OrderStatus table with fields such as PK -> OrderStatusID, DateTime, OrderID, StatusID, Notes. The StatusID field on the form is a combo box that pulls from another table called Status. Which basically is just a 2-3 field table identifying the status names possible basically PK -> StatusID, StatusName, and Description.
So the form basically drops down a list of the Statuses for someone to choose from and that info is stored in the OrderStatus table. My question is......
What might be the easiest way to make that list customized???? I want to look at the previous entry and customize the list based upon the last "StatusID" put in for that Order. For Instance....
If the regular progession for an order is......Ordered, Sent to Contractor, Returned From Contractor, Sent to Client, Completed to Client, Invoiced, Paid.........
You can always have a status of Cancelled that can come in at any time AND you wouldn't want a status of "Paid" or "Invoiced" to be available to be entered if the Previous status was "Sent to Contractor"(since you do not want to invoice unless the job has been completed to to the client)
And subsequently if a job has been Invoiced you do not want a status of "Ordered" to be available as well. A stsus of Updated Invoice and Paid would be preferable.
Now as if this doesn't get complicated enough.....it cannot be as simple as saying a StatusID number greater or lesser than the last status. The simple heirarchy cannot work in this situation. Each status has a custom set of statuses that could come after it...and it doesn't fall neatly into a specific order.
My initial thought is to program the "got focus" event in order to create the list. Basically...
run a query to determine the last status
If status is "On Hold", or "Need Info" Then I have to look at the record before to determine the last status....ok no big deal so far create a recordset and navigate to assign a variable to what the last status would be....
Then I would set the rowsource type to Value list, create a Select case statement off of the variable and then provide the values in the case statements.....
This is my current plan...but it seems rather brutish...so I wanted to see if anyone had a more elegant, cleaner, possibly faster approach.....Any Ideas????
thanks ahead of time.
SQL 2000 backend
I have an order tracker. I am keeping track of the status of each order. So I have an OrderStatus table with fields such as PK -> OrderStatusID, DateTime, OrderID, StatusID, Notes. The StatusID field on the form is a combo box that pulls from another table called Status. Which basically is just a 2-3 field table identifying the status names possible basically PK -> StatusID, StatusName, and Description.
So the form basically drops down a list of the Statuses for someone to choose from and that info is stored in the OrderStatus table. My question is......
What might be the easiest way to make that list customized???? I want to look at the previous entry and customize the list based upon the last "StatusID" put in for that Order. For Instance....
If the regular progession for an order is......Ordered, Sent to Contractor, Returned From Contractor, Sent to Client, Completed to Client, Invoiced, Paid.........
You can always have a status of Cancelled that can come in at any time AND you wouldn't want a status of "Paid" or "Invoiced" to be available to be entered if the Previous status was "Sent to Contractor"(since you do not want to invoice unless the job has been completed to to the client)
And subsequently if a job has been Invoiced you do not want a status of "Ordered" to be available as well. A stsus of Updated Invoice and Paid would be preferable.
Now as if this doesn't get complicated enough.....it cannot be as simple as saying a StatusID number greater or lesser than the last status. The simple heirarchy cannot work in this situation. Each status has a custom set of statuses that could come after it...and it doesn't fall neatly into a specific order.
My initial thought is to program the "got focus" event in order to create the list. Basically...
run a query to determine the last status
If status is "On Hold", or "Need Info" Then I have to look at the record before to determine the last status....ok no big deal so far create a recordset and navigate to assign a variable to what the last status would be....
Then I would set the rowsource type to Value list, create a Select case statement off of the variable and then provide the values in the case statements.....
This is my current plan...but it seems rather brutish...so I wanted to see if anyone had a more elegant, cleaner, possibly faster approach.....Any Ideas????
thanks ahead of time.
