4 Replies Latest reply on May 12, 2011 2:01 PM by ernieB

    Run-time error '3251':

    ernieB
      Hi All,

      I'm getting an error with my code below.
      I'm trying to find out if any records exist, if they do, edit the record. if not insert new record. but i'm getting the error message:

      Run-time error '3251':
      Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

      My Code Below:
      {
      Sub StoreTarDscOrPrdIDNext()
      Dim conn As ADODB.Connection
      Dim prdDsc, prdID As ADODB.Recordset
      Dim strConn As String
      strConn = "Provider = Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & ThisWorkbook.Path & "\eps.mdb"
      Set conn = New ADODB.Connection
      conn.Open strConn
      Set prdDsc = conn.Execute("SELECT hs_cod FROM hs_log where prd_dsc = '" + LCase(txtDesc.value) + "'")
      Set prdID = conn.Execute("SELECT DISTINCT hs_cod FROM hs_log where prd_id = '" + LCase(cboProductID.value) + "'")

      'On Error GoTo ErrorHandler
      storTarDscPrd = Application.WorksheetFunction.VLookup("storTarDscPrd", Worksheets("config").Range("B:C"), 2)
      'If storTarDscPrd = "1" Then
      'store tariff and description
      If Not (prdDsc.BOF And prdDsc.EOF) Then 'There are no records if Beginning-Of-File and End-Of-File are both true.
      'if found Ask to Edit record
      EditTar = MsgBox(Prompt:="Edit record?", Buttons:=vbYesNo, Title:="ePrisoft")
      If EditTar = vbYes Then
      prdDsc("hs_cod") = Trim(cboTariffNo2.value)
      prdDsc.Update
      'Set prdDsc = conn.Execute("UPDATE hs_log SET hs_cod = '" + Trim(cboTariffNo2.value) + "' WHERE prd_dsc = '" + LCase(Trim(txtDesc.value)) + "'")
      End If
      MsgBox "Edit"
      Else
      'else insert new record
      prdDsc.AddNew
      prdDsc("prd_dsc") = LCase(Trim(txtDesc.value))
      prdDsc("hs_cod") = Trim(cboTariffNo2.value)
      prdDsc.Update
      'sql = "INSERT INTO hs_log (prd_dsc, hs_cod) VALUES '" + LCase(Trim(txtDesc.value)) + "','" + Trim(cboTariffNo2.value) + "'"
      'Set prdDsc = conn.Execute("INSERT INTO hs_log (prd_dsc, hs_cod) VALUES '" + LCase(Trim(txtDesc.value)) + "','" + Trim(cboTariffNo2.value) + "'")
      MsgBox "Insert"
      End If
      'ElseIf storTarDscPrd = "2" Then
      'store tariff and productid

      'Else
      'dont store tariff
      'End If
      'ErrorHandler:
      'Err.Clear
      'Resume Next
      prdDsc.Close
      Set prdDsc = Nothing
      prdID.Close
      Set prdID = Nothing
      conn.Close
      Set conn = Nothing
      End Sub
      }

      Any help would be appreciated

      Thanks