Change the name of the field "Update" to "UpDt" and put all the field names in the SQL string in []. I used a reserved word for that field name so that may be the issue. Also, make sure the code matches your actual field names:

sSQLB = "SELECT TOP 1 [FKFieldName], [ServiceDate], [UpDt] FROM MachineService Where [FKFieldName] =" & iPK & " AND [UpDt] <> 0 Order BY ServiceDate DESC"

Is FKFieldName an actual name of a column in your table MachineService? It should be the look up column to the Primary Key in the table MachineParts.

mmcdonal

Look it up at: http://wrox.books24x7.com

I changed the Update field of UpDt and also changed FKFieldName to PartID which is the name of the primary key in MachineParts.

The code is now running, but it is not working.
It loops through all the records keeps the proper iPK count, but the code never enters the If atatement that actually updates the date.

I think the problem is in sSQLB because it never seems to pull in any records.

I am using the following code

Private Sub Form_Close()

    Dim rsA As ADODB.Recordset
    Dim rsB As ADODB.Recordset
    Dim sSQLA As String, sSQLB As String
    Dim iPK As Integer

    'Open a recordset on Table MachineParts first:
    sSQLA = "Select * FROM MachineParts"
    Set rsA = New ADODB.Recordset
    rsA.Open sSQLA, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

    rsA.MoveFirst
    'Take first PK
    Do Until rsA.EOF
        iPK = rsA("PartID")
        sSQLB = "SELECT TOP 1 [PartID], [ServiceDate], [UpDt] FROM [MachineService] Where [PartID] =" & iPK & " AND [UpDt] <> 0 Order BY [ServiceDate] DESC"
    Set rsB = New ADODB.Recordset
    rsB.Open sSQLB, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
        If rsB.RecordCount > 0 Then
            rsA("LastServiceDate") = rsB("ServiceDate")
            rsA.Update
        End If

    rsA.MoveNext
    Loop

    rsA.Close

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "qryUPDATE_MachineService_SetUpdate_0_All"
    DoCmd.SetWarnings True

End Sub


I have a rough idea how to read sSQLB and what it means, but have no way of knowing if it is coded right for my application.

The name of the primary key for table MachineParts is "PartID" One to
The name of the primary key for table MachineService is "ServiceID" Many

Thanks for all your help, you have been Great

Can you create a query in the local query designer that does what you want, and then review the code and see what needs to be changed in sSQLB?

The way the looping works is that it first opens all the records in TableA, goes to the first one, takes its primary key, and then opens all the records in TableB that have that primary key as a foreign key in the proper field, that have a servicedate, and that are checked for Update. IF it finds one of those records, it should only take the latest service date, and updates the proper field in TableA.

There should be many times that it will scroll through all of TableA, an just find one record that needs updating from Table B.

A better way to do this is to create a query in the local designer that gathers all the FKs where Update = Yes, and ServiceDate <> "", and then use 3 recordsets. The first recordset would be to open Table A for updates, then open the outer loop on the query that displays all the unique FKs of TableB that have the conditions you want. Then open the 3rd recordset on TableB where the FK is in the record, and not worry about the other conditions since they were alreay met, just open Top 1. Then copy the Service Date over. THis would keep the transaction overhead down.

Anyway, see if te fix works, and if it takes too long to close the form each time, (I didn't ask how many records we are dealing with) then we can recast the code to run more quickly.


mmcdonal

Look it up at: http://wrox.books24x7.com

Thanks for all your help. I wrote a query to take care of the update right now it works great, but there are not that many records in the table. I think it will be OK though.

Thanks for everything