Advice concerning form building Access 2007

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

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
[/Code]

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
[/Code]

Reply | Quote

Tom,

If you enclose your code in code tags [noparse]

Code:

 Your Code Here 

[/noparse] it will preserve your indentation and make it easier for others to copy your code. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Thanks. That looks better.
How do I add a blank line in the procedure?

Reply | Quote

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. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Hi RG,

I tried all kinds of things….simply hitting , adding some blanks spaces and then hitting , 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.

Reply | Quote

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. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote