PDA

View Full Version : Solved: Find record and edit field in database



WillyTheFish
10-30-2008, 02:18 AM
Hello everyone,

I won't get my code to work. I get an error message saying "Update or CancelUpdate without Edit or AddNew". But i'm using Edit?! I don't get it.

As you may see i'm trying to loop through my sheet to find a true If-statement and then search the database for the record matching Cells(lngRow, 1) and entering "1" in the Field name gstrBannedArticlesDB (string).


Public Sub gBanArticle()

Dim dbDatabase As dao.Database
Dim dbRecordSet As dao.Recordset
Dim lngRow As Long
Dim lngPos As Long

Set dbDatabase = OpenDatabase(gstrImportPath1)

lngRow = 2
Do
If frmMain.lstExportView.Selected(lngRow - 1) = True Then

Set dbRecordSet = bDatabase.OpenRecordset(gstrDatabaseTable, dbOpenDynaset)
With dbRecordSet
.Edit
.FindFirst ("product_id = '" & Sheets(gstrFilename).Cells(lngRow, 1) & "'")
.Fields(gstrBannedArticlesDB) = "1" 'the error occurs here
.Update
End With

dbRecordSet.Close
Set dbRecordSet = Nothing
End If
lngRow = lngRow + 1
Loop Until Sheets(gstrFilename).Cells(lngRow, 1) = ""

dbDatabase.Close
Set dbDatabase = Nothing

End Sub


Thanks in advance :)

WillyTheFish
10-30-2008, 02:51 AM
just solved it myself with

With dbRecordSet
.MoveFirst

Do While Not .EOF
If Trim(dbRecordSet("product_id")) = Trim(Sheets(gstrFilename).Cells(lngRow, 1)) Then
.Edit
.Fields(gstrBannedArticlesDB) = "1"
.Update
Exit Do
End If
.MoveNext
Loop
End With

thanks anyway :)