Need pointing in the right direction for modifying order entry 2

[DBServices]

Am building a db for a restaurant. Have been working on this for a while now and MajP has helped me tremendously. I have started another thread due to the extensive length of the previous one concerning the same db...thread702-1616766...

MajP, I would like an idea or two on how to modify an order. To bring you up to date: I have a main order entry form with 3 list boxes (unbound). The first is where users pick a category such as "breakfast, lunch, dinner, extras..."
The second is cascading according to what is selected in the first. i.e., you select "breakfast" in the first and the second is populated with the breakfast menu items. Once you select an item in the second list box, various pop up forms load and ask you to select sides, food preparation, and such. All of this gets appended into the tblMenuItemDetails as you suggested. I really love this idea because you can add as many sides as you need without being bound to just the normal "two" sides that come with a meal in case things change down the road. The third list box is for display only, this is so the user can see the whole order for that table for accuracy as it's being selected before it will eventually be submitted to the kitchen. I had to run two loops as you suggested and it works and looks great.
Now my question is this: How is the best way to modify an item? Example: suppose I want extra cheese in my omelet and no seasoning in my hash browns? I would like to have this inserted into the tblMenuItemDetails because it would be easier that way but I need a little direction on the best way to do this...
Below is my tblMenuItemDetails.

ID OrdSelect ItemChoice      MenuItemChoice        PrepDesc

243   183	51	     Ham/CheeseOmelet	 Extra Chees
244   183	42	     Hash Browns	   NO SEASON
245   185	41	      NY Strip	              Well
246   185	51	         Eggs	               OE
247   185	49		Eng Muffin	
248   185	42		Grits

When I select "OE" for my eggs, it's because of the required choices table that holds that info which loads a pop up form and inserts that selection into the tblMenuItemDetails. I want the modifiers to do the same thing but I need to figure out a way to modify the correct item, such as being able to select the item I want to modify and then being able to run an UPDATE query to insert that "extra cheese" and "no seasoning" selections... So, when user selects "Extras" in the first list box, should I have a form pop up with a combo box populated with the items entered already for that order and then once the item is selected, user can modify that item?
I hope I have explained this well enough and I appreciate all of your help...
Thanks, Dannie.

 

[MajP]

I think I would have 2 ways to do this. When you pick an item sometimes you have mandatory preperation description (Meat: rare, medium rare, .. Well, or Eggs: OE,OH,SSU,Poached,...Scram). But I am guessing you have optional descriptions as well (No Salt, runny in the middle, No season, No onions...). The items that are required choices, contained in the required choice table, should have an additional textbox on the same form you pick (OE, OH,..SSU) to add non required description. That then could get concatenated into the prepdesc. so prepDesc whould equal the values in comboPrepDesc & ";" & txtBxAdditionalDesc. Giving you something like this as the insert: "OE; No Salt"

After the fact you could add a modifier by clicking on an item in the list box. Double click on an item, and get a popup, with a button to delete or modify. If you select modify it then gives you a text box to add a modifier and concatenates it to the existing.

But you propably are more interested in items that are not in the required choice table. Lets say someone orders the Eggs and Bacon, and since this meal comes only with bacon there is nothing in the required choice table. But they would like the crisp pieces if possible. Could this be a general note for the order, instead of adding a Bacon line in the menuItemDetails.

 

[DBServices]

Thank you for your help and advice. I was thinking something other than what you suggested and it would have added a lot of work to the design phase and to the flow of work to the users as well. I will work on what you suggested and let you know how it goes.
I do have a small problem I can't seem to fix and it concerns an SQL statement and the quotes it requires for variables that are numeric and text. I started working on adding "extra..." to the main item chosen, such as "Cheese Stk. Omelet -Extra Jalapenos". Below is the code I wrote but it is putting an extra apostrophe in the table and I can't figure out how to fix this...

Dim strSQL As String, ordItem As Integer, extraItem As String, extra As String
 extra = "-EXTRA "
  ordItem = Me.cboPickItem.Value
   extraItem = Me.lstExtras.Column(2)
  strSQL = "UPDATE tblOrderDetails SET Extra = " & "'" & extra & "'" & "'" & extraItem & "'" & " WHERE tblOrderDetails.OrderSelectionID = " & ordItem & ";"
 DoCmd.SetWarnings False
  DoCmd.RunSQL strSQL
 DoCmd.SetWarnings True

In the list box it looks like:
"CHEESE STK. OMELET -EXTRA 'JALAPENOS"

How do i get rid of the apostrophe between the words extra and jalapenos? And is there a FAQ I can read on the proper way to use single/double quotes surrounding variables when writing SQL statements in VBA?
Thank you again...Dannie.

 

[MajP]

The single quotes go around the concatenated string.
So you want to put single quotes around:
CHEESE STK. OMELET -EXTRA JALAPENOS

"UPDATE tblOrderDetails SET Extra = '"& extra & " " & extraItem & "' where...


remember you can always test the strSql in the immediate window
debug.print strSql

 

[DBServices]

thank you! that was very helpful...Have a great day and I will keep you up to date on the database..

 

[MajP]

I never get my quotes, apostrophes, or sql dates wrong because I never write them. I do not mess up my field names and values for insert queries because I do not write them. I push them to functions to do the tedious work for me. You might want to stick these in your code library, and roll some to help with your update queries

Public Function sqlTxt(varItem As Variant) As Variant
  If Not IsNull(varItem) Then
    varItem = Replace(varItem, "'", "''")
    sqlTxt = "'" & varItem & "'"
  End If
End Function

Public Function insertFields(ParamArray varfields() As Variant) As String
  Dim fld As Variant
  For Each fld In varfields
    If insertFields = "" Then
      insertFields = "(" & fld
    Else
      insertFields = insertFields & ", " & fld
    End If
  Next fld
  If Not insertFields = "" Then
    insertFields = insertFields & ")"
  End If
End Function

Public Function insertValues(ParamArray varValues() As Variant) As String
  Dim varValue As Variant
  For Each varValue In varValues
    If IsNull(varValue) Then varValue = "NULL"
    If insertValues = "" Then
      insertValues = "(" & varValue
    Else
      insertValues = insertValues & ", " & varValue
    End If
  Next varValue
  If Not insertValues = "" Then
    insertValues = insertValues & ")"
  End If
End Function

Function SQLDate(varDate As Variant) As Variant
    'Purpose:    Return a delimited string in the date format used natively by JET SQL.
    'Argument:   A date/time value.
    'Note:       Returns just the date format if the argument has no time component,
    '                or a date/time format if it does.
    'Author:     Allen Browne. allen@allenbrowne.com, June 2006.
    If IsDate(varDate) Then
        If DateValue(varDate) = varDate Then
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
        Else
            SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
        End If
    End If
End Function

test

Public Sub Test()
  Dim flds As String
  Dim vals As String
  Dim strSql As String
  flds = insertFields("FirstName", "LastName", "OrderID", "OrderDate")
  vals = insertValues(sqlTxt("Mike"), sqlTxt("O'Brien"), Null, SQLDate(Date))
  
  strSql = "INSERT INTO tableName " & flds & " VALUES " & vals
  Debug.Print strSql
End Sub

output
INSERT INTO tableName (FirstName, LastName, OrderID, OrderDate) VALUES ('Mike', 'O''Brien', NULL, #11/22/2010#)

If you are referencing a lot of field values, that can be a tremendous sanity saver. If you are writing a lot of sql strings in code it can save a whole lot of time and avoid mistakes

 

[DBServices]

I copied and pasted and made a test table to insert a couple fields and it worked like a charm! I will use this from now on and will try to change it from an INSERT query to an UPDATE query so i will have both. thanks MajP...You and Tek-Tips are a great help to all of us...Dannie.

 

[DBServices]

I have run into a small problem I can't seem to figure out. On my OrderEntry form which is bound to the tblOrders I have various fields such as txtOrderNumber, cboTableNumber, txtGuests, etc... When I go to a new record, the new order number is displayed in the txtOrderNumber field (auto number field) and then I select values in the other fields I just mentioned. Then I select a category in my first unbound list box; this populates the second list box with menu items. When I select a menu item I run an INSERT query to insert a line into the tblOrderDetails which holds the menu item and a couple other relevant fields. (My relationships are one-to-many between the tblOrders and the tblOrderDetails; and one-to-many on the tblOrderDetails and the tblMenuItemDetails.)That said: even though my new order number is displayed in the txtOrderNumber field, when I try to run my INSERT query it won't run if I have referential integrity selected between my tblOrders and tblOrderDetails because the new record hasn't been saved to the tblOrders yet. I went to a new record on my OrderEntry form and selected a few values before I even tried to run my query (to test this) and then opened the table and the values are not there. Obviously if I navigate to a different record and back the values are there but that would be impossible to do at run time. So my question is this: How do I save my record in the tblOrders so that when I run the code below it will run when I enforce referential integrity?

Private Sub RowsSelected()

Dim ordID As Integer, personNum As Integer, itemNum As Integer, intPersonCount As Integer, strSQL as String

ordID = Me.txtOrderID.Value
 itemNum = Me.lstItems.Column(0)
  strSQL = "INSERT INTO  tblOrderDetails (OrderID,MenuItemID) VALUES (" & ordID & "," & itemNum &");"
  DoCmd.SetWarnings False
 DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

'set global order selection id
 glblOrdSelID = DMax("OrderSelectionID", "tblOrderDetails")
  intPersonCount = Nz(DMax("personID", "tblOrderDetails", "OrderID =" & ordID)) + 1
   strSQL = "UPDATE tblOrderDetails SET PersonID = " & intPersonCount & " WHERE tblOrderDetails.OrderSelectionID = " & glblOrdSelID & ";"
   DoCmd.SetWarnings False
  DoCmd.RunSQL strSQL
 DoCmd.SetWarnings True

End Sub

Normally I use Recordset.move(0) but that's when I have dimmed a record set...Thank you for any and all help..(Right now I have un-checked the referential integrity check box between the tblOrders and tblOrderDetails to make this work but I know there is a better way.)

 

[PHV]

...
Me.Dirty = False
ordID = Me.txtOrderID.Value
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DBServices]

Hello PHV, thank you very much! i forgot all about me.dirty...Works great...thank you again.