need direction on how to enter order 1

[DBServices]

Am building a restaurant db. I want to have the servers select the dish and then select the side(s) that come with the dish. My idea is to have a main form that will populate a listbox on it with the relevent items based on the category they selected, such as "Breakfast", "Lunch", etc...Once they select the dish, I want a form to pop up asking how they want their eggs cooked. Then another form asking what sides..Once they select all the info, I want a listbox on the main form populated with the results. I have tables with all the info such as tblMenuItems, tblSides, tblPrepareEggs, tblPrepareMeats, etc...they contain the names of the menu items, sides, how to cook the eggs, how to cook the meats, respectively. I need to be able to select more than one order and possibly make the listbox look like:

Bacon Platter, OE
Home Fries
Toast
Fish & Eggs, OM
Hash Browns
English Muffin

2 coffees


Putting the selected values from the forms in the listbox is where i need help.
I am just beginning this but have my table structure and relatioships complete. I am hoping to get some advice on the best way to go about this. I have been programming in VBA for a few years now; am still a beginner but will go where you tell me to go...Any and all help will be greatly appreciated...Thank you

 

[MajP]

You really did denormalize this. I am not sure if you want to go this far. I would think very carefully and "what if" the possibilities. As long as you will only ever care about meat prep, and eggPrep, and only ever have 2 sides max. Then this is workable, but it could break down really quick.
A future menu choice with 3 sides
a menu choice with one poached and one scrambled egg
a menu choice where you would want to prep the side a certain way

I would go back to look at the concept of using menu item details
tblMenu_ItemDetails
OrderSelectionID_fk
menuChoiceID_FK
foodPrepID_F
This concept is far more flexible, and only a little more work. I can have any number of sides, any number of food preparation decisions. You could add quantity in here and a notes field. Maybe and "isSide" field. It also solves your question about the second side.


Every row in tblOrderDetails represents a persons menu choices for a given order. I added a PK to this table orderSelctionID_PK (autonumber) to uniquely identify this. I did this out of simplicity. You could use a composite primary key composed of the three keys.
OrderID_fk, PersonID, MenutItemID_FK
No problem doing this, but it makes it cumbersome
OrderSelectionID_PK uniquely defines the above three keys in a single key.

I do not know how you are popping up your choice forms, but somehow you have to either pass the form (or public variables) the OrderSelectionID_FK or all three composite keys.

So a person says they want the big Breakfeast. That should create a record in tblOrderDetails and generate an orderSelectionID_PK. Now pass the form (or use a public variable) this key. Choose rare and insert the choice into your tblOrderDetails at record with the orderSelectionID_PK.

 

[DBServices]

Okay MajP, I need your expertise one more time...lol.
I went back to the MenuItemDetails as you suggested. I set up the relationships accordingly and am stuck on one thing...
Let me explain how I have it set up: I have a OrderEntry form with two listboxes so far. The first, lstMenuGroup, holds the menu groups such as Breakfast, Lunch, Dinner, Sides etc..Once a menu group is selected, the other listbox is requeried and populated with menu items relevant to menu group. You select Breakfast in the lstMenuGroup and lstMenuItems is populated with breakfast items. Once you select a breakfast item a rec is created in the tblOrderDetails and I have a function that opens a recordset and checks the tblRequiredChoices and then prompts for my MeatPrep, EggPrep, and Sides forms but only which ones are needed. (a selection of pancakes won't prompt for the frmMeatPrep...)
So say a selection of NYStrip&Eggs is selected; a rec is created in the tblOrderDetails and the OrdID, PersonID, and MenuItemID is inserted; no problems there. The frmMeatPrep will load with a listbox populated with the relevant meat prep choices. Once a selection in made, I need to insert a record into the tblMenuItemDetails with the OrdSelID (which is from tblOrderDetails), and the FoodPrepID, right? Okay, how do I insert the OrdSelID? How do I pass the correct OrdSelID to every line?

Example:

tblMenuItemDetails
MenuItemDetailsID OrdSelID MenuChoiceID FoodPrepID
001 225 1(steak) 2(rare)
002 225 22(eggs) 25(OE)
003 225 14(toast)
004 225 3(hash browns)
005 226 2(tenderloin) 3(fried)
006 226 14(toast)

This is the same Order Number but two different people so the number 225 represents what the first person got, and 226 is what the second person got. I realize you already understand all this but just making sure so I don't waste your time...so my question is, how do i insert the right OrdSelID into the tblMenuItemDetails? I know it is coming from the tblOrderDetails but am confused on how to go about doing this...Thank you for your help...again...lol.

 

[MajP]

In my mind the flow is like this.
1)Create a new order and get a orderID ( lets say OID001)
2)Maybe with a subform or some other method you select a menu item (lets say MENUITM001). This creates a record in the order details table.
Lets say (OSELID225). Probably the easiest is to save this id as a global variable. Lets call that glblOSELID. If you do not save it as a public variable (global in scope) then you are going to have to pass it from form to form.
3)When you create a record in the order details table by selecting a menu item, you then loop through the required choices for that menu item MENUITMOO1 and presents the proper form.
4)When you close the "choice" form Or hit an OK button it inserts into the menu Items detail table the menuchoiceID, FoodPrepID, and the glblSelID value of OSELID225. It does this for each choice.

