I am updating a db and have made some changes to it and I am unable to figureour how to make the backorder module work correctly
My tables are as such:
Order table:
OrderPK (AutoNumber)
CustomerOrderNumber (Text)
CustomerID
etc.
Order Details Table:
DetailPK (AutoNumber)
OrderFK (Number)
PartNumber
etc.
Temp Order Table:
Same as Order table
I am having problems with imputing the new backorder information into the tables
Code:
My tables are as such:
Order table:
OrderPK (AutoNumber)
CustomerOrderNumber (Text)
CustomerID
etc.
Order Details Table:
DetailPK (AutoNumber)
OrderFK (Number)
PartNumber
etc.
Temp Order Table:
Same as Order table
I am having problems with imputing the new backorder information into the tables
Code:
Code:
Private Sub cmdInvoice_Click()
If IsNull([InvoiceNo]) Or IsNull([InvoiceDate]) Then
MsgBox "Invoice number and date are required.", vbExclamation
Exit Sub
End If
[Invoiced] = True 'set invoiced flag
Dim rs As Recordset, blnBackOrder As Boolean
Set rs = CurrentDb.OpenRecordset("Select * From qryOrderDetails Where OrderFK=" & [OrderPK] & "")
With rs
Do While Not .EOF And Not blnBackOrder
If Nz(!QtyShipped, 0) < !QtyOrdered Then blnBackOrder = True
.MoveNext
Loop
Dim strOrderNo As String, strBackOrderNo As String, strProformaNo As String, strCustID As String, strSQL As String
strOrderNo = [OrderPK]
strBackOrderNo = [CustomerOrderNumber] & "BO"
If blnBackOrder Then
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * From tblTempOrder"
DoCmd.RunSQL "Insert Into tblTempOrder Select * From tblIndentOrder Where OrderPK=" & [OrderPK] & ""
DoCmd.RunSQL "Update tblTempOrder Set CustomerOrderNumber='" & strBackOrderNo & "', Invoiced=False,InvoiceNo=Null,InvoiceDate=Null"
DoCmd.RunSQL "Insert Into tblIndentOrder Select * From tblTempOrder"
Me.Refresh 'refresh Orders table
.MoveFirst
Do While Not .EOF
If Nz(!QtyShipped, 0) < !QtyOrdered Then
strSQL = "Insert Into tblIndentOrderDetail Values ('" & strOrderNo & "','" & !PartNumberFK & "'," & !DiscountedPrice & "," & (!QtyOrdered - Nz(!QtyShipped, 0)) & ",Null)"
DoCmd.RunSQL strSQL
End If
.MoveNext
Loop
End If
End With
'Me.Refresh 'refresh order details subform
Me.Requery
If blnBackOrder Then 'go to back order
Set rs = Me.Recordset.Clone
rs.FindFirst "[OrderPK]='" & strOrderNo & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If
DoCmd.OpenReport "rptOrders", acViewPreview, , "OrderNo='" & strOrderNo & "'", , "Invoice"
End Sub