How to populate all the selected label controls into the menu in Ms Access Form

[Christophernector2013]

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

[#2]

I 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 .....?

[#3]

@George_Hepworth 

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.

[#4]

No I do not see a database attached. All I see is a screenshot of one form.

[#5]

This is a bald guess, in the absence of any substantive details to work with, but it's quite possible there is a problem in your data. When part of a process does work as expected, and other parts don't, it can often be traced to problems in the data. Of course, without seeing your data, the best we can do is guess.

[#6]

Crossposted with several responses at https://www.access-programmers.co.uk/forums/threads/how-to-populate-label-controls-on-a-form-to-act-... 

[#7]

And that one does include the accdb attachment.... hmm.

[#8]

@George_Hepworth 

Thank you George , I managed to clear it the issue was improper referencing the labels.

Many thanks

Regards