Forum Discussion
How to populate all the selected label controls into the menu in Ms Access Form
I need help my mathematics with vba code is missing some fields called onions and traditional tea as part of the main menu available for the restaurant cashier’s selection. The menu is made of labels controls and a query such that when an item is selected either by a mouse click or touch screen then its should be written in the data sheet. I know I can easily achieve that by simply using a combo box and some vba on after update but the environment we are required the mentioned to be used.
Kindly find attached the simple database for easy following and below are the VBA that I’m trying to use.
Private Sub Form_Open(Cancel As Integer)
Me.txtGroupNo = 0
DisplayMenuItems 0 'display Group 0 as default
End Sub
Private Sub cmdGroup_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
'Detect MouseDown event for menu group buttons
ClearMenuButtons 'reset all menu button backcolors to default grey
ClearSelections 'and reset all sale item cells to default backcolor
Me.txtGroupNo = Y \ (Me.cmdGroup.Height \ 😎 'calc menu button clicked (0-7)
DisplayMenuItems Me.txtGroupNo 'and display sales items for selected menu
End Sub
Private Sub cmdItem_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
'Detect Sales Item clicked on
Dim vX As Long, vY As Long
Dim vCtrl As Control
ClearSelections 'and reset all sale item cells to default backcolor
vY = Y \ (Me.cmdItem.Height \ 6) 'fetch vertical location (0-5)
vX = X \ (Me.cmdItem.Width \ 😎 'fetch horizontal location (0-3)
Set vCtrl = Me("Label" & vY & vX) 'calc label name
If vCtrl.Caption <> "*" Then 'skip if unused cell
vCtrl.BackColor = 13434828 'highlight cell in green
Me.txtPrice = DLookup("ItemPrice", "tblSaleItems", "ItemLocation = '" & vY & vX _
& "' AND ItemGroup = " & Me.txtGroupNo) 'fetch item price from table and display
Me.txtProductNames = DLookup("ItemName", "tblSaleItems", "ItemLocation = '" & vY & vX _
& "' AND ItemGroup = " & Me.txtGroupNo)
Me.txtQuantities = 1
Me.txtVatrates = DLookup("Vatrate", "tblSaleItems", "ItemLocation = '" & vY & vX _
& "' AND ItemGroup = " & Me.txtGroupNo)
Me.txtTaxlabels = DLookup("TaxClass", "QryTaxClass", "ItemLocation = '" & vY & vX _
& "' AND ItemGroup = " & Me.txtGroupNo)
Me.OptVats = DLookup("TaxInclusive", "tblSaleItems", "ItemLocation = '" & vY & vX _
& "' AND ItemGroup = " & Me.txtGroupNo)
End If
DoCmd.SetWarnings False
DoCmd.OpenQuery "QryPosRestuarant"
Me("sfrmRestaurantDetails").Form.Requery
End Sub
Public Sub DisplayMenuItems(vGroupNo As Long)
'Update sales items captions with item names from table when menu selection changed
'Entry (vGroupNo) = Number ref of menu button (0-7)
Dim rst As Recordset
Dim vCtrl As Control
Set vCtrl = Me("Label" & vGroupNo + 1) 'calc name of label for button image clicked
vCtrl.BackColor = 14869218 'and set color to light grey (14869218)
Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblSaleItems WHERE ItemGroup = " & vGroupNo) 'fetch sales items for selected menu from table
Do Until rst.EOF
Set vCtrl = Me("Label" & rst!ItemLocation) 'calc label name from ItemLocation field in table
vCtrl.Caption = rst!ItemName 'copy ItemName to label caption
rst.MoveNext 'and move to next item
Loop
rst.Close
Set rst = Nothing
End Sub
Public Sub ClearSelections()
'Clear all sales item labels to default backcolor (light grey)
Dim vCtrl As Control
Dim vRow As Long, vCol As Long
For vRow = 0 To 5
For vCol = 0 To 3
Set vCtrl = Me("Label" & vRow & vCol) 'calc label name
vCtrl.BackColor = 14869218 'and set color to light grey
Next
Next
Me.txtPrice = 0
End Sub
Public Sub ClearMenuButtons()
'Reset all menu label buttons to default backcolor (dark grey)
Dim vCtrl As Control
Dim vRow As Long
For vRow = 1 To 8
Set vCtrl = Me("Label" & vRow) 'calc label name from vRow (Label1 - Label8)
vCtrl.BackColor = 12632256 'and set BackColor to grey
Next
End Sub
- George_HepworthSilver ContributorI don't see an attachment. Can you try again?
Also, please clarify. The problem is that two products are not properly displayed in the menu?
Green tea should be displayed with other beverages? and Onions should be displayed with .....?- Christophernector2013Copper Contributor
I have a data sheet called sfrmrestuarantdetails where if I click any label I'm able to enter the label name in the data sheet by calling an append query via the the onclick mouse event, except the onions and tradition tea that is where I want help locate where I'm missing it. I hope you will be able to see the small database attached.
- George_HepworthSilver ContributorNo I do not see a database attached. All I see is a screenshot of one form.