Oct 16 2021 01:18 AM
Oct 16 2021 01:18 AM
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 \ 8) '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 \ 8) '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
Oct 16 2021 04:09 AM
Oct 16 2021 10:51 AM
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.
Oct 16 2021 11:46 AM
Oct 16 2021 02:21 PM
Oct 17 2021 12:47 AM
Crossposted with several responses at https://www.access-programmers.co.uk/forums/threads/how-to-populate-label-controls-on-a-form-to-act-...
Oct 17 2021 07:00 AM
Oct 19 2021 01:37 AM
Thank you George , I managed to clear it the issue was improper referencing the labels.