Sort Order from Subform

[David Eagar]

I have a subform based on a query - Can I change the sort order of the query by multiple fields from the the subform? To make it tougher, the data is locked down in read only mode, so the toolbar sort option is not available

Thanks for any tips

 

[Galaxiom]

Place the query sql in the Record Source peoperty of the subform.
Use VBA to change it as required.

 

[David Eagar]

VBA not my strong suit, can you give me some pointers?

 

[Galaxiom]

From VBA on the subform.
Me.Form.RecordSource = "SELECT etc etc"

Normally you would have a checkbox for the order by selections and build up the sql text as required in a variable. There are other variations to clean up the SQLtext.
Rushed Air code follows.
Note the trailing spaces in strings

Dim SQLtext As String
SQLtext = "SELECT fields FROM table WHERE whatever ORDER BY " 
 
'assuming checkboxes on mainform
IF Me.Parent.checkboxname Then: SQLtext = SQLtext & "fieldname, "
 
'Add more similar if statements for other fields
 
'Take off the last comma and space
If Right(SQLtext,2) = ", " Then: SQLtext = Left(SQLtext, Len(SQLtext)-2)
 
'Take off ORDER BY (and spaces) if none selected
If Right(SQLtext,10) = " ORDER BY " Then: SQLtext = Left(SQLtext,Len(SQltext)-10)
 
Me.Form.RecordSource = SQLtext
'(Since the Record Souce changes, the Requery should be automatic.)

 

[David Eagar]

I get message - Can't find the form specified in the sql statement? The only mention of form is Me!Form.RecordSource = SQLtext

 

[Galaxiom]

I get message - Can't find the form specified in the sql statement? The only mention of form is Me!Form.RecordSource = SQLtext

That is odd because there is no form specified in the SQL statement.
The sql you are directly referencing table or query fields rather than controls on the form.

The mention of the form is only at the end where the sql is applied to the Record Source of the form.

Can you post a sample of the database?
Just the tables and the forms related to this problem would probably do.

Or even just this VBA code?

 

[David Eagar]

Code is

Dim SQLtext As String
SQLtext = "SELECT [2tblSighting].SightingID, [2tblSighting].AnimalGroup, [2tblSighting].Species, [2tblSighting].Depth, [2tblSighting].Sighted, [2tblSighting].Method, [2tblSighting].Explorer, [2tblSighting].SightDate, [2tblSighting].Extinct, [2tblSighting].Notes, [2tblSighting].sl FROM 2tblSighting WHERE ((([2tblSighting].sl)=[Forms]![2frmSightings]![lk])); Order By [AnimalGroup]"
Me!Form.RecordSource = SQLtext

 

[Galaxiom]

Semicolon terminates the statement so anything after is ignored. (It is optional in Access anyway.) I expect the Order By would just be ignored so i don't think this is the real issue.

 WHERE ((([2tblSighting].sl)=[Forms]![2frmSightings]![lk]))[COLOR=red][B];[/B][/COLOR] Order By [AnimalGroup]"

However this may be the problem. I had a typo in my post.

Me!Form.RecordSource
should be:
Me.Form.RecordSource

This is because Form is a property of the form not a member of a collection.
I'll fix it in my post too, lest it lead someone astray later.

 

[David Eagar]

Thanks kindly, works perfectly!

 

[David Eagar]

Of course, what is the first thing ones does when something works perfectly? Yes, tamper with it

accdb (2007) attached.

Now want to use combination of filter combo boxes (blank = *) and sort functions. Works mostly, but depends what order you do things.

Use combo boxes first and then sort (the field Labels) all works well

If I sort first, combo boxes don't filter. I'm sure it is where I'm resetting variables, but most of this is new to me & I can't see solution

Any help much appreciated

 

[David Eagar]

Any takers? I'm going round in circles with this