Get Error No update with edit. BUT am looking at both.

Question

Keep getting error 3020, NO Update with edit.  But there is.

Option Compare Database

Option Explicit

Private Sub cmmCreatePassword_Click()

'do DAO based on tbl, uses strings that want to make avail or not

'use one big string, that is combo of strings like "ABC" and "abc"

' only want to see if current char in CharIS is in the big string. DONT CARE WHERE.

' VALUE WILL BE > ZERO IF IN BIG STRING.

Dim MyPos As Long

Dim varSymbolsByVendor As Variant

Dim varCharInCharTable As Variant

varSymbolsByVendor = Me.SymbolsCanUse

Debug.Print "varSymbolsByVendor = " & varSymbolsByVendor

Dim dbsChars As DAO.Database

Dim rstChars As DAO.Recordset

Set dbsChars = CurrentDb

Set rstChars = dbsChars.OpenRecordset("tblChars", dbOpenTable)

With rstChars

    .Edit           'NOTICE EDIT HERE.

            Debug.Print .RecordCount

    .MoveFirst

    Do While Not .EOF

        Debug.Print "field charIS =  " & .Fields("CharIS") 'GETS CORRECT VALUE FROM FIELD.

     'want to check if char in current record is in the bigString, dont care where.

     'if yes, then set tblchar field charAvail to true.

    

     MyPos = InStr(1, varSymbolsByVendor, .Fields("CharIS"), vbBinaryCompare) ‘ works correctly

 

     'str1 is on form based on tblSpecs of what chars can use.   NEED TO TEST IF SYMBOLSyn=TRUE BEFORE TEST THIS WAY.

        'set charAvail to true

    

     If MyPos > 0 Then

    

     MsgBox " get here only if found in string."

     'MsgBox "prob with below, trying to set to True. ", vbCritical

     '                 none of the three below would work.

    '!Fields("avaibleYN") = True

   ' rstChars!avaibleYN = True

'    rstChars.Fields("avaibleYN") = True

‘Below show correct values for the table.

Debug.Print " field 0 " & rstChars.Fields(0).Name & "  " & rstChars.Fields(0).Value

Debug.Print "field 1 " & rstChars.Fields(1).Name; "   " & rstChars.Fields(1).Value

Debug.Print "--"

    Debug.Print "field 2 " & rstChars.Fields(2).Name & "  " & rstChars.Fields(2).Value

     Else

               MsgBox "Not found, MyPos NOT > 0    So Not setting value to true."

     End If

          '  https://msdn.microsoft.com/en-us/library/office/ff195231.aspx     tried this link and ONE below, no joy.

                 ' http://answers.microsoft.com/en-us/office/forum/office_2007-access/vba-do-while-loops-and-edit-data-question/80712bd5-9b57-e011-8dfc-68b599b31bf5

     .Update  ' when I click on cmmCreatePassword  STOPS ON THIS LINE. I DID REMOVED AND RETYPED THIS LINE. NO FIX.

                           ' this comment on line with "update" was NOT on line of code, just added this comment when I posted this.

     .MoveNext

    Loop

   

End With

rstChars.Close

dbsChars.Close

Set dbsChars = Nothing

End Sub

---

Mark J

Answer 1

The line

    .Edit           'NOTICE EDIT HERE.

is placed incorrectly. It should be below the line

   Do While Not .EOF

so that each record is placed in Edit mode as you loop through the records, not just the first one.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

Hans,

thanks for fast response.

what puzzles me, is I put   .Edit     just under the line    With rstChars   so I thought

if first line under  the start of the With block is  .Edit  And all the Do While ... Loop is within the With block  is after .Edit and Before  With End.

Is this one of those like, a  Do While ... Loop can raise a error code talking about a IF statement missing something.  really bugged me all that looking for a missing IF End. when I was missing part of a Do While...Loop.  Looked at lots of documentation to find the small print at end of document.

Thanks,

Mark J

---

Mark J

Answer 3

Placing .Edit above the line Do While Not .EOF is not a syntax error that will be flagged even before the code is run. But if .Edit is placed outside the loop, it is executed only once, for the first record. When you move to the second record, it will not be placed in Edit mode, to the .Update line will cause an error.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 4

With DAO you have to use .Edit with each row. If I remember right ADO lets you enter "edit mode" just one time and then do an .Update at the end, but don't hold me to that.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 5

Hans,

Thanks again.

This was very needed info for me.

This was info I didn't have before, and could not find in the reference sites I found.

Mark J

---

Mark J

Answer 6

Bill,

Thanks for the info.

I cannot remember ever seeing anything saying DAO  needs  and .Edit for each row.

That does explain why I got the results I did. Where worked and where didn't work.

Mark J 

---

Mark J

Answer 7

With DAO you have to use .Edit with each row. If I remember right ADO lets you enter "edit mode" just one time and then do an .Update at the end, but don't hold me to that.

When using ADO, you don't have to use .Edit at all. Simply assigning a value to a field places the record in edit mode. You still have to use .Update to save changes to the record, of course.

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 8

Hans,

In less than a minute it was working perfect.

Mark J

---

Mark J