Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2005
    Posts
    89

    Unanswered: Form RecordSource problem

    Hi everyone,
    I have a problem with one of my form. I'm using Access with SQL stored proc. The problem is that I'm passing a string with more than 256 car.

    Here is the code:
    Code:
       Public Sub RechAss(ByVal Critere As String)
       
          On Error GoTo HandleErr
       
          modGestion.RAZ_Timout
        
          Me.RecordSource = "EXEC dbo.spAss @Critere = """ & Critere & """"
           
       	If Me.Recordset.Clone.RecordCount = 0 Then
       	   cmbModif.Enabled = False
       	   cmbSupprimer.Enabled = False
       	Else
       	   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
          
          UpdateStaticFields
          SetSubForms True
          
       ExitHere:
          On Error Resume Next
          'Cleanup
          Exit Sub
       
       HandleErr:
          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
          Resume
       ' End Error handling block.
       End Sub
    WHERE dbo.spAss =
    Code:
    CREATE Procedure spAss
      
      	@Critere NVARCHAR(3500) = ''	--1000
      
      
      As
      	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'
      	ELSE
      		SET @SQLString = @SQLString + ' ORDER BY i.NoInventaire'
      
      	EXECUTE sp_executesql @SQLString
      
      
      	return
      GO
    And string Critere =" 13519, 14663, 14653, 5784, 5785, 5787, 5788, 5791, 5792, 5793, 5794, 5795, 5796, 5797, 5798, 5799, 5800, 5801, 5802, 9200, 6586, 5803"

    Critere is a string where I put the Id of each assembly that I wanna see on my form. If I select more than 21 rec(assembly) I have this error
    Error 2757 [font=Tahoma]There was a problem accessing a property or method of the OLE object.


    So my question is: Is there a way to pass more than 256 car to a RecordSource Object.
    Last edited by shelbygt22; 10-27-05 at 12:38.
    Shelbygt22
    The solution is probably in your face
    but you dont see it !

  2. #2
    Join Date
    Apr 2005
    Posts
    89
    Thanks but I solve the problem by myself

    Here is the solution:
    Code:
     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
     
        modGestion.RAZ_Timout
     
        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
     	  
     	Set Me.Recordset = rs
     
     	If Me.Recordset.Clone.RecordCount = 0 Then
     	   cmbModif.Enabled = False
     	   cmbSupprimer.Enabled = False
     	Else
     	   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
        
        UpdateStaticFields
        SetSubForms True
        
     ExitHere:
        On Error Resume Next
        'Cleanup
        Set param = Nothing
        Set cmdUR = Nothing
        Set rs = Nothing
        
        Exit Sub
     
     HandleErr:
        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
        Resume
     ' End Error handling block.
     End Sub
    Shelbygt22
    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
  •