Concessions Multilevel Item list on a Access Form

[bartus991]

I'm willing to create a concession sales program in access 2007, but I'm stuck.

The concessions program will have two different Items
1. Sales Items
2. Menu Items, which contains 2 or more sales items

I have created the following tables:

tblOrders
- OrderId
- Date
- TerminalNo.

tblOrderDetails
- OrderId
- OrderDetailId
- Itemtype (Sales or Menu Item)
- Quantity
- ItemId (Unique number of Sales or Menu Item, with a listbox the name is shown)
- SalesPrice per Item
- Discount

tblOrderDetailsSub (will only be used if it is about a MenuItem)
- OrderDetailId
- OrderDetailSubId
- ItemId (Unique number of Salesitem, with a listbox the name is shown)

Also I have created the SalesItem tables
tblSalesItems
- ItemId
- Itemtype (menu or sales)
- ItemName

tblSalesItemsSub (only for the menu items, which contains more than 1 sales item
- ItemId
- SalesItemsSubId
- AssignedSalesItems (ItemId of Salesitem, with a listbox the name is shown)

With above tables the appropriate records will be generate in the OrderDetails and OrderDetailsSub. This works already very well, a kind of switchboard access form will allow the user to select the sales or menu-items. The mainform contains the information of the OrderId

But on this form I would like to create a list overview of all the selected sales items, which can be selected to modify (only with the buttons on the form, but the OrderDetailId must be retrievable.

The list must look like:
Quantity Item Price Total
1 Coca Cola 3.00 3.00
2 Fanta 3.00 6.00
1 Duo Menu 8.00 8.00
1 Coca Cola
1 Coca Cola
1 Popcorn Salt.
1 CandyBar 1.25 1.25

This means that the Duo menu, have 3 subrecords: Coca Cola, another Coca Cola and a Popcorn Salt.

I can’t figure out how to create this list. I have search for a ListView which allows sublevels, but as I am not a great visual Basic programmer I only get the Information displayed without the subrecords.

Also I would like that the Listview sees the MainLevel and the SubLevel as one Item, so all the lines will be highlighted, for example if I select the Duo Combo in the list, the following will be highlighted:
1 Duo Menu 8.00 8.00
1 Coca Cola
1 Coca Cola
1 Popcorn Salt.

Hopefully some one can help me to get this ItemList with one sublevel and how to create this. Please note that the Total column in this list is a sum, but this can also be calculated in a query if necessary.

To give you an sample Idea you can have al look at this:

The program I'm creating won't be a touchscreen, but it gives you a slight Idea what I mean.

 

[MajP]

You have several options to get this view:

Quantity   Item                         Price  Total
   1       Coca Cola                    3.00      3.00
   2       Fanta                        3.00      6.00
   1       Duo Menu                     8.00      8.00
            1 Coca Cola
            1 Coca Cola
            1 Popcorn Salt.
   1       CandyBar                     1.25    1.25

I have not looked at your tables, but here are your options.

1) build a Union query to combine the subItems into the query. In other words you should be able to make a query that looks exactly like you are presenting.

Something like (very notional):
select
Quantity,
Itemname as Item,
salesPrice,
SalesPrice * Quantity as TotalPrice,
orderDetailID,
null as orderDetailSubID,
itemType,
From
(some join of salesitem, orders, orderdetails)
Union
Select
Null as Quantity,
" " & someTable.Quantity & " " &ItemName as Item,
null as salesPrice,
null as totalPrice,
orderDetailID,
orderDetailSubID,
"Menu" as itemType,
from
(some join of order, item, subitem)
OrderBy
have to play with this to get it into the correct order
I did not see a quantity for subItems.

However, you should be able to get the columns you describe in the correct order.
2) If you can get the union query correct then you have 2 options
A. Just bind it to a list box and show the columns you need and hide the ones you do not need. Pro: using a union query requires very little coding. Con: no way to do any colorization or formatting.
B. Same as above but make a continous form to look like a list. Pro: you could do conditional formatting and other formatting. Will require some code to alternate colors. Con: A little more difficult to get the format you want.

3) You can use a listview to do this. But since you are not binding a query, you have a much more flexibility. With a listview you can read through recordsets using DAO or ADO and put the values where you want. Pro: If you are comfortable with recordsets this provides a more flexible way to load the data. You can color and format individual cells and rows. Con: If you are weeak in VB and working with recordsets then do not even consider it.

They will all fundamentally work the same.
1. These controls are for display and navigation: adding, deleting, or modifying records is done in a seperate but linked area
2. Each time you add, delete, modify an order item I would not even bother trying to edit the control. I would simply requery or reload.
3. Since all of these controls will have an orderid, itemID, subitem id in the "rowsource" you should be able to select any order item and either pop up a form to edit the item or simply have it as a "linked" subform somewhere in the main screen.

One thing

ItemId (Unique number of Sales or Menu Item, with a listbox the name is shown)

If you are suggesting putting a pulldown in a table,
NEVER,EVER consider doing this. Your are better off sticking a needle in your eye if you enjoy pain. Pull downs ONLY go in Forms even if MS allows you to do otherwise.