Chaging order status

[moretickets]

Hi there,

I have a table with orders and order status which can be selected from a pull
down list with choices of on hold, shipped, cancelled, returned, delivered. Once
the order has been delivered I dont want anyone to have the ability to change
the status to anything. How can this be done?

Thanks [sig][/sig]

 

[THoey]

Might I suggest an OrderShipped date? When the order gets set to shipped, insert the sysdate into this field. Then you can write some VBA that would check to see if this field had a value (not null) and only update the record if that date field is still Null.

Hope that helps...
[sig]<p>Terry M. Hoey<br><a href=mailto:th3856@txmail.sbc.com>th3856@txmail.sbc.com</a><br><a href= > </a><br>Ever notice that by the time that you realize that you ran a truncate script on the wrong instance, it is too late to stop it?[/sig]

 

[jagilman]

the suggestion to use the shipdate is a good one. You usually dont know when somthing is delivered. If you are using a form to present this information to the user, you could use the current event of the form to enable or disable any control or group of controls. Just have some field that you can use to base your decision on. If you need esamples just ask. [sig]<p>John A. Gilman<br><a href=mailto:gms@uslink.net>gms@uslink.net</a><br>[/sig]

 

[DaveSH]

Hi More Tix,

Lets say we have the following on our form:
Text Box &quot;Id&quot;
Combo Box &quot;CbStatus&quot; <---- The one were concerned with.

In the afterupdate event of CbStatus put the following code:

Private Sub CbStatus_AfterUpdate()
Dim StrMsg As String, OrigStatus

OrigStatus = Forms!Form1![CbStatus].OldValue
StrMsg = &quot;You are about to PERMANENTLY change the order status.&quot;
If Me![CbStatus] = &quot;Shipped&quot; Then

If MsgBox(StrMsg, vbOKCancel, &quot;Confirmation&quot = vbCancel Then
Me![CbStatus] = OrigStatus
Exit Sub
Else
Me![id].SetFocus
Me![CbStatus].Enabled = False
Me![CbStatus].Locked = True
Me![CbStatus].ForeColor = 8421504
Me![CbStatus].BackColor = 12632256
End If
End If
End Sub

In the forms on current event put the following code:

Private Sub Form_Current()
If Me![Combo43] = &quot;Shipped&quot; Then
Me![id].SetFocus
Me![Combo43].Enabled = False
Me![Combo43].Locked = True
Me![Combo43].ForeColor = 8421504
Me![Combo43].BackColor = 12632256
Else
Me![Combo43].Enabled = True
Me![Combo43].Locked = False
Me![Combo43].ForeColor = 0
Me![Combo43].BackColor = 16777215
End If
End Sub

This code will prompt the user to confirm the change. Then if they cancel it will return the previous status unchanged. If they click OK, the status will be changed to shipped, the combo box locked and greyed out.

You will want to create a procedure somewhere that allows you to change the status back from shipped particularly since you indicate that &quot;returned&quot; is a potential status.

Oops ... I re-read your post and see that my code is using &quot;Shipped&quot; as the final status. You apparently are distinquishing between Shipped and Delivered, so you can substitute your terminology, you get the idea.

Hope this helps [sig]<p>Dave<br><a href=mailto:gallagherd@earthlink.net>gallagherd@earthlink.net</a><br>[/sig]