Solved Append Query - 'Key Violations'

[HillTJ]

Friends, I'm stuck again! I'm writing an asset Management System. Basically I have a "Parent" piece of equipment to which are allocated "spareparts". I am developing a Purchase order 'frmPurchaseOrders' which allows me to build up up a PO for a particular supplier. Upon receipt of that line item, or completion of the PO, I wish to Append that line (or multiples if flagged as complete) to 'tblInventoryDetails', using 'qryAppendPOtoStock'. This is where the 'Key Violation' occurs. I'm new to append queries, so another learning curve..

Appreciate some assistance. The project is a work in progress ....Any other observations would be appreciated too. If I'm going about something wrong, please let me know.

 

[arnelgp]

you should add it to LineItemID:

INSERT INTO tblInventoryDetails ( LineItemID, POID, PartID, QuantityRecieved )
SELECT tblPO_Line_Item.LineItemID, tblPO_Line_Item.POID, tblPO_Line_Item.PartID, tblPO_Line_Item.QuantityRecieved
FROM tblPO_Line_Item
WHERE (((tblPO_Line_Item.POID)=[forms]![frmPurchaseOrders]![POID]) AND ((tblPO_Line_Item.LineComplete)=Yes));


and check tblInventoryDetails PK field?

 

[HillTJ]

Arnelgp, thanks for the prompt response, you have helped me many a time & i appreciate it. So, how best do i run this command ? It hasn't yet sunk in, but i'll have a go over the next few days.

 

[HillTJ]

arnelgp, Didn't work. See screenshot of error message attached.

 

[arnelgp]

becoz your table, tblInventoryDetails has a PK field InventoryID (?) and it's Default value is 0.
every time you insert record to this table and you don't specify InventoryID, 0 will be saved to
the table. Since this field is Unique, it won't accept any more 0 values for this field.

honestly i don't see the purpose of this field?

 

[HillTJ]

Arnelgp, i understand & will delete the PK field then have another go. Thanks, this is my first go at an append query so a'm keen to see it work for me. Thanks.

 

[HillTJ]

Gents, OK, so now I don't get the Key violations (great) but get 5 instances of 'validation rule violations'. I'll have to check for consistant validation rules, but it's progress. Thanks.

 

[HillTJ]

Pat, arnelgp, got it to work thanks to you both. I'm using the DoCmd.OpenQuery "qryAppendPOtoStock" method behind a button after deactivating warnings & this works, but I'd really like to show how many records are appended each 'press' of the button. My Append Query includes a 'parameter' & I tried unsuccessfully to 'execute' it. Error Message said 'too few parameters' & I read that parameters cause issues with 'execute' operations. I'd just like to show how many records are posted. Any Ideas?

 

[HillTJ]

Pat, Is there any advantage to the 'execute' method over the 'openquery' method? And, I'd like to display a message of the number of records 'posted' but haven't got my head around this. Appreciate any assistance.

 

[HillTJ]

Pat, Thanks. I'll give it a go.

 

[HillTJ]

Pat, I tried the following after some 'googling' & looking at bits of code but something is not correct. Can you please help. Code is below;

Private Sub Command70_Click()

If MsgBox("Do you want to post the Completed PO lines to Inventory?", vbYesNo, "Confirm Posting of Inventory ") = vbNo Then Exit Sub
    'DoCmd.SetWarnings False                              ' my original method
    'DoCmd.OpenQuery "qryAppendPOtoStock"  ' my original method
    'DoCmd.SetWarnings True                                ' my original method
    
    Dim db                    As DAO.Database
    Dim sSQL                  As String
    Dim qd                    As DAO.QueryDef
    Dim JobID                 As String
    
    On Error GoTo Error_Handler
    
    Set db = CurrentDb
    
    Set qd = db.QueryDefs("qryAppendPOtoStock")
        qd.Parameters!EnterJobID = JobID
        qd.Execute
 
    'Set db = CurrentDb
    'sSQL = "INSERT INTO tblInventoryDetails ( lineitemID, POID, PartID, QuantityRecieved ) " & vbCrLf & "SELECT tblPO_Line_Item.LineItemID, tblPO_Line_Item.POID, tblPO_Line_Item.PartID, tblPO_Line_Item.QuantityRecieved " & vbCrLf & "FROM tblPO_Line_Item " & vbCrLf & "WHERE (((tblPO_Line_Item.POID)=forms!frmPurchaseOrders!POID) And ((tblPO_Line_Item.LineComplete)=Yes));"
    'db.Execute sSQL, dbFailOnError
    
    If db.RecordsAffected = 0 Then
        MsgBox "No New Postings were created!"
    End If
 