Then you choose your next menu item which creates ordSelID226 ...

I am guessing from your question that your flow is different. You Enter all the menu Items and then want to do all the choices for all menu items.

If you wanted to do it that way. Once you are done selecting all menu Items. You would loop through the menu items for the current order. Get the OrderSelID, and then select the choices.

 

[MajP]

http://www.4shared.com/document/P_V-V4_i/ShortCutMenuControl_V2__2003_.html

Here is another control that you might find interesting. I never considered this until recently someone asked if it could be done. Once I did it, I have used it a lot. This is much better interface in my opinion than cascading combo boxes. It is a little tricky to do the first time, but once you code it once it is easy to reuse. Just click the demo button. I thought of your db because it is already showing food categories and food choices. Something you may want to consider for later

 

[DBServices]

I have the same flow as you. Once a Menu Item is selected in the list box, a rec is created in the tblOrderDetails. Then forms load according to what sides, meat prep, and egg prep is needed. Then it is back to the Order Entry form to submit another order...One question: I haven't been programming for the last few months and can't seem to get back "in the zone", how do I save the OrdSelID as a global variable after a rec is created in the OrderDetails table? Here is the code I am using to enter the item into the OrderDetails table:

When an item is selected in the lstMenuItems, this is the after update event handler...

Dim ctlList As Control, varItem As Variant, strSQL As String, num As String, ordID As String
ordID = Me.txtOrderID.Value (order entry form)
Set ctlList = Me.lstItems (listbox on order entry form)

For Each varItem In ctlList.ItemsSelected
num = ctlList.ItemData(varItem)
strSQL = "INSERT INTO tblOrderDetails(OrderID,MenuItemID) VALUES (" & ordID & "," & num & ");"
DoCmd.RunSQL strSQL
Next varItem

This code inserts a rec into the order details table after a menu item is selected in the list box on the order entry form. When the rec is created, how do i get the OrderDetailsID from there into a global var???
Sorry, can't seem to get it together lately...Thank you MajP...you are really helping me a lot!!

 

[MajP]

This looks to me like you are inserting many menuItems.

For Each varItem In ctlList.ItemsSelected
num = ctlList.ItemData(varItem)
strSQL = "INSERT INTO tblOrderDetails(OrderID,MenuItemID) VALUES (" & ordID & "," & num & ");"
DoCmd.RunSQL strSQL
Next varItem

I would expect something more like

public glblOrdSelID

For Each varItem In ctlList.ItemsSelected
  num = ctlList.ItemData(varItem)
  strSQL = "INSERT INTO  tblOrderDetails(OrderID,MenuItemID) VALUES (" & ordID & "," & num & ");"
  DoCmd.RunSQL strSQL
  'set global order selection id
  glblOrdSelID = dmax("ordSelID","tblOrderDetails")  
  'code here to add menuItem details
Next varItem

If you are using an autonumber normally the numbers are generated sequentially. However, sometimes autonumbers are random or can become random. Access does not have a way to determine the last record entered unlike SQL server. So most of the time you are safe on just pulling out the greatest orderDetailsID. To be extra safe you can generate you own PK or maybe add a time stamp. Then grab the PK with the largest date using a dmax function. But lets assume you are always generating an increasing orderDetailsID.

 

[DBServices]

Thanks, i will work on that...No, i am not inserting many menu items, I realize it's looping through the list box but there is only one menu item selected, the list box is not multi-select. I just used that code in case I want to change it up, but I probly won't...Yes, I am using a auto number in the OrdDetailID field...I might make my own but hopefully this works...but I am sure it will...Thank you for your help. I will work on this later tonight and let you know tomorrow what is happening..Have a good one!

 

[DBServices]

I got everything to work and I like the way it's coming along. I have a couple questions though...

dim my variables
person = ???
strSQL = "INSERT INTO tblOrderDetails(OrderID,PersonID,MenuItemID) VALUES (" & ordID & "," & person & "," & num & ");"
DoCmd.RunSQL strSQL

1.)Here's my code for inserting a line into the tblOrderDetailsTable...I want to make the "person" variable a counter that starts at one so everytime a line is entered it will insert what each person got..instead of having to select the person in a combo box...
2.)I would like to make this database handle everything including popping open a cash drawer.Is there a specific software that will integrate w/ Access I can get? Because I don't want to have to use database to create order and then have to use cash register to pay for order. That would be too cumbersome.
Other than that, I still have a long way to go but it's coming along. I am going to make a list box on the right side of the main entry form that will show the whole order as it's being created. It's record source will obviously come from different tables but as long as i can format it to look the way i want then it'll be cool.
Thanks for all your help!

 

[DBServices]

Let me explain a little more...My list box that holds all the menu items will only have one menu item selected because i want to select the menu item and then insert any sides/egg & meat preps that go with that order first before i select another menu item. Once a menu item is selected, the submit button runs the insert query and then the prep forms and sides form load if needed. Then the selected item in the list box is deselected and user can select another menu item for another person. I have it looping through the list box but like i said, there will only be one menu item selected at a time so what i want to do is:
1.) select a menu group in the first menu group list box
2.) select a menu item in the second list box
3.) take that selection and insert it into the tblOrderDetails along with the global variable, and the personID which will start at one.
Then once preps/sides are selected, if needed, i want to select another menu item if needed and repeat the process but personID would be two now.

I can't figure out how to get rid of the loop in the menu item list box and just get the selected item and insert it. Here's the code I am using now...

Dim ctlList As Control, varItem As Variant, num As Integer, ordID As Integer, personNum As Integer
personNum = Me.cboPersonID
ordID = Me.txtOrderID.Value
Set ctlList = Me.lstItems
For Each varItem In ctlList.ItemsSelected
num = ctlList.ItemData(varItem)
strSQL = "INSERT INTO  tblOrderDetails (OrderID,MenuItemID,PersonID) VALUES (" & ordID & "," & num & "," & personNum & ");"
DoCmd.RunSQL strSQL

'set global order selection id
glblOrdSelID = DMax("OrderSelectionID", "tblOrderDetails")
Next varItem

personID is just a combo box i am using right now to insert each personID...
thanks for all your help...

 

[MajP]

For question 2 post a seperate thread in the forum "Access Other Topics". I do primarily engineering type database work, not buisness. There are a lot of people who focus in this area.

As for question 1. I would think a single person would have many menu items. Wouldn't you have something like?

menuItemName menuItemCategory
p1 Coke Beverage
p1 Prime Rib Entree
p1 Pecan Pie Desert
p1 nachos Appetizer

Also I would think you still need a method to manually choose as well. What happens when you need to go back to add another menu item after they have already ordered.

Without seeing how all forms and flow comes together, it is hard for me to figure out how to implement a counter. I could see a flow where you have a button to add first order and a next button that moves to the next person. In that case I still think I would have a combo box but when I hit next person it just sets the value of the combobox to the next person. Also if the value of "Guests" in the Orders table is 4 my combo would only have choices "P1,P2...P4". But this design would then allow you to manually override as well by changing the combo to a previous value.

Take a look at that list view demo, to go back to your original question. I would envision a main form that shows the menu build as you click your choices with a list view you have options for coloring and formatting rows that you do not have in a listbox. I would envision on my forms left side a list view "menu" and then other tabs and subforms on the right for making choices. The list view can be populated through code as you enter records into the tblmenuItemDetails, and tblOrderDetails to show something like

P1
  -Steak and Eggs (Entree)
     + Steak  Rare
     + Eggs   Over Easy
     + Side 1 Hash Browns
     + Side 2 Bacon
  - Orange Juice   (Beverage)
  - Pecan Pie      (Desert)
P2
  -3 Pancakes     (Entree)
    + Bread  Biscuit
    + Side 1 Sausage
  -Milk           (Beverage)

(-+ just to show level)

 

[DBServices]

Thank you. But to answer your question,

p1 Coke Beverage
p1 Prime Rib Entree
p1 Pecan Pie Desert
p1 nachos Appetizer

is almost exactly how i have it set up. I will go and check the list view out and get back to you in a couple days...Thank you for your help.

 

[MajP]

I can't figure out how to get rid of the loop in the menu item list box

You are making that one hard. If it is not a multi select list box then the value of the list is the selected item.

num = ctlList.value
returns the selected item value (of the bound column)
If you have multiple columns you can return those values

num = cltList.column(0).value (first column)
str = ctlList.column(1).value (second column)

 

[MajP]

Here is a rough idea for the counter.

Build a function to get the person counter

Public function getPersonCounter (orderID)
dim intPerson as integer
dim strWhere as string
"OrderID = " & OrderID
intPerson = nz(dmax("personNumber","tblOrderDetail",strWhere)
end function

This is suggesting to make it easy in the order details insert two fields if you plan to save a string for the personID

PersonID PersonNumber
P1 1
P2 2

If there are no people for that order (first order) then it returns 0.

Then your code is something like
personNumber = getPersonCounter (glblOrderID) + 1
personID = "P" & personNumber
num = ctlList.value
strSQL = "INSERT INTO tblOrderDetails (OrderID,MenuItemID,PersonID,personNumber) ...