Help with backorder creator in VB (1 Viewer)

[tl mike]

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:

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

 

[boblarson]

I am having problems with imputing the new backorder information into the tables

What problems are you experiencing while inputing? More explanation is required. We aren't there so we can't see what is happening (or not happening). You must supply us with INPUT, NEED INPUT (to quote Johnny 5 from the movie Short Circuit).

 

[tl mike]

I am working on cleaning up a copy so it can be looked at.

But it looks like my problem is partly because of the OrderPK (autonumber) and CustomerOrderNumber

What needs to happen is the CustomerOrderNumber gets a BO attached to the end of it and the new order imput into the Orders and order detail table with a new autonumber assigned to it.

 

[tl mike]

See attach db:

I have an order added

Open the invoice form enter a invoice number and date and press the invoice button

 

[tl mike]

I have been narrowing down my problem.

What I need to do is clear the OrderPK number on the tblTempOrder table after it has been loaded into the table before it is then uploaded to the tblIndentOrder.

My current code looks like this:

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 strOrderPK As String, strBackOrderNo As String, strProformaNo As String, strCustID As String, strSQL As String, strBackOrderPK As String
strOrderPK = [OrderPK]
strBackOrderPK = [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 & "', BOOrderFK='" & strBackOrderPK & "', Invoiced=False,InvoiceNo=Null,InvoiceDate=Null"
 
    DoCmd.RunSQL "Update tblTempOrder Set OrderPK=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 ('" & strBackOrderPK & "','" & !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]='" & strBackOrderPK & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If
DoCmd.OpenReport "rptOrders", acViewPreview, , "OrderNo='" & strBackOrderPK & "'", , "Invoice"
End Sub

 

[tl mike]

Ok I have been working on this and my current problem is when it goes to upload the temp table information into the orders table I need to have the db create a new autonumber for the OrderPK (primary key is setup as an autonumber) but because if the insert function it is trying to insert a null value into the autonumber which wont allow it.

How do I get the db to create a new Primary key to allow the insertion of the temporary order table information??

My current code looks like this

Private Sub cmdInvoice_Click()
'Check for invoice number and invoice date
If IsNull([InvoiceNo]) Or IsNull([InvoiceDate]) Then
MsgBox "Invoice number and date are required.", vbExclamation
Exit Sub
End If
[Invoiced] = True 'set invoiced flag
'Select record set
Dim rs As Recordset, blnBackOrder As Boolean
Set rs = CurrentDb.OpenRecordset("Select * From qryOrderDetails Where OrderFK=" & [OrderPK] & "")
With rs
'Check for Qunatity shipped
Do While Not .EOF And Not blnBackOrder
If Nz(!QtyShipped, 0) < !QtyOrdered Then blnBackOrder = True
.MoveNext
Loop
'Create Backorder
Dim strOrderPK As String, strBackOrderNo As String, strProformaNo As String, strCustID As String, strSQL As String, strBackOrderPK As String
strOrderPK = [OrderPK]
strBackOrderPK = [OrderPK]
strBackOrderNo = [CustomerOrderNumber] & "BO"
 
If blnBackOrder Then
    DoCmd.SetWarnings False
    DoCmd.RunSQL "Delete * From tblTempOrder" 'Clear temp order table
    DoCmd.RunSQL "Insert Into tblTempOrder Select * From tblIndentOrder Where OrderPK=" & [OrderPK] & "" 'Insert data to Temp table linking order number
    DoCmd.RunSQL "Update tblTempOrder Set CustomerOrderNumber='" & strBackOrderNo & "', BOOrderFK='" & strBackOrderPK & "', Invoiced=False,InvoiceNo=Null,InvoiceDate=Null" 'change invoice info
    DoCmd.RunSQL "Update tblTempOrder Set OrderPK=Null"
    DoCmd.RunSQL "Insert Into tblIndentOrder Select * From tblTempOrder" 'Insert data from temp table to orders table
    Me.Refresh 'refresh Orders table
    .MoveFirst
