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

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

even when using a stored procedure?

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.

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

or Me.frmOutstanding.OrderBy = "invoice_no"

?

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.

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

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.

Tried that as an example on that link you gave.

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

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

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

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.

try this cut down version

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

frmLedgerFull has the radio buttons

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.

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?

Which line generates the Reserved error?

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'.

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

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

nope, same error.
It's almost like it's expecting a SQL query but because all the data came from a stored procedure on the server it doesn't know what to order by

Well, I can try my own test here with a SQL installation. I'll try it and let you know how it goes.

I just thought of something. I don't know how to create a stored procedure on my SQL Server 2005 Express edition without any UI. I can do a linked table but that won't be the same.

Code goes:

CREATE PROCEDURE namehere

AS

SELECT fieldname, fieldname, fieldname
FROM tablename

GO

Code goes:

CREATE PROCEDURE namehere

AS

SELECT fieldname, fieldname, fieldname
FROM tablename

GO


See there's still a lot I don't even know. My ignorance of this subject will be displayed further when I say, "Where do I use that?" I know I can use the SQL Query Analyzer in the Enterprise Tools (at least when I worked with MSDE and SQL 2000).

Yep just type it into the query analyzer and run it.
After that if you need to change it you need to use ALTER PROCEDURE

That's the problem. I don't have access to query analyzer.

By the way are you using SQL 2000 or 2005?

SQL 2000.
Doesn't SQL express come with some sort of analyzer?
If it has enterprise manager then you can right click on stored procedures, goto new, and then put some sql code in.

No, it doesn't come with any management tools. That's a point of consternation as they give it to you but don't give you much of anything to work with it.