Editing Recordset

[yessir]

I am trying to Edit a recordset on the fly using the following code. The problem is in the BOLD text in the code. It is supposed to set a Y/N field to true or yes (whatever) But it doesn't.

Can SOMEONE please HELP!
~

'ONLY 'ONLY do the following if there are records to work on
    If NumRec >= 1 Then

    'Cycle through each record in the tables record set
        While i <= NumRec
        'Go to the first record as each time there is a record appended from the top to the bottom
            '   And then the first record is deleted
            RS.MoveFirst

            'Look up the values in the tables on the form to ensure they are valid in the maintanence tables
            If IsNull(DLookup("ID", "tblMatrix", "Matrix = Forms!frmWater_Sample_Import.frmWater_Sample_Import_Temp.Form.Matrix.Value")) Then
                MsgBox "" & i & " > NULL > " & DLookup("ID", "tblMatrix", "Matrix = Forms!frmWater_Sample_Import.frmWater_Sample_Import_Temp.Form.Matrix.Value")

                If MsgBox("Would you like to ADD this NEW VALUE?", vbYesNo, "Value Not Valid") = vbYes Then
                    'Set warnings void
                    'Insert the new value into the table
                    DoCmd.SetWarnings False
                        DoCmd.RunSQL "INSERT INTO tblMatrix ( Matrix ) SELECT tblWater_Sample_Temp.Matrix FROM tblWater_Sample_Temp WHERE (((tblWater_Sample_Temp.Matrix)=Forms!frmWater_Sample_Import.frmWater_Sample_Import_Temp.Form.Matrix.Value));"
                    DoCmd.SetWarnings True

                    i = i + 1

                    'Appends the first entry of the table onto the end of the table
                    DoCmd.SetWarnings False
                        DoCmd.RunSQL "INSERT INTO tblWater_Sample_Temp ( ClientID, ProjectNo, LabID, Matrix, DuplicateOf, SampleDate, Analyte, Method, Result, Units, LOQ, Outlet_ID, Elevation, Unit_ID, DataSource, [Note], DL, FlowValue, FlowUnit_ID, Notes, Duplicate ) SELECT TOP 1 tblWater_Sample_Temp.ClientID, tblWater_Sample_Temp.ProjectNo, tblWater_Sample_Temp.LabID, tblWater_Sample_Temp.Matrix, tblWater_Sample_Temp.DuplicateOf, tblWater_Sample_Temp.SampleDate, tblWater_Sample_Temp.Analyte, tblWater_Sample_Temp.Method, tblWater_Sample_Temp.Result, tblWater_Sample_Temp.Units, tblWater_Sample_Temp.LOQ, tblWater_Sample_Temp.Outlet_ID, tblWater_Sample_Temp.Elevation, tblWater_Sample_Temp.Unit_ID, tblWater_Sample_Temp.DataSource, tblWater_Sample_Temp.Note, tblWater_Sample_Temp.DL, tblWater_Sample_Temp.FlowValue, tblWater_Sample_Temp.FlowUnit_ID, tblWater_Sample_Temp.Notes, tblWater_Sample_Temp.Duplicate FROM tblWater_Sample_Temp;"
                    DoCmd.SetWarnings True

                    'Deletes the first entry in the table as is now a dulicate
                    RS.Delete

                    'Refresh the subforms record source so the deleted values do not appear to the user
                    Me.frmWater_Sample_Import_Temp.Form.RecordSource = "SELECT * FROM tblWater_Sample_Temp;"

                Else

                    [B]With RS
                        .Edit
                        ![ERR_MATRIX] = True
                        .Update
                    End With[/B]
                    
                    i = i + 1

                    'Appends the first entry of the table onto the end of the table
                    DoCmd.SetWarnings False
                        DoCmd.RunSQL "INSERT INTO tblWater_Sample_Temp ( ClientID, ProjectNo, LabID, Matrix, DuplicateOf, SampleDate, Analyte, Method, Result, Units, LOQ, Outlet_ID, Elevation, Unit_ID, DataSource, [Note], DL, FlowValue, FlowUnit_ID, Notes, Duplicate ) SELECT TOP 1 tblWater_Sample_Temp.ClientID, tblWater_Sample_Temp.ProjectNo, tblWater_Sample_Temp.LabID, tblWater_Sample_Temp.Matrix, tblWater_Sample_Temp.DuplicateOf, tblWater_Sample_Temp.SampleDate, tblWater_Sample_Temp.Analyte, tblWater_Sample_Temp.Method, tblWater_Sample_Temp.Result, tblWater_Sample_Temp.Units, tblWater_Sample_Temp.LOQ, tblWater_Sample_Temp.Outlet_ID, tblWater_Sample_Temp.Elevation, tblWater_Sample_Temp.Unit_ID, tblWater_Sample_Temp.DataSource, tblWater_Sample_Temp.Note, tblWater_Sample_Temp.DL, tblWater_Sample_Temp.FlowValue, tblWater_Sample_Temp.FlowUnit_ID, tblWater_Sample_Temp.Notes, tblWater_Sample_Temp.Duplicate FROM tblWater_Sample_Temp;"
                    DoCmd.SetWarnings True

                    'Deletes the first entry in the table as is now a dulicate
                    RS.Delete

                    'Refresh the subforms record source so the deleted values do not appear to the user
                    Me.frmWater_Sample_Import_Temp.Form.RecordSource = "SELECT * FROM tblWater_Sample_Temp;"
                End If

            Else

                i = i + 1

                'Appends the first entry of the table onto the end of the table
                DoCmd.SetWarnings False
                    DoCmd.RunSQL "INSERT INTO tblWater_Sample_Temp ( ClientID, ProjectNo, LabID, Matrix, DuplicateOf, SampleDate, Analyte, Method, Result, Units, LOQ, Outlet_ID, Elevation, Unit_ID, DataSource, [Note], DL, FlowValue, FlowUnit_ID, Notes, Duplicate ) SELECT TOP 1 tblWater_Sample_Temp.ClientID, tblWater_Sample_Temp.ProjectNo, tblWater_Sample_Temp.LabID, tblWater_Sample_Temp.Matrix, tblWater_Sample_Temp.DuplicateOf, tblWater_Sample_Temp.SampleDate, tblWater_Sample_Temp.Analyte, tblWater_Sample_Temp.Method, tblWater_Sample_Temp.Result, tblWater_Sample_Temp.Units, tblWater_Sample_Temp.LOQ, tblWater_Sample_Temp.Outlet_ID, tblWater_Sample_Temp.Elevation, tblWater_Sample_Temp.Unit_ID, tblWater_Sample_Temp.DataSource, tblWater_Sample_Temp.Note, tblWater_Sample_Temp.DL, tblWater_Sample_Temp.FlowValue, tblWater_Sample_Temp.FlowUnit_ID, tblWater_Sample_Temp.Notes, tblWater_Sample_Temp.Duplicate FROM tblWater_Sample_Temp;"
                DoCmd.SetWarnings True

                'Deletes the first entry in the table as is now a dulicate
                RS.Delete

                'Refresh the subforms record source so the deleted values do not appear to the user
                Me.frmWater_Sample_Import_Temp.Form.RecordSource = "SELECT * FROM tblWater_Sample_Temp;"

            End If

        Wend

    End If

 

[pono1]

At a glance the bold code seems fine, assuming the "Err_Matrix" field exists in the table and its datatype is Yes/No...

But your INSERT INTO statement uses a TOP predicate without an ORDER BY clause and therefore can return any rec. Rather than using TOP in your append query, use a WHERE clause to get the rec, with the criteria being a unique field, like the primary key in the table (available through the recordset's current record).

Additionally, maybe I missed it but I didn't see an Err_Matrix field in the Append query. And you must be using the Append Query to verify if the DAO edit is working since you delete the edited record. You could type STOP in a line just below the Edit -- this will stop code execution -- and then manually check the table to see if the DAO cursor is updating the row then and there -- I don't know enough about DAO to know when the change occurs.

Sorry, no silver bullet but perhaps something to move you forward with the debugging.

Regards,
Tim