Error_Handler_Exit:
    On Error Resume Next
    If Not db Is Nothing Then Set db = Nothing
    Exit Sub
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: cmd_AddRec_Click" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
    
End Sub

 

[arnelgp]

you declared JobID as string but you never assign any value to it.

 

[HillTJ]

arnelgp, so if I do this jobID=forms!frmPurchaseOrders!POID. Is this what you mean (see commented out sql statement that I'm trying to replicate) ? How do I incorporate this bit (tblPO_Line_Item.LineComplete)=Yes)

 

[bastanu]

Hi Terry,
Can you try this please?

Private Sub Command70_Click()

If MsgBox("Do you want to post the Completed PO lines to Inventory?", vbYesNo, "Confirm Posting of Inventory ") = vbNo Then Exit Sub
    'DoCmd.SetWarnings False                              ' my original method
    'DoCmd.OpenQuery "qryAppendPOtoStock"  ' my original method
    'DoCmd.SetWarnings True                                ' my original method
    
    Dim db                    As DAO.Database
    Dim sSQL                  As String
    Dim qd                    As DAO.QueryDef
    Dim JobID                 As String
    Dim prm as Parameter

    On Error GoTo Error_Handler
    
    Set db = CurrentDb
    
    Set qd = db.QueryDefs("qryAppendPOtoStock")
    
For each prm in qd.Parameters
    prm.Value=Eval(prm.Name)
Next prm 

'qd.Parameters!EnterJobID = JobID
        qd.Execute,dbFailOnError
 
    'Set db = CurrentDb
    'sSQL = "INSERT INTO tblInventoryDetails ( lineitemID, POID, PartID, QuantityRecieved ) " & vbCrLf & "SELECT tblPO_Line_Item.LineItemID, tblPO_Line_Item.POID, tblPO_Line_Item.PartID, tblPO_Line_Item.QuantityRecieved " & vbCrLf & "FROM tblPO_Line_Item " & vbCrLf & "WHERE (((tblPO_Line_Item.POID)=forms!frmPurchaseOrders!POID) And ((tblPO_Line_Item.LineComplete)=Yes));"
    'db.Execute sSQL, dbFailOnError
    
    If db.RecordsAffected = 0 Then
        MsgBox "No New Postings were created!"
    End If
 
Error_Handler_Exit:
    On Error Resume Next
    If Not db Is Nothing Then Set db = Nothing
    Exit Sub
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: cmd_AddRec_Click" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
    
End Sub

As for the other issue to getting the number of records you could use a dCount on the source table (tblPO_Line_Item) with the criteria like in the append query.
Cheers,

 

[HillTJ]

Gents, Got it to work. My only problems is the if ...recordsAffected... code, where I wish to show the number of records affected. It doesn't work. I'm indebted to all whom helped. I've learn't something new!

For the record my code became;

Private Sub Command70_Click()

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter


On Error GoTo Error_Handler


If MsgBox("Do you want to post the Completed PO lines to Inventory?", vbYesNo, "Confirm Posting of Inventory ") = vbNo Then
    Exit Sub
End If


Set qdf = CurrentDb.QueryDefs("qryAppendPOtoStock")
Set dbs = CurrentDb()


For Each prm In qdf.Parameters
        prm = Eval(prm.Name)
    Next prm


   qdf.Execute
        If dbs.RecordsAffected = 0 Then                                                               ' this bit doesn't return correctly.
             MsgBox "No New records were Posted"
        Else
            MsgBox CStr(dbs.RecordsAffected) & " Records were Posted."
        End If
    
Set qdf = Nothing
Set dbs = Nothing


Error_Handler_Exit:
    On Error Resume Next
    If Not dbs Is Nothing Then Set dbs = Nothing
    Exit Sub
 
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: cmd_AddRec_Click" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

 

[arnelgp]

replace dbs.RecordsAffected with qdf.RecordsAffected

 

[HillTJ]

arnelgp,Worked. Thanks.