Results 1 to 2 of 2
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Filtering form based on PROC fails in 2003 (Access 2003 and XP)

    I am using an Access .adp project that is mainly running on Access 2000 but can be run on Access 2003 sometimes.
    The problem is with sorting and filtering when applied to a form based on a PROC as the record source,

    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_CourseI D"
    strSortA = "crs_CourseDate,Delname,crs_coreID,crs_CourseI D"
    Case 3
    strSort = "Delname,crs_CourseDate,crs_coreID,crs_CourseI D"
    strSortA = "Delname,crs_CourseDate,crs_coreID,crs_CourseI D"
    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 and the subform populated with data
    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.

    Is there anyway round this other than writing the PROC to accept more parameters, or building the SQL Query on the fly at the level of the form, and then chaging the subforms recordsource to the new query.
    I wanted to run it from the PROC, because it is easier to m aintain the queries as much as possible on the server rather than have to make changes to the Access FE.
    Andrew

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Filtering form based on PROC fails in 2003 (Access 2003 and XP)

    To follow up my own post.
    I tried to re-write the PROC using Dynamic SQL passing all parameters to the proc.This creates a final SQL statement in a variable @SQL in the proc, which is executed with EXEC(@SQL) in the proc.

    Yep, works like a dream in Access 2000, but fails totally in Access XP and 2003.

    What the heck did Microsoft do between the two versions?

    I also tried to use an ADO recordset as the recordsource of the form rather than a direct call to the Proc, setting it on Open and against assorted buttons and Option Buttons.

    A bit of success here, but all the fields show #Name, yet the ADO returns the recordset with the correct number of rows, so NOT sure what is going on there, but working on it.

    Sadly, it looks like I have to create the SQL in the client and then pass the SQL string from there to the Server, rather than using a stored PROC with passed parameters.

    I prefer walking in the Mountains!
    Andrew

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •