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.

 

[Pat Hartman]

Delete the 0 default, not the PK field

 

[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.

 

[Pat Hartman]

If you define a relationship between the parent table and the child table, Access will automagically link the two when you add a subform to a main form. That way, when you insert a row in the subform, Access automatically populates the FK that points to the main record.

Access automatically sets 0 as the default for numeric fields. That helps novices working with numbers who don't understand the import of null values but it is 100% wrong as the default for a foreign key. Foreign keys should have NO default. Access has no way ahead of time to determine your use of the numeric field so it can't make the appropriate choice. Only YOU can do that..

 

[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?

 

[Pat Hartman]

To use the .execute method, you need to set the parameter values first. One at a time if there are multiple parameters.

    Set qd = db.QueryDefs!qUpdateSfx
        qd.Parameters!EnterJobID = JobID
        qd.Execute

 

[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.

 

[Pat Hartman]

You get more flexibility when you use the .execute method and you don't have to toggle warnings off to avoid informational messages so all in all, I normally use .execute.

 

[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,

 

[Pat Hartman]

Are you sure JobID is a string?

I would expect to see:

qd.Parameters!EnterJobID = Me.JobID

Since JobID should be available on the form where the code is running. I don't understand why you referred to JobID as POID. Surely you aren't using two names for the same variable?

You haven't told us what error message you are getting or when it is happening. You also haven't posted the text of the query you are trying to run. Does the query work when you run it directly? Remember, if you are referencing a form field, the form MUST be open.

 

[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