Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    330
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Advice concerning form building Access 2007

    I want to build a form that works in the same way as a table in design mode, that is, being able to drag and drop rows up or down the list.

    The use is to create a list of jobs allocated to a person and be able to quickly resort them as needed as new jobs come in and priorities change.

    An idea I have is to use a listbox, highlight a line and then use up/down arrows to move it, by reading the line and its index number, delete the line from the box and then reinsert it by adjusting the index number but I think it will only allow steps up or down of 1, which could become tedious.

    Any ideas to improve upon this (including instructions) would be greatly appreciated.

    My programming skills aren't that good and most of what I have done lately is only small mods to the database I built mainly with Access 97 (some years back).

    Thanks to all in advance.
    "Heading for the deep end"

  2. #2
    New Lounger
    Join Date
    Jun 2012
    Posts
    11
    Thanks
    1
    Thanked 1 Time in 1 Post
    I've done this sort of thing a lot. I have a standard, useful design that works, but it's not the only choice.

    Basically I use two list boxes. On the left is the list of all choices available. On the right is the list of choices selected (such as job roles or tasks). The navigation is this: double-click on an item in the left side box, and that copies it to the right-side box. Double-click in the right-side box, and that removes it from the list. You can write code to the double-click event for each list box.

    To make it work best, both list boxes should be populated from tables. The left-side list box may well come from a Master Lists table. Don't even think about using value lists!

    Now, as for the sorting and prioritizing, that's a separate function. I'd put it in another screen, with a numeric field for priority that you change by hand as needed; and sort on that column. If you want you can add functionality for auto-assigning priority values, but in my experience manual works better.

  3. #3
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    You can use a Continuous form, with some fairly simple VBA code, which allows you to sort the form by clicking on the appropriate column label. Here is an example, for a label named lblSubject, with caption = Subject. The field is named SubjectTitle. You use the click event of the label.
    Code:
    Option Compare Database
    Option Explicit
       
    Private Sub lblSubject_Click()
    On Error GoTo ProcError
       
      Static blnOrderDesc As Boolean
        
      Call UnboldLabels
         
      If blnOrderDesc = 0 Then
         Me.OrderBy = "SubjectTitle"
         blnOrderDesc = -1
      Else
         Me.OrderBy = "SubjectTitle Desc"
         blnOrderDesc = 0
      End If
        
      Me.lblSubject.ForeColor = 128
      Me.lblSubject.FontBold = True
        
    ExitProc:
      Exit Sub
    ProcError:
      MsgBox "Error " & Err.Number & ": " & Err.Description, _
              vbCritical, "Error in procedure lblSubject_Click..."
      Resume ExitProc
    End Sub
    Add similar click event procedures for all fields that you wish to allow the user to sort. Here is the code for a procedure that is called from each lblName_Click procedure:
    Code:
    Public Sub UnboldLabels()
    On Error GoTo ProcError
        
    Dim ctl As Control
        
      For Each ctl In Me.Controls
         If ctl.ControlType = acLabel Then
            ctl.ForeColor = 0
            ctl.FontBold = False
         End If
      Next ctl
         
    ExitProc:
      Exit Sub
    ProcError:
      MsgBox "Error " & Err.Number & ": " & Err.Description, _
              vbCritical, "Error in procedure UnboldLabels..."
      Resume ExitProc
    End Sub
    
    Last edited by tgw7078; 2012-06-09 at 21:31. Reason: Added [Code] and [/Code] tags, to add readability.
    Tom Wickerath
    Microsoft Access MVP
    4/1/2006 - 3/31/2012

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Tom,

    If you enclose your code in code tags [code] Your Code Here [/code] it will preserve your indentation and make it easier for others to copy your code.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    Thanks. That looks better.
    How do I add a blank line in the procedure?

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Tom,

    To add a blank line just hit Enter. What I do is put in the tags and then paste the code between them from the VBA window.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    Star Lounger tgw7078's Avatar
    Join Date
    Jul 2010
    Location
    Seattle, WA., USA
    Posts
    90
    Thanks
    1
    Thanked 12 Times in 12 Posts
    Hi RG,

    I tried all kinds of things....simply hitting <Enter>, adding some blanks spaces and then hitting <Enter>, and, as you've said to copy the code from a module and paste it in-between the [ Code ] and [ /Code ] tags. Nothing has worked for me.

  8. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Tom,

    Don't know why you are having the problem but I guess you could always resort to the comment character ( ' ) at the beginning of a line for a blank line in appearance.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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