Best way to: list sorted all groups and all items in each group?

[geekgirljess]

Hello all, first time poster but I've used these groups for a while as
an invaluable research and problem solving tool. So a big thanks to all
who help out here! Unfortunately I'm not sure where to begin trying to
find an answer to my current dilemma and I hope some kind soul can help
point me in the right direction yet again!

I'm currently working on a database to aid in order tracking for a
small telecommunications company. One of the telephone features we
provide is a feature to where phone numbers can be "linked" in what is
referred to as a "hunt group", such that if someone calls in on a
particular phone number and that line is busy or goes unanswered for a
certain length of time, the call will automatically hunt for another
free line. This is most often used by commercial customers with several
phone numbers, and I need to have something to keep track of how many
of these "hunt groups" the customer wants, and the order of the numbers
in each group.

To aid me in this I have a table, tblPhoneOrders, which has, among
other fields, the following:

phnID AutoNumber PK
phnNumber Text (10) 10 digit phone number
phnHuntGroup Byte which hunt group this number is in (if
any)
phnHuntOrder Byte order of this number in the hunt group

What I'm trying to figure out now is the best way to actually *display*
these on a form / report. Ideally, if a customer had the following 5
numbers, with 3 numbers in one group and 2 in another as follows:

phnNumber phnHuntGroup phnHuntOrder
912-555-1234 1 1
912-555-1235 2 1
912-555-2022 1 3
912-506-5678 1 2
912-602-9415 2 2

I should be able to show on the Phone Order Details form / report that
the "Hunt Groups" are requested to be set up as follows:

Hunt Group 1:
912-555-1234
912-506-5678
912-555-2022
Hunt Group 2:
912-555-1235
912-602-9415


Any ideas? I was thinking a TreeView at first, but I don't have any
experience whatsover with those and I've read they are buggy. I was
pondering if a simple ListBox would work but I can't quite seem to wrap
my head around it. Any advice or suggestions would be greatly
appreciated!!

(Disclaimer: above phone numbers were made up on the spot off the top
of my head. Any similarites to any actual routed telephone numbers is
unintended.)

 

[Marshall Barton]

Hello all, first time poster but I've used these groups for a while as
an invaluable research and problem solving tool. So a big thanks to all
who help out here! Unfortunately I'm not sure where to begin trying to
find an answer to my current dilemma and I hope some kind soul can help
point me in the right direction yet again!

I'm currently working on a database to aid in order tracking for a
small telecommunications company. One of the telephone features we
provide is a feature to where phone numbers can be "linked" in what is
referred to as a "hunt group", such that if someone calls in on a
particular phone number and that line is busy or goes unanswered for a
certain length of time, the call will automatically hunt for another
free line. This is most often used by commercial customers with several
phone numbers, and I need to have something to keep track of how many
of these "hunt groups" the customer wants, and the order of the numbers
in each group.

To aid me in this I have a table, tblPhoneOrders, which has, among
other fields, the following:

phnID AutoNumber PK
phnNumber Text (10) 10 digit phone number
phnHuntGroup Byte which hunt group this number is in (if
any)
phnHuntOrder Byte order of this number in the hunt group

What I'm trying to figure out now is the best way to actually *display*
these on a form / report. Ideally, if a customer had the following 5
numbers, with 3 numbers in one group and 2 in another as follows:

phnNumber phnHuntGroup phnHuntOrder
912-555-1234 1 1
912-555-1235 2 1
912-555-2022 1 3
912-506-5678 1 2
912-602-9415 2 2

I should be able to show on the Phone Order Details form / report that
the "Hunt Groups" are requested to be set up as follows:

Hunt Group 1:
912-555-1234
912-506-5678
912-555-2022
Hunt Group 2:
912-555-1235
912-602-9415


Any ideas? I was thinking a TreeView at first, but I don't have any
experience whatsover with those and I've read they are buggy. I was
pondering if a simple ListBox would work but I can't quite seem to wrap
my head around it.

A TreeView control is probably a good idea, but I have never
needed to use one so can't help with that idea.

To do it with a single select list box (or combo box), use
this kind of query for its RowSource:

SELECT P.phnHuntGroup, P.phnHuntOrder,
& " " & P.phnNumber AS Display
FROM tblPhoneOrders As P
UNION SELECT DISTINCT H.phnHuntGroup, Null,
"Hunt Group " & H.phnHuntGroup
FROM tblPhoneOrders As H
ORDER BY phnHuntGroup, phnHuntOrder

Set the list box's properties:
ColumnCount = 3
ColumnWidths = 0,0,1.5
Bound Column = 3

I also suggest that you do something like Lock the list box
so users can't interact with it. If it's necessary to allow
them to select an item in the list, use the list box's
BeforeUpdate event to prevent them from selecting a header
line:

If Left(Me.listbox,3) = "---" Then
Beep
Cancel = True
Me.listbox.Undo
End If

 

[geekgirljess]

Thanks for the response, Marshall. I figured some kind of SQL magic
would be involved, as SQL is by far one of my weaker points.

I had planned on locking it for now, as I mainly wanted something that
would simply display the groups intuitively in a single control. Though
if I could get drag-n-drop re-arranging or even button-click change
order/change group capabilities out of it that'd be most excellent. But
I won't push my luck.

Thanks again!

 

[Marshall Barton]

Drag and Drop is not a native Access capability. You might
want to search the web for ways to simulate it using various
events or API calls. Button's to move an item up or down
the list is probably easier.

You should also consider using a continuous subform instead
of a list box. You have a lot more control over the
appearance and behavior of a form than a list box.

 

[geekgirljess]

I'm already about 2 subforms deep on this particular form, what's
another level eh?

Drag and Drop is not a native Access capability. You might
want to search the web for ways to simulate it using various
events or API calls. Button's to move an item up or down
the list is probably easier.

You should also consider using a continuous subform instead
of a list box. You have a lot more control over the
appearance and behavior of a form than a list box.
--
Marsh
MVP [MS Access]

Thanks for the response, Marshall. I figured some kind of SQL magic
would be involved, as SQL is by far one of my weaker points.

I had planned on locking it for now, as I mainly wanted something that
would simply display the groups intuitively in a single control. Though
if I could get drag-n-drop re-arranging or even button-click change
order/change group capabilities out of it that'd be most excellent. But
I won't push my luck.

Thanks again!