My bad!
I'd misdefined the relationship between the header and item records.
Answer - don't write and debug code after midnight!!!!
--- In MS_Access_Professionals@yahoogroups.com, "d_i_s_r" wrote:
>
> I've been puzzling over this for the last couple of hours.
> The reason is probably staring me in the face but I just can't see it.
> Anybody got any advice please?
>
> Code follows: (excuse the wrapping!)
>
> ' Create a tblFTPHistoryHeader record
> '
> strSqlString = "INSERT INTO tblFTPHistoryHeader (FTPHistoryQueueID" & _
> ") VALUES (" & _
> FTPID & _
> ")"
> CurrentDb.Execute (strSqlString)
> Dim rs As DAO.Recordset
> Set rs = CurrentDb.OpenRecordset("SELECT @@IDENTITY")
> If Not rs.EOF Then
> lngtblHistoryHeader = rs.Fields(0)
> Else
> ' ERROR MsgBox "No AutoNumber generated."
> End If
> rs.Close
> Set rs = Nothing
> Note that this is working OK
> '
> ' Now examine the log file to see if the transfer was successful
> '
> strLogArray = Split(LogFile, vbCrLf)
> boolSuccess = False
> intLineCount = 0
>
> Do
> Code snipped for brevity
> '
> ' write the text line to the FTPDetail table
> '
> strLogArray(intLineCount) = Replace(strLogArray(intLineCount), strQ, strQQ)
> strSqlString = "INSERT INTO tblFTPHistoryDetail (FTPHistoryHeaderID," & _
> "FTPDetailLineNo," & _
> "FTPDetailText)" & _
> " VALUES (" & _
> FTPID & "," & _
> intLineCount + 1 & "," & _
> strQ & strLogArray(intLineCount) & strQ & _
> ")"
> CurrentDb.Execute (strSqlString)
> intLineCount = intLineCount + 1
> Loop Until intLineCount > UBound(strLogArray)
>
> '
> ' Now update the FTPHistoryHeader record with the Timestamp and result
> '
> tblFTPQueueRecordSet("FTPQXferTimeStamp").value = strRunDate
> tblFTPQueueRecordSet("FTPQStatus").value = boolSuccess
> tblFTPQueueRecordSet.Update
>
> strSqlString = "UPDATE tblFTPHistoryHeader " & _
> "SET " & _
> "FTPHistoryXferTimeStamp=" & strHash & strRunDate & strHash & _
> "," & _
> "FTPHistoryStatus=" & boolSuccess & _
> " WHERE " & _
> "FTPHistoryID=" & lngtblHistoryHeader
> CurrentDb.Execute (strSqlString)
>
> Code ends
>
> Their are no records found in table tblFTPHistoryDetail.
> The input log has 67 lines in it.
> I manually created a row in the table and was assigned a primary key value of 1502.
> I ran the routine again, no additional rows.
> I then manually added another row and recieved PK field value of 1571. Do the math, 1502 + 67, next new record would be 1571!
>
> Regards,
> David Rees
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
Tidak ada komentar:
Posting Komentar