Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004

    Unanswered: Multi-selection w/ Continuous Forms (you know, like a listbox)

    My Desire
    I want to display a multi-select list of orders to my users so that they can perform actions on multiple (non-contiguous) orders at once (i.e. print or ship).

    Option 1: A List Box
    I am well on my way to mastering the art of the multi-column listbox . A great little control that I discovered after reading the Access Cookbook 2nd (great book!). And this is working great as it is VERY easy to iterate through the .itemsSelected property of the listbox to perform actions on each...item selected.

    My Desire (Cont.)
    However...I want to be able to sort/filter the columns of the listbox without reinventing the wheel. (i.e. without having to write dynamic sql or having to create buttons or controls to filter each column as there are 17 columns in my listbox)

    Option 2: A Continuous Form
    I keep reading that using continuous forms is an excellent way to replicate all the benefits of a listbox while gaining the benefits of adding buttons and being able to format each field and you also get all the sorting and filtering features of Access. I agree, LET's GO!

    But wait! There is one thing missing for me, the idea of being able to select multiple (non-contiguous) items. I suppose I could add a checkbox so that they could be selected that way, but then

    How can I still capture the order number each row that has been selected to then perform actions on them when using a continous form like you can with the .ItemsSelected property of a list box?

  2. #2
    Join Date
    Apr 2004
    outside the rim
    Choose your poison:

    If you want to continue to use list boxes, as they are one of the most useful controls access has, I must ask:

    How are you setting up the RowSource (is it Table or Values)?

    If you are using a Table, then sorting is as easy as adding
    ORDER BY [mySortField] Asc
    to the end of the SQL string access puts in the Rowsource property. You can accomplish this on the fly by:
    Dim strSQL As String
    Dim blSort As Boolean
    strSQL = UCase$(Me.List1.Rowsource)
    iC = InStr(0,strSQL,"ORDER BY") ' see if the ORDER BY clause is in the value already
    If iC > 0 Then ' there's already a sort
         strSQL = Left$(Me.List1.Rowsource,iC - 1)
         strSQL = Me.List.Rowsource
    End If
    strSQL = strSQL & " ORDER BY [" & strField & "] Asc"
         ' Multiple sorts can be used by adding fields, seperated by commas
         ' They get applied from left to right
         ' Asc is Ascending, is not required and assumed if it's not used
         ' Desc is Descending
    Me.List1.Rowsource = strSQL
    Me.List1.Requery ' this will apply the new sort, but will unselect anything selected
    To experiment how different sorts look in SQL, create a query and set it up the way you want it, then go into SQL view. That a nice little trick to learning to basics of SQL AND creating giant SQL strings without losing your mind.

    If you are using a Value List, you may want to consider using a Temp Table (setting up the Listbox from a Table is a whole lot easier) and then use the method shown above.

    If you want to use a continuous form (probably preferable if I understand your requirements) then add a check box (Yes/No in the table) for selected. If you want to be fancy, put a filled (non-transparent) box behind all the controls and place an afterupdate for the box and each control as follows:
    Me.Check1 = Iif(Me.Check1, False, True)
    This will reverse the state of the check box upon clicking and where in the record.

    Sorting the records of the continuous form is much easier - use the OrderBy and OrderByOn properties (see the Access help file).

    Have fun and good luck!

    PS: I wrote this in here with out checking it in a program environment - I apologize if there's any typos!

  3. #3
    Join Date
    Feb 2004
    First off, thank you for your time to help me with this!

    The RecordSource for the listbox is a parameterized stored procedure. SPs are by far the easiest things to use as a record source because all that you have to do is place a control on the form with the same name as each of the parameters and then requery the recordsource after any of the controls are updated. There is no code involved ! The only issue with stored procedures is that you can't use them as a "table" in another query as easily.

    I think, to save my sanity, since the query is rather complex, I will make it a function, and then just change the dynamic SQL to be

    strSQL = "SELECT * " & _
    strSQL = strSQL + "FROM dbo.recordsourcefunction(@Parameter1,@Parameter2) " & _
    strSQL = strSQL + "ORDER BY " + strOrderByString
    If you want to use a continuous form (probably preferable if I understand your requirements) then add a check box (Yes/No in the table) for selected. If you want to be fancy, put a filled (non-transparent) box behind all the controls and place an afterupdate for the box and each control as follows:
    That is such a cool idea!! I am definitely going to use that at some point, it pretty much replicates the multi-select properties of the listbox. AND if I change the background color from black to white it even looks like a listbox.

    One last question:
    So, I figure the easiest way to add the checkbox functionality is to make it a bound field to a boolean column....uh oh, there is no way to do the whole checkbox thing without a Make Table query is there? UNLESS would it be better to use an array of order numbers and their checkboxes OR and ado recordset?...

    If I create an ADO recordset from, say, a SELECT query, am I able to add a boolean column to this recordset that I can change the value of based on whether or not the checkboxes are checked? This would be to avoid having to create temp tables and all that nonsense.

    Anyway, you have been unbelievably helpful and definitely pointed me in the right direction!!

Posting Permissions

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