requery datasheet when using stored proc

[qwertyjjj]

I have a datasheet which gets its data from a sql server stored procedure.
Using .requery doesn't seem to refresh the data.
Any ideas on how to do this?

Private Sub GetData(Optional parameterOrder As String)

On Error GoTo LocalHandler

    Dim strsql As String
    Dim db As Database
    Dim myquery As querydef
    
    Set db = CurrentDb()
    Set myquery = db.CreateQueryDef("")
    myquery.Connect = connection
    
    If IsLoaded("frmOutstandingTasks") Then
            varcc = Forms!frmOutstandingTasks!tempcc
    Else
            varcc = Forms!frmAdminScreen!tempccname
    End If
    
    parameterOrder = Me.Parent!optGroup_SortOrder.Value
    MsgBox (parameterOrder)
            
    If Len(parameterOrder) = 0 Then parameterOrder = 1 'account code
    
    If varcc = "All Controllers" Then
            myquery.SQL = "exec ccapp_LedgerAll 0, " & parameterOrder 'runs the stored proc
            myquery.ReturnsRecords = True
            Set rs = myquery.OpenRecordset()
            Set Me.Recordset = rs
    Else
            myquery.SQL = "exec ccapp_LedgerByController 0, '" & varcc & "', " & parameterOrder 'runs the stored proc
            myquery.ReturnsRecords = True
            Set rs = myquery.OpenRecordset()
            Set Me.Recordset = rs
    End If
    
    myquery.Close
    Set rs = Nothing
    Set db = Nothing
    Set myquery = Nothing

Exit Sub

 

[skyline666]

Hi,

I don't really know the answer as im fairly new to vba, and .requery usually does refresh everything, but how come you have defined strsql at the top but not used, and also used rs but not defined it (maybe you didn't post unnecessary code and strsql is in that, and rs is a global variable)?

Just a thought, as you use rs for openrecordset().

Andrew

 

[qwertyjjj]

but won't requery make it ru the same stored procedure?
I need to change a parameter and then get it to run again.

I took out strSQL and put i Dim rs as recordset