'Insert backorder details into order details table
    Do While Not .EOF
    If Nz(!QtyShipped, 0) < !QtyOrdered Then
    strSQL = "Insert Into tblIndentOrderDetail Values ('" & strBackOrderPK & "', '" & !PartNumberFK & "', " & !DiscountedPrice & ", " & (!QtyOrdered - Nz(!QtyShipped, 0)) & ", Null)"
    DoCmd.RunSQL strSQL
    End If
    .MoveNext
    Loop
 
End If
End With
'refresh order details subform
Me.Requery
If blnBackOrder Then 'go to back order
Set rs = Me.Recordset.Clone
rs.FindFirst "[BOOrderFK]='" & strBackOrderPK & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If
DoCmd.OpenReport "rptOrders", acViewPreview, , "OrderNo='" & strOrderPK & "'", , "Invoice"
End Sub

 

[wiklendt]

how did the old version do it? can you go back and check there? i often recycle working code from one database to another.

 

[tl mike]

That is what I also try to do but since the original db was setup without autonumbers as the primary key they had the primary key set on the customers PO number which recently became a problem when different customers have the same PO number. So now I am trying to rebuild the program correctly with autonumbers as the PK to help
alleviate this problem.

I have been able to figure out this part of my problem by creating an append query but now I have a new problem with the Orders details.

I need to be able to get the new appended back orders PK and add it to the order details "order number foreign" key so that the data is linked correctly.

How do I obtain the new order PK for this use???

My Current code look like this:

Private Sub cmdInvoice_Click()

'Check for invoice number and invoice date
If IsNull([InvoiceNo]) Or IsNull([InvoiceDate]) Then
MsgBox "Invoice number and date are required.", vbExclamation
Exit Sub
End If
[Invoiced] = True 'set invoiced flag
'Select record set
Dim rs As Recordset, blnBackOrder As Boolean
Set rs = CurrentDb.OpenRecordset("Select * From qryOrderDetails Where OrderFK=" & [OrderPK] & "")
With rs
'Check for Qunatity shipped
Do While Not .EOF And Not blnBackOrder
If Nz(!QtyShipped, 0) < !QtyOrdered Then blnBackOrder = True
.MoveNext
Loop
'Create Backorder
Dim strOrderPK As String, strBackOrderPK As String, strProformaNo As String, strCustID As String, strSQL As String, strBackOrderPK As String
strOrderPK = [OrderPK]
strBackOrderPK = [OrderPK]
strBackOrderPK = [CustomerOrderNumber] & "BO"

If blnBackOrder Then
    DoCmd.SetWarnings False
    DoCmd.RunSQL "Delete * From tblTempOrder" 'Clear temp order table
    DoCmd.RunSQL "Insert Into tblTempOrder Select * From tblIndentOrder Where OrderPK=" & [OrderPK] & "" 'Insert data to Temp table linking order number
    DoCmd.RunSQL "Update tblTempOrder Set CustomerOrderNumber='" & strBackOrderPK & "', BOOrderFK='" & strBackOrderPK & "', Invoiced=False,InvoiceNo=Null,InvoiceDate=Null" 'change invoice info
    DoCmd.RunSQL "Update tblTempOrder Set OrderPK=Null"
    DoCmd.OpenQuery "apdOrder", acViewNormal, acAdd
  
    
    Me.Refresh 'refresh Orders table
    .MoveFirst
'Insert backorder details into order details table
    Do While Not .EOF
    If Nz(!QtyShipped, 0) < !QtyOrdered Then
    strSQL = "Insert Into tblIndentOrderDetail Values ('" & strBackOrderPK & "','" & !PartNumberFK & "'," & !DiscountedPrice & "," & (!QtyOrdered - Nz(!QtyShipped, 0)) & ",Null)"
    DoCmd.RunSQL strSQL
    End If
    .MoveNext
    Loop
End If
End With
'refresh order details subform
Me.Requery
If blnBackOrder Then 'go to back order
Set rs = Me.Recordset.Clone
rs.FindFirst "[BOOrderFK]='" & strBackOrderPK & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End If
DoCmd.OpenReport "rptOrders", acViewPreview, , "OrderNo='" & strOrderPK & "'", , "Invoice"
End Sub