-----Original Message-----
Yes, I will make about 30+ buttons for each menu and a set
of "quantity" that counts from Zero to Nine next to the
subform.
Like following
____________________________________________________
| main form ______________________________ |
| ____________ |A 0-9 number button set here | |
| | subform | |______________________________| |
| | | |
| | | |
| | | 30+ menu buttons here |
| | | |
| | | |
| | | |
| |__________| |
|___________________________________________________|
(c) click a "menu" button to add an item, and then click a
"quantity" button to adjust the quantity for the item you just added.
(C) is exactly what I need. [...]
3. I need a "cancel" button to undo tickets made by
mistake while the subform is dirty with menu on the main
form.
I'm afraid I don't understand what you are saying here. You'll have
to explain a bit more.
I am sorry that I wasn't clear.
My question was that if users accidently created a ticket
with three or four items, for instance, I need a button
that wipe out all the items selected in the subform to
make the ticket empty then delete the ticket for the next
one.
Since "ticketId" is autonumber, the main form, frm_order
won't actually make a new ticket number when it's opened
from main switchboard until "tickettype" selected from
buttons ("Dine-In" and "Take-Out") that users can click in
the main form.
If I am just wiping out all items selected in the subform
and exit the main form, there will be a dummy ticket
number with 0 item in tbl_order as well as tbl_orderdetail.
To prevent that, I think I need to delete the ticket
number after wiping out all items in subform, and I need a
button which can perform the task.
I'm afraid that, to make the quantity buttons work the way you want,
we're going to have to change some of the code you've already got. I've
been playing around with this, and this is what I've come up with. Note
that I haven't changed my table and field names to match yours, but you
should be able to figure out what of mine corresponds to what of yours.
On the form I have several "menu" buttons, each of which has its Tag
property set to an ItemID, and all of which have their OnClick event
properties set to "=AddToOrder()".
I also have 9 "quantity" buttons, with captions "1" through "9". I
didn't see any point of having a "0" button, since the current
arrangement doesn't support clicking two quantity buttons in a row to
make a two-digit number. Each of the "quantity" buttons has its OnClick
event property set to "=ChangeQuantity()". That has the effect of
setting the quantity for the current item (which will normally be the
one most recently added to the order) to the numeric value of the
button's caption. I also have a button named "cmdRemoveItem", which
will remove the current item from the order.
In addition, I have two more buttons named "cmdClearItems" and
"cmdDeleteOrder". cmdClearItems deletes all the items on the order but
leaves the order itself intact so you can add a different set of items
to it. cmdDeleteOrder deletes the order and, by way of cascading
deletes, all of the items that belong to it.
Here's the complete code for the form's code module, as it stands right
now:
'------- start of code for form module -------
Option Compare Database
Option Explicit
Function AddToOrder()
On Error GoTo Err_AddToOrder
Dim rs As DAO.Recordset
Dim varItem As Variant
If IsNull(Me.OrderID) Then
MsgBox "Enter basic order information first."
Exit Function
End If
If Me.Dirty Then Me.Dirty = False
varItem = Me.ActiveControl.Tag
If IsNumeric(varItem) Then
Set rs = Me!sfOrderItems.Form.RecordsetClone
With rs
.FindFirst "ItemID=" & varItem
If .NoMatch Then
.AddNew
!OrderID = Me.OrderID
!ItemID = CLng(varItem)
!Quantity = 1
Else
.Edit
!Quantity = !Quantity + 1
End If
.Update
.Bookmark = .LastModified
End With
Me!sfOrderItems.Form.Bookmark = rs.Bookmark
Set rs = Nothing
End If
Exit_AddToOrder:
Exit Function
Err_AddToOrder:
If Err.Number = 3201 Then
MsgBox "Item " & varItem & " is not defined in the Items table."
Else
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
End If
Resume Exit_AddToOrder
End Function
Function ChangeQuantity()
Dim varQuantity As Variant
varQuantity = Me.ActiveControl.Caption
If Not IsNumeric(varQuantity) Then
MsgBox "Error: bad call to ChangeQuantity()"
Exit Function
End If
With Me.sfOrderItems.Form
If .NewRecord Then
DoCmd.Beep
Exit Function
End If
!Quantity = CLng(varQuantity)
End With
End Function
Private Sub cmdClearItems_Click()
If IsNull(Me.OrderID) Then
Exit Sub
End If
CurrentDb.Execute _
"DELETE FROM OrderItems WHERE OrderID=" & Me.OrderID, _
dbFailOnError
Me.sfOrderItems.Requery
End Sub
Private Sub cmdDeleteOrder_Click()
On Error GoTo Err_Handler
If Me.Dirty Then
Me.Undo
End If
If Not Me.NewRecord Then
RunCommand acCmdDeleteRecord
End If
Exit_Point:
Exit Sub
Err_Handler:
If Err.Number <> 2501 Then
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
End If
Resume Exit_Point
End Sub
Private Sub cmdRemoveItem_Click()
Dim rs As DAO.Recordset
With Me.sfOrderItems.Form
If .Dirty Then
.Undo
End If
If Not .NewRecord Then
Set rs = .RecordsetClone
rs.Bookmark = .Bookmark
rs.Delete
If .NewRecord Then
If rs.RecordCount > 0 Then
rs.MovePrevious
.Bookmark = rs.Bookmark
End If
End If
Set rs = Nothing
End If
End With
End Sub
'------- end of code for form module -------
If you click the same "menu" button more than once for an order, that
has the effect of adding 1 to the quantity already ordered for that
item. You can change that behavior easily enough, if you just want to
display an error message.
All that may not be exactly how you want your form to behave, but it
should be a step in the right direction.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
.
