Value in last record using ADO 1

[swaybright]

I am stumbling my way through this, so any help will be great.
I am retrieving a lot of data from a Lotus Notes database and am trying to normalize the data at the same time. So, I want to put the data into two tables and assign a primary key/foriegn key relationship. The primary key is autonumbered in Table 1 so that part is easy. The problem is in assigning the foriegn key in Table 2 to equal the appropriate primary key in Table 1.
My code is at the end of this post, but since it's long, let me summarize:
1. Append the data for first document in Notes to Table 1.
2. Get the primary key of Table 1 for the last record in table (presumably the one just appended)
3. Append data in table 2 and assign the foriegn key.
4. Loop for all records in table 2 that relate to table 1 record
5. Loop to the beginning and get data for the next document in Notes

The problem is, sometimes (not always) the ADO code does not get the last record primary key and it assigns the foriegn key incorrectly (Like it forgets to go to the last record and just uses the previous value for the foriegn key).

Set regDb = New ADODB.Recordset
regDb.Open "Select TempTsrID From zTempTSRData", _
      CurrentProject.Connection, adOpenDynamic
regDb.MoveLast
ForiegnFld = regDb!temptsrid
regDb.Close

Does anyone see what I am doing wrong?
Thanks!
Shane

My full code is below with the ADO code in bold.

Public Function GetAllTSR()
Dim session As Object   'notes session
Dim db As Object        'notes database
Dim view As Object      'notes view
Dim doc As Object       'notes document
Dim NameCtr, EntryNo As Integer 'integers for loop control
Dim ViewStr As String   'string for notes view
Dim TestStr As String   'string to test for last record in document
Dim sSQL As String      'SQL statement for append
Dim FieldValue As Variant   'notes field value
Dim NFNS, NFND As Variant   'arrays for notes field names
Dim NFS(28), NFD(10) As Variant 'arrays for notes field values
Dim ForiegnFld As Long
Dim regDb As ADODB.Recordset

'This array contains the Notes field names for each TSR
NFNS = Array("TSRNumber", "Form", "StandardAdditive", _
    "A01", "A01Level", "A02", "A02Level", "Acid", _
    "AcidLevel", "Mixing", "Compounding", "MeshScreen", _
    "Zone1F", "Zone2F", "Zone3F", "DieZoneF", _
    "ScrewSpeed", "Molding", "BarrelTemp1C", _
    "BarrelTemp2C", "DieTempC", "Cancel", "CloseDate", _
    "CustomerName", "LabWorkComplete", "EnteredBy", _
    "customertype", "DateEntered", "CommunicationsAttachments")
    
'check to see that Notes is running
'--see Email Module for function code
If fIsAppRunning = False Then
    MsgBox "Lotus Notes is not running" & Chr$(10) _
        & "Make sure Lotus Notes is running and you " _
        & "have logged on."
    Exit Function
End If

'set the Notes enviroment
ViewStr = "3. R&D\By Period"
TSRRetrieval:
Set session = CreateObject("Notes.NotesSession")
Set db = session.GetDatabase("servername", _
    "filename")
Set view = db.GetView(ViewStr)
Set doc = view.GetFirstDocument
Do While Not (doc Is Nothing) 'Loop through each doc in view
    'get field values for each field name in NFNS array
    'these values are populating the NFS array
    For NameCtr = 0 To 28
        FieldValue = doc.GetItemValue(NFNS(NameCtr))
        NFS(NameCtr) = Chr(34) & StripQuotes(FieldValue(0)) _
            & Chr(34)
    Next NameCtr
    'send TSRNumber to log table and append data to zTempTSRData
    DoCmd.RunSQL "Insert Into zTSRLog (TSRNo) Values (" _
        & NFS(0) & ")"
    sSQL = "INSERT INTO zTempTSRData (TSRNo, FormType, " _
            & "StandardAdditive, Anti1, Anti1load, Anti2, " _
            & "Anti2load, AcidScav, AcidScavLoad, Mixing, " _
            & "Compounder, MeshScreen, CompZ1, CompZ2, " _
            & "CompZ3, CompDZ, CompRPM, Molder, BarrelTemp1, " _
            & "BarrelTemp2, DieTemp, Cancel, CloseDate, " _
            & "customername, LabWorkComplete, Enteredby, " _
            & "customertype, DateEntered, Attach) VALUES (" _
            & NFS(0) & ", " & NFS(1) & ", " & NFS(2) & ", " _
            & NFS(3) & ", " & NFS(4) & ", " & NFS(5) & ", " _
            & NFS(6) & ", " & NFS(7) & ", " & NFS(8) & ", " _
            & NFS(9) & ", " & NFS(10) & ", " & NFS(11) & ", " _
            & NFS(12) & ", " & NFS(13) & ", " & NFS(14) & ", " _
            & NFS(15) & ", " & NFS(16) & ", " & NFS(17) & ", " _
            & NFS(18) & ", " & NFS(19) & ", " & NFS(20) & ", " _
            & NFS(21) & ", " & NFS(22) & ", " & NFS(23) & ", " _
            & NFS(24) & ", " & NFS(25) & ", " & NFS(26) & ", " _
            & NFS(27) & ", " & NFS(28) & ")"
    DoCmd.RunSQL sSQL
    'get field values for each field name in R&D TSR form
    'EntryNo is used to step through each row in the form 1 to 25
    EntryNo = 1
    'loop to get each row of data in R&D TSR form
    Do Until EntryNo > 25
        NFND = Array("t" & EntryNo & "1", "t" & EntryNo _
            & "2", "t" & EntryNo & "3", "t" & EntryNo & "5", _
            "t" & EntryNo & "6", "t" & EntryNo & "7", "t" _
            & EntryNo & "8", "r" & EntryNo & "4", "r" _
            & EntryNo & "5", "r" & EntryNo & "7", "r" _
            & EntryNo & "8")
        TestStr = ""
        For NameCtr = 0 To 10
            FieldValue = doc.GetItemValue(NFND(NameCtr))
            NFD(NameCtr) = Chr(34) & StripQuotes(FieldValue(0)) _
                & Chr(34)
            TestStr = TestStr & FieldValue(0)
        Next NameCtr
        'open ADO recordset and get value for primary key in last record

Set regDb = New ADODB.Recordset
regDb.Open "Select TempTsrID From zTempTSRData", _
CurrentProject.Connection, adOpenDynamic
regDb.MoveLast
ForiegnFld = regDb!temptsrid
regDb.Close

        'SQL statement to append data to zTempTSRCompData table 
        'using values in NFD array
        sSQL = "INSERT INTO zTempTSRCompData (TSRNo, " _
            & "TempTSRID, compoundname, chemistname, " _
            & "notebookNo, MP, PpmInPlastic, MolderTemp, " _
            & "Thickness, ResinGrade, Haze, PeakTc, " _
            & "Comments) VALUES (" & NFS(0) & ", " _
            & ForiegnFld & ", " & NFD(0) & ", " & NFD(1) _
            & ", " & NFD(2) & ", " & NFD(3) & ", " & NFD(4) _
            & ", " & NFD(5) & ", " & NFD(6) & ", " & NFD(7) _
            & ", " & NFD(8) & ", " & NFD(9) & ", " & NFD(10) & ")"
        'put in a single entry if there are no rows in R&D Form
        If TestStr = "" And EntryNo = 1 Then
            DoCmd.RunSQL sSQL
            Exit Do
        'if records are in R&D form then exit loop when no more rows
        Else
            If TestStr = "" Then
                Exit Do
            End If
        End If
        'run sql to add record for each row of R&D data in TSR
        DoCmd.RunSQL sSQL
        EntryNo = EntryNo + 1
    Loop
'go to the next document in Notes
Set doc = view.GetNextDocument(doc)
Loop
'Change the view and rerun the function with the new view
If ViewStr = "3. R&D\By Period" Then
    ViewStr = "9. Archived\By TSR#"
    GoTo TSRRetrieval
End If
End Function

 

[DaltonVB]

Try getting the last number (this will be the biggest number for an autonumber field) by using DMAX. I may have spelled foreign differently than you did =)

ForeignFld = DMAX("TempTsrID","zTempTSRData")

Another way to do it would be to open two recordsets(one for each destination table) and plop the data into the tables that way.

i.e. (pseudo-code)
loop while notes has data
'if you have a new record for the first table
if UniqueIdentifier for main table<> LastIdentifier then
with rszTempTSRData
.AddNew
!Field1 = field1value
...
.UpdateBatch
LastIdentifier = UniqueIdentifier
end with
end if
'Add record for data that would make first table un-normalized
with zTempTSRCompData
.AddNew
!Field1 = field1value
...
.UpdateBatch
end with

enumerate your datasource
Loop

 

[swaybright]

Thank you!!! Oh, the beauty of a simple solution. Maybe I should learn to spell, too