trying to refresh data

[qwertyjjj]

The data is brought in from a SQL server stored procedure through an ODBC connection (!).

I need to refresh the data but I'm not sure how. It doesn't seem to do anything at present:

Private Sub optGroup_SortOrder_AfterUpdate()

On Error GoTo LocalHandler

Dim optValue As Integer
Dim db As Database
Dim myquery As querydef
Set db = CurrentDb()
Set myquery = db.CreateQueryDef("")
myquery.Connect = connection

optValue = optGroup_SortOrder.Value
'MsgBox (optValue)
If IsLoaded("frmOutstandingTasks") Then
        varcc = Forms!frmOutstandingTasks!tempcc
Else
        varcc = Forms!frmAdminScreen!tempccname
End If

If optValue = 1 Then
'sort by account code

ElseIf optValue = 2 Then
  
    If varcc = "All Controllers" Then
            myquery.SQL = "exec ccapp_LedgerAllInvoiceOrder 0, 2" 'runs the stored proc '0 for the paid flag
            myquery.ReturnsRecords = True
            Set rs = myquery.OpenRecordset()
            'Set Me.Recordset = rs
            Set Forms!frmSubLedgerFull.Recordset = rs
    Else
            myquery.SQL = "exec ccapp_LedgerByControllerInvoiceOrder 0, '" & varcc & "', 2" 'runs the stored proc
            myquery.ReturnsRecords = True
            Set rs = myquery.OpenRecordset()
            'Set Me.Recordset = rs
            Forms!frmSubLedgerFull.Requery
            Set Forms!frmSubLedgerFull.Recordset = rs
            Forms!frmSubLedgerFull.Requery
    End If
    
ElseIf optValue = 3 Then

ElseIf optValue = 4 Then

ElseIf optValue = 5 Then

End If

Exit Sub

LocalHandler:
MessageBox.Show (Err.Description)

End Sub

 

[boblarson]

I think you're getting too complex for a sort order.

You can use this:

Me.OrderBy = "FieldName1, FieldName2 Desc, FieldName3" ' and so on
Me.OrderByOn = True

 

[qwertyjjj]

even when using a stored procedure?

 

[boblarson]

Give it a try. What have you got to lose? I haven't tried it directly with stored procedures, but I would think it would work.

 

[qwertyjjj]

doesn't recognise these
Me!frmOutstanding.OrderBy = "invoice_no"
Me!frmOutstanding.OrderByOn = True

or Me.frmOutstanding.OrderBy = "invoice_no"

?

 

[boblarson]

Do not use the BANG as it isn't a field, but a property you are setting:

Me.OrderBy = "invoice_no"
Me.OrderByOn = True

You don't put the form name in there with ME. It's EITHER OR. You can use:

Forms!frmOutstanding.OrderBy = "invoice_no"
Forms!frmOutstanding.OrderByOn = True

IF the form is not the one that the code is on. If the form is the same one that the code is on use the Me.OrderBy, etc.

 

[qwertyjjj]

Then I get the erro can't find form frmOutstanding !

If I put in Me. it lists frmOutstanding in there

I'm pretty sure that that's the subform name

 

[boblarson]

Oh, you didn't say that before. You MUST remember that SUBFORMS HAVE DIFFERENT SYNTAX!!!

Then, IF the subform container (the control that houses the subform on the main form) is the same name as the subform, you would use:

Me.frmOutstanding.Form.OrderBy = "invoice_no"
Me.frmOutstanding.Form.OrderByOn = True

exactly as shown.

 

[qwertyjjj]

Tried that as an example on that link you gave.

Then I get 3129 Reserved Error which originated in the AfterUpdate code

 

[boblarson]

Can you post your actual db? I think there may be more to this than meets the eye.

 

[qwertyjjj]

sadly not, it's 4Mb and the max upload is only 350k or something.

 

[boblarson]

If you run Tools > Database Utilities > Compact and Repair and then Zip it with WinZip, what does it wind up as? If it isn't too bad, you could email it to me. I can give you my email via PM. But first run the compact and then zip it to see what it will be.

 

[qwertyjjj]

try this cut down version

 

[boblarson]

What should I be looking at? Also, it already gave me a compile error because it couldn't find the sub: GetData

 

[qwertyjjj]

frmLedgerFull has the radio buttons

 

[boblarson]

frmLedgerFull has the radio buttons

I'm not going to be able to work with this cut down sample. It won't load any data due to missing items and without the data I can't see what is working and not working.

 

[qwertyjjj]

Does the code look correct though?
Have I closed off all recordsets in order to be able to rquery, etc.
The data is in a back end sql server so I'm not sure I could send it over with the db?

 

[boblarson]

Which line generates the Reserved error?

 

[qwertyjjj]

immediately after this line

Me.frmSubLedgerFull.Form.OrderBy = "invoice_no"

but it first goes to the subform's code and runs:

Private Sub Form_Current()
    Me.Parent!txtinvno = invoice_no
End Sub

then returns to the on error part of the option groups

Microsoft site says:
3129 error is
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

 

[boblarson]

Since I can't test this, try changing the current event to:

Private Sub Form_Current()
    Me.Parent.txtinvno = Me.invoice_no
End Sub