Recordset not updating as expected

Try add .Edit in the beginning of cycle
                .Edit
                !numCompanyID = numCompany
                !strXRef = Me.txtCostCtr
                .Update

Which one is updated and which one not?

You have to give us some more info in regards on how numCompany is defined and if your Commandbutton is placed on the same Form as your field txtCostCtr.

Hope this helps,
Daniel

Second record is not being updated. numCompany is an integer. Button is on the same form as the text box... als315....edit is not recognized. Hope this helps
Keep in mind, if I step through the code, both records are updated. I also tried an SQL update statement with conn.execute strSQL. That didn't work either.

using ADODB.Recordset, you don't need the .EDIT

how is the numCompany get its value ?

does the field "numCompanyID" allow duplicates ?

are you updating a Local table or linked table?
 if linked table, is it an access database or sql ?

numCompany reads a table to get its value. I have stepped through the code and I know it's the right value. numCompanyID in the table allows duplicates. There are no constraints on the table as I am basically using it as a temp table.

Local table

can you post the rest of your codes.

This is where I get numCompany:
strSQL = "Select * " _
    & "    from tblCompany " _
    & "   where numworksheetID= " & numWksID & "" _
    & "     and strCompany='" & Me.cboCompany & "'"
   
Set rs = New ADODB.Recordset
rs.Open strSQL, mConn
numCompany = rs!numCompanyID
rs.Close
Set rs = Nothing
 

Private Sub cmdOK_Click()

    strSQL = "Select * " _
        & "     From tblActiveCorpSub"

    Set rs = New ADODB.Recordset
    rs.Open strSQL, mConn, adOpenDynamic, adLockOptimistic

    With rs
            .MoveFirst
            Do Until .EOF
                !numCompanyID = numCompany
                !strXRef = Me.txtCostCtr
                .Update
            .MoveNext
            Loop
         .Close
    End With
    Set rs = Nothing
   
    strSQL = "INSERT INTO tblActiveCorp ( numCompanyID, strPlanExt, strPlanCD, strGroupExt, strXRef )" _
        & "        SELECT tblActiveCorpSub.numCompanyID, tblActiveCorpSub.strPlanExt, " _
        & "               tblActiveCorpSub.strPlanCD, tblActiveCorpSub.strGroupExt, tblActiveCorpSub.strXRef " _
        & "          FROM tblActiveCorpSub;"

    mConn.Execute strSQL
   
   
    strSQL = "DELETE tblActiveCorpSub.* " _
        & "     FROM tblActiveCorpSub;"
    mConn.Execute strSQL
   
   
    Forms!frmActive.subLineItems.Form.RecordSource = Forms!frmActive.subLineItems.Form.RecordSource
    DoEvents
    Forms!frmActive.subLineItems.Requery
    Forms!frmActive.subLineItems.Visible = True

    Me.Visible = False




End Sub

I figured it out Capricorn, but I need your help. I am getting the data from a subform. And the last field is defaulted as NA because it is not always needed and if I don't put something in that field I have other issues down the road. So the situation is that I am not stepping off of the last record before I click on the OK button. Make sense? So how can I make sure I get that record for updating?

why do you need to delete the contents of table "tblActiveCorpSub"


strSQL = "DELETE tblActiveCorpSub.* " _
        & "     FROM tblActiveCorpSub;"
    mConn.Execute strSQL



also, which table are you looking at, that you said "is not updating the second record" ?

I delete it because I am using it as a temp table. Long story... Did you get my last message about figuring it out?

<So the situation is that I am not stepping off of the last record before I click on the OK button. Make sense? So how can I make sure I get that record for updating?>

is this the part that you are referring to ?

This is where I get numCompany:
strSQL = "Select * " _
    & "    from tblCompany " _
    & "   where numworksheetID= " & numWksID & "" _
    & "     and strCompany='" & Me.cboCompany & "'"
   
Set rs = New ADODB.Recordset
rs.Open strSQL, mConn
numCompany = rs!numCompanyID
rs.Close
Set rs = Nothing

Select allOpen in new window

the recordset of the codes above could return more than one record .

can you explain what exactly do you want to happen..

Sorry, Ok, I have a subform that I am entering data into. The subform recordsource is tblActiveCorpSub and it is continuous forms. If I enter data into the fields but dont hit the tab key to go to the next record, my table doesn't update. However, if I step off of that record, it is captured in the table and updates no problem. So what I think I need is to make sure that the user always steps off of that record before my routine runs. Maybe something like on lost focus or something. Does that make sense?
Doc1.doc

Not helping. Sorry

can you upload a copy of the db

I wish I could...the data is classified. I'd have to kill you :)

upload a copy of the db with dummy records.