Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2005

    Unanswered: Form Recordset problem !

    Hy everybody,
    Next to my previous problem I have this one. Yesterday I change my recordsource to a recordset and since i'm unable to edit some fields on my form(See the picture) And I have this error when I try to edit a field : Update impossible for this recordset (Translate from french to english lol)

    Here is the recordset :
      Public Sub RechAss(ByVal Critere As String)
      Dim rs As ADODB.Recordset
      Dim cmdUR As ADODB.Command
      Dim param As ADODB.Parameter
         On Error GoTo HandleErr
         Set cmdUR = New ADODB.Command
         Set param = New ADODB.Parameter
      	With cmdUR
      	   Set param = .CreateParameter("@Critere", adVarChar, adParamInput, 3500, Nz(Critere, 0))
      	   .Parameters.Append param
      	   .ActiveConnection = CurrentProject.Connection
      	   .CommandText = "spAss"
      	   .CommandType = adCmdStoredProc
      	   Set rs = .Execute
      	End With
         'Me.RecordSource = "EXEC dbo.spAss @Critere = """ & Critere & """"
      	Set Me.Recordset = rs
      	If Me.Recordset.Clone.RecordCount = 0 Then
      	   cmbModif.Enabled = False
      	   cmbSupprimer.Enabled = False
      	   cmbNouveau.Enabled = m_bCanAdd
      	   cmbModif.Enabled = m_bCanModify
      	   cmbSupprimer.Enabled = m_bCanDelete
      	End If
         mnuOpe.Enabled = CBool(Nz(Me.IdAss, 0)) And m_bCanModify
         mnuImprimer.Enabled = mnuOpe.Enabled
         SetSubForms True
         On Error Resume Next
         Set param = Nothing
         Set cmdUR = Nothing
         Set rs = Nothing
         Exit Sub
         Select Case Err.Number
      	  Case Else
     		 MsgBox "Error " & Err.Number & ": " & Err.Description, vbCritical, "Form_frmAss.RechAss" 'ErrorHandler:$$N=Form_frmAss.RechAss
         End Select
         Resume ExitHere
      ' End Error handling block.
      End Sub
    Last edited by shelbygt22; 10-28-05 at 14:56.
    The solution is probably in your face
    but you dont see it !

  2. #2
    Join Date
    Jan 2005
    You're using an ADO Command object to retrieve a recordset from a Stored Procedure.

    1) What version of ADO are you using?

    2) What Database is this pulling from? (Given "EXEC dbo.spAss @Critere = """ & Critere & """", I am assuming SQL Server . . .)

    3) What is the code in the Stored Procedure?

    Note: MS Access 2000 Help has this to say about the Recordset Property of a Form and ADO:
    Recordset type     Based on SQL data     Based on Jet data 
    ADO                Read/Write (1)        Read Only 
    DAO                N/A                   Read/Write 
    (1) Note   The ADO Recordset.CursorLocation property must be set to
    adUseClient. The UniqueTable property is available in client/server on the
    property sheet, but not for Access databases (.mdb). This property must be
    set in Visual Basic code.
    Global rstSuppliers As ADODB.Recordset
    Sub MakeRW()
        DoCmd.OpenForm "Suppliers"
        Set rstSuppliers = New ADODB.Recordset
        rstSuppliers.CursorLocation = adUseClient
        rstSuppliers.Open "Select * From Suppliers", CurrentProject.Connection,
    adOpenKeyset, adLockOptimistic
        Set Forms("Suppliers").Recordset = rstSuppliers
        Forms("Suppliers").UniqueTable = "Suppliers"
    End Sub
    See also the UniqueTable Property of a Form.

  3. #3
    Join Date
    Apr 2005
    Here is my stored proc :
     CREATE Procedure spAss
     	@Critere NVARCHAR(3500) = ''	--1000
     	DECLARE @SQLString NVARCHAR(4000) --1500
     	SET @SQLString = 
     			'SELECT a.*, i.NoInventaire, i.Description, i.CoutUnitaire, i.IdTypeCategories, i.Localisation, ' +
     		    'i.NoDossierCSA, i.NoDossierUL, i.NoClassTarif, i.CritPref, i.Producteur, i.CoutNet, i.PaysOrgine ' +
     			'FROM dbo.Ass a ' +
     			'INNER JOIN dbo.Inve i ON a.IdAss = i.IdInve '
     	IF LEN(@Critere) > 0 
     		SET @SQLString = @SQLString + ' WHERE a.IdAss IN (' + @Critere + ') ORDER BY i.NoInventaire'
     		SET @SQLString = @SQLString + ' ORDER BY i.NoInventaire'
     	EXECUTE sp_executesql @SQLString
    The solution is probably in your face
    but you dont see it !

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts