January 15, 2009 at 12:32 pm
I have an adp with a form that uses a simple PROC with a passed parameter as it's data source. On the form there are some buttons that different sorts using the forms sort property. In Access 2000 this worked perfectly well.
If it is run on Access XP or Access 2003 it fails. There is no error it is just that as soon as any filter or sort is applied then data is returned although the underlying PROC is returning a data set. It is annoying that Microsoft have obviously broken this feature between 2000 and 2002/3.
I could rewrite the PROC to use more parameters and Dynamic SQL, or just hard code the order and sort query in the client, but I was wondering if there is a simple fix for this.
January 15, 2009 at 12:36 pm
I guess I'm not clear on what's happening with your form. You are setting the sort-by property of the form, and it's not sorting? Or something else?
I built and maintained an Access 2003 app on an SQL database for several years, and I definitely used that functionality successfully, so it can be done. But my memory of the exact details is a bit rusty, so you'll need to tell me a bit more about what you're doing and how you're doing it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 15, 2009 at 12:55 pm
To Clarify, I have a PROC on the SQL Server (I could attach the text if necessary).
It has 2 parameters passed to it, and works just fine when the form is loaded, showing all data as required.
The sort code is triggered from an option group fraWhich is shown below.
Select Case fraWhich
Case 1
strSort = "crs_CoreID,crs_CourseID,Delname"
strSortA = "crs_CoreID,crs_CourseID,Delname"
Case 2
strSort = "crs_CourseDate,Delname,crs_coreID,crs_CourseID"
strSortA = "crs_CourseDate,Delname,crs_coreID,crs_CourseID"
Case 3
strSort = "Delname,crs_CourseDate,crs_coreID,crs_CourseID"
strSortA = "Delname,crs_CourseDate,crs_coreID,crs_CourseID"
End Select
'Now apply this to the subforms
sfmOne.Form.OrderBy = strSort
sfmOne.Form.OrderByOn = True
sfmO.Form.OrderBy = strSortA
sfmO.Form.OrderByOn = True
When the .adp is run from Access 2000 the sort is applied correctly.
When the .adp is run in either XP or 2003, the code runs without error, but NO rows are returned to the subform.
The same issue also applies if instead of a sort, the forms Filter property and FilterOn property is used.
If however OrderByOn is set to False and No Filter is attempted then it works OK.
If that makes no sense, then let me know and I'll supply some more information.
AW
January 20, 2009 at 1:52 am
To add to this.
I tried re-writing the PROC so that it was Dynamic.
I passed all parameters for filtering and sorting etc to the PROC.
It then Creates on the FLY DYNAMIC SQL in a variable @SQL, which is finally executed in the PROC with EXEC(@SQL)
Guess what?
Worked brilliantly in 2000, but failed totally in 2002 and 2003.
Guess I am going to have to use Dynamix SQL on the fly from the Client.
If it ain't bust, don't fix it Microsoft!
January 21, 2009 at 11:00 am
Sorting works just fine for may adp in 2003, but I'm using a slightly different method.
DoCmd.GoToControl "txtCompanyName"
DoCmd.RunCommand acCmdSortDescending
Tim
Tim
January 21, 2009 at 11:57 am
Is your for based upon a Table, View or a Proc?
It is only procs that I appear to be getting issues with.
January 21, 2009 at 12:18 pm
Try something like this:
strSort = "[tablename].crs_CoreID,[tablename].crs_CourseID,[tablename].Delname"
January 22, 2009 at 1:37 am
Mine is based on a View
Tim
January 23, 2009 at 1:29 am
try this, hope it will solve the problem
Select Case fraWhich
Case 1
strSort = "'" & crs_CoreID,crs_CourseID,Delname & "'"
strSortA = "'" & crs_CoreID,crs_CourseID,Delname & "'"
Case 2
strSort = "'" & crs_CourseDate,Delname,crs_coreID,crs_CourseID & "'"
strSortA = "'" & crs_CourseDate,Delname,crs_coreID,crs_CourseID & "'"
Case 3
strSort = "'" & Delname,crs_CourseDate,crs_coreID,crs_CourseID & "'"
strSortA = "'" & Delname,crs_CourseDate,crs_coreID,crs_CourseID & "'"
End Select
January 23, 2009 at 4:44 am
Sorry, that doesn't make sense.
strSort = "'" & crs_CoreID,crs_CourseID,Delname & "'"
Does not even pass the line Syntax check.
strSort = "'" & "crs_CoreID,crs_CourseID,Delname" & "'"
Might be what you meant, but that certainly does not work in ANY version of Access.
I also tried putting the Table name as a prefix to the field but that made no difference either. It is definitely a feature!
I'm now off to code the query script on the client so it can be passed to the server rather than using a proc.
Shame because I would rather have done it via a proc and passed parameters.
January 23, 2009 at 5:19 am
After you apply the sort you have to run the form's requery method to get the sort to show:
Me.Requery
January 23, 2009 at 7:01 am
Yep, did that too before I posted the original message.
However, the filter sort does NOT have to have a requery applied to it to re-populate the subform in this case, and it works fine without the requery.
It is true that a requery is essential when repopulating a combo or list box though.
With OR without a requery, the problem I have is that although the underlying PROC returns data , the recordset of the form has NO data when run under Access XP or Access 2003, but it is fine when run under Access 2000.
Not a problem I have now re-written the Code from the PROC as on the fly SQL in the adp and it works fine. Just does not work when the recordsource of the subform is a PROC.
Might be a feature of XP or it might just be the complexity of the PROC that Access cannot handle the overlay of a filter/sort.
January 23, 2009 at 9:52 am
I am intrigued by this thread, having worked with Access front-ends for some time. Would you mind posting the stored procedure code?
Rob
January 23, 2009 at 10:25 am
There are two different versions of the PROC, both which work just fine when running against the passed parameters with or without access filters.sorts in Access 2000.
In Access 2003, the first fails when a filter or sort is applied in Access as shown ealier in thread.
The second which is dynamic SQL, works in 2000 with all passed parameters, but fails again in XP/2003.
If the identical SQL that is generated by the second proc, is built in ACCESS and the SQL script passed to server rather than a call to the Proc, it works.
The PROCS that I was using are attached.
I have now fixed the issue, with an alternative method, so working out the cause of the problem is now just an academic exercise, but still like to know if it is me, OR the changes bewtween Access 2000 and XP/2003.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply