Append query doesn't work but no errors are generated

[PeteJohnston]

I have a query that accepts 3 parameters and should insert a record into a linked table. It doesn't kick up any errors but it doesn't write the record to the table either.

Set qDef = db.QueryDefs("qryInsertChild")
qDef.Parameters("Master ID") = txtID
    
' Process all of the selected records
sSQL = "Select * From tblGenerated " & _
        "Where User = '" & cSystem.WinUserName & "' " & _
        "And Selected = True "
Set rst = db.OpenRecordset(sSQL)
If rst.RecordCount > 0 Then
    rst.MoveFirst
    Do While Not rst.EOF
        qDef.Parameters("Due to Run") = rst.Fields("RunDate")
        qDef.Parameters("Due to Requestor") = rst.Fields("RequestorDate")
        Err.Clear
        qDef.Execute dbFailOnError
        If Err.Number = 0 Then
            ConfirmRequests = ConfirmRequests + qDef.RecordsAffected
        End If
        rst.MoveNext
    Loop
End If

There are no errors when it tries to execute the query. The error structure Err.number shows as zero. The RecordsAffected property of the query definition shows that 1 record has been inserted. I haven't used On Error Resume Next (work of the devil) and as far as I can see it should have inserted the record. It didn't. The query definition is defined as a DAO QueryDef.

If anyone can come up with a solution I will be forever in their debt.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[genomon]

I don't see where the parameters are declared.
Any help here?

https://msdn.microsoft.com/en-us/library/office/ff193967.aspx

Beir bua agus beannacht!

 

[PeteJohnston]

Query qryInsertChild is a parameter query that reads one record from a table with a specific ID and inserts a record into the same table but with different dates. The ID is an auto number field and is the primary key. Running the query interactively prompts for three parameters, the ID and two dates. Running it in VBA allows you to specify the parameters as shown.

The link is Microsoft's explanation of one way to use a QueryDef object. Their example is creating a temporary query definition but you can also use it to run queries that you have previously created. Where a parameter is required the value is provided using the technique that I posted.

The article doesn't make any reference to a situation where a query fails to write a record but gives no errors to say why. Unfortunately I need to find the reason that it is failing.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[PeteJohnston]

Eventually I found it. Sharepoint did not always tell Access that a field is required or read-only and it fails at the time the record is inserted. The fact that Sharepoint doesn't tell Access that it has failed seems to be ignored. Once I got the field values sorted it inserted the record.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)