Public Function isOrderDispatched(OrderID) As Boolean
Dim con As Object
Dim rs As Object
Dim rs2 As Object
Dim stSql As String
Set con = Application.CurrentProject.Connection
'first check if beer items exist on the order
stSql = "SELECT COUNT(OrderDetailID) as BeerItems FROM [Order Details] od "
stSql = stSql & " LEFT JOIN [Products] pd "
stSql = stSql & " ON od.ProductID = pd.ProductID"
stSql = stSql & " WHERE OrderID = " & OrderID
stSql = stSql & " AND pd.CaskType <> 'UU'"
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con
If (rs.EOF) Then
'no beer items on the order so not dispatched, user can edit the order.
isOrderDispatched = False
Else
While (Not (rs.EOF))
If rs![BeerItems] > 0 Then
'if there are beer items on the order then check if they are all dispatched.
stSql = "SELECT COUNT(OrderDetailID) as NotAllocated FROM [Order Details] od "
stSql = stSql & " LEFT JOIN [Products] pd "
stSql = stSql & " ON od.ProductID = pd.ProductID"
stSql = stSql & " WHERE od.CasksAssigned < od.Quantity "
stSql = stSql & " AND OrderID = " & OrderID
stSql = stSql & " AND pd.CaskType <> 'UU'"
'check order has beer items.
Set rs2 = CreateObject("ADODB.Recordset")
rs2.Open stSql, con
While (Not (rs2.EOF))
If rs2![NotAllocated] > 0 Then
isOrderDispatched = False
Else
isOrderDispatched = True
End If
rs2.MoveNext
Wend
Else
isOrderDispatched = False
End If
rs.MoveNext
Wend
End If
isOrderDispatched = False
End Function