Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Recordset not updating as expected

Posted on 2011-10-18
19
Medium Priority
?
233 Views
Last Modified: 2012-06-27
I have 2 records in a table I am trying to update. If I step through the code, both records update as expected, but if I run the code off of the command button, only one updates.

    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
0
Comment
Question by:smm6809
19 Comments
 
LVL 40

Expert Comment

by:als315
ID: 36988071
Try add .Edit in the beginning of cycle
                .Edit
                !numCompanyID = numCompany
                !strXRef = Me.txtCostCtr
                .Update
0
 
LVL 12

Expert Comment

by:danishani
ID: 36988075
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
0
 

Author Comment

by:smm6809
ID: 36988125
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.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36988188
using ADODB.Recordset, you don't need the .EDIT

how is the numCompany get its value ?

does the field "numCompanyID" allow duplicates ?



0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36988209
are you updating a Local table or linked table?
 if linked table, is it an access database or sql ?
0
 

Author Comment

by:smm6809
ID: 36988228
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.
0
 

Author Comment

by:smm6809
ID: 36988262
Local table
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36988264

can you post the rest of your codes.
0
 

Author Comment

by:smm6809
ID: 36988310

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
0
 

Author Comment

by:smm6809
ID: 36988396
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?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36988421
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" ?
0
 

Author Comment

by:smm6809
ID: 36988436
I delete it because I am using it as a temp table. Long story... Did you get my last message about figuring it out?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36988441
<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

Open 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..

0
 

Author Comment

by:smm6809
ID: 36988503
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
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 36988544
try placing this code as the first line in the cmdOK click event


Private Sub cmdOK_Click()

Forms!frmActive.subLineItems.Form.Dirty=false   '< insert this line
0
 

Author Comment

by:smm6809
ID: 36988609
Not helping. Sorry
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36988615
can you upload a copy of the db
0
 

Author Comment

by:smm6809
ID: 36988629
I wish I could...the data is classified. I'd have to kill you :)
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36988651
upload a copy of the db with dummy records.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have had my own IT business for a very long time. I started mostly with hardware and after about a year started to notice a common theme. I had shelves with software boxes -- Peachtree, Quicken, Sage, Ouickbooks -- and yet most of my clients were…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

577 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question