How to populate label controls on a form to act as a restaurant menu

[nectorprime]

I need help with my mathematics, vba code is missing some fields called onions and traditional tea as part of the main menu available for the restaurant cashier’s selection by using an access form. 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 in require the mentioned query 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

Here is my sample database for easy referencing and auditing

 

[Gasman]

Admittedly, you do have your onions with your beverages, but the tea is there?

 

[arnelgp]

see the code i added / removed from cmdItem_MouseDown event.
you add the "other" textbox values inside my code.

 

[nectorch]

see the code i added / removed from cmdItem_MouseDown event.
you add the "other" textbox values inside my code.

Sorry Sir;

I'm not able to see where you added the code I checked the attachment it has no new code

Regards

 

[Gasman]

I would restructure your labels, as I can see the confusion.
The menu items would be Lblnn
The category labels lblCatnn
where nn is it position in the label area.

Then the location would match the label number better?
In fact I would make the caption the label name?, as they get written over anyway?

 

[Gasman]

Sorry Sir;

I'm not able to see where you added the code I checked the attachment it has no new code

Regards

What do you call this then?

'arnelgp
With Me![sfrmRestaurantDetails].Form
    With .Recordset
        .AddNew
        !ProductName = Me!txtProductNames
        !SellingPrice = Me!txtPrice
        !QtySold = Me!txtQuantities
        .Update
        vBook = .LastModified
    End With
    .Bookmark = vBook
End With
    
DoCmd.SetWarnings False
'arnelgp
'DoCmd.OpenQuery "QryPosRestuarant"
'Me("sfrmRestaurantDetails").Form.Requery

 

[Gasman]

@nectorch posted this to my profile? seems same user as nectorprime

The issue when I click onions or traditional it gives an error , that is where the problem is

That is down to your label names not being consistent?
I suspect you obtained this code from somewhere and are attempting to modify it?
It relies on objects being named correctly and in their correct location.

 

[nector]

'arnelgp
With Me![sfrmRestaurantDetails].Form
With .Recordset
.AddNew
!ProductName = Me!txtProductNames
!SellingPrice = Me!txtPrice
!QtySold = Me!txtQuantities
.Update
vBook = .LastModified
End With
.Bookmark = vBook
End With

DoCmd.SetWarnings False
'arnelgp
'DoCmd.OpenQuery "QryPosRestuarant"
'Me("sfrmRestaurantDetails").Form.Requery

This is just a form filter code

 

[nector]

I would restructure your labels, as I can see the confusion.
The menu items would be Lblnn
The category labels lblCatnn
where nn is it position in the label area.

Then the location would match the label number better?
In fact I would make the caption the label name?, as they get written over anyway?

Ok let me just do that as per advice

 

[Gasman]

This user is changing usernames more often than I eat meals. In fact is here as 3 different users?????
One minute they are from the UK, but this user states they are from Zambia, as does the other alias, the DB appears to refer to Zambian VAT.
I am out of this thread.

 

[nector]

Ok let me try it

 

[Pat Hartman]

I don't know if this would work but it is worth a shot since it will save you a lot of work going forward if it does.

Rather than the hard coded boxes, use a listbox for the major categories and embed a subREPORT for the matrix. The subreport allows you to specify columns so you can just select all the beverages for example and they will get laid out across and down in alphabetical order if you sort them alphabetically. If you want to control the order, add a hidden field and sort by that instead of the name. That way you can group the coffees, the teas, etc which makes it easier for the person entering the order.

You don't have a lot of formatting options for a listbox but if you make the left panel a subform, you'll have more flexibility and you can make the whole thing look much like what you have now.

 

[nector]

Kindly note that its not like the entire database is not working , only 2 labels are not being written in the data sheet and that is the only help I need , I can easily use a combo as the last option to sort out this issue but the users prefer to have the selection populated on form. The controls affected are onions and traditional tea

 

[Pat Hartman]

The boxes are not labeled correctly. so anything past the fourth column has the wrong label number. Also only Beverage and Soft drinks worked initially. Now nothing seems to work. All the controls are locked.

Additionally, the subform CANNOT be set to Data Entry.

 

[isladogs]

Now crossposted at Re: How to populate all the selected label controls into the menu in Ms Access Form - Microsoft Tech Community

 

[moke123]

pfmji, I've never done a Pos system but looking at yours it seems to be overly complicated to me.

Why use X & y Positions and group numbers and mouse down events to find your items?
Wouldn't it be easier to just return the ItemRef and get tax and price, etc using that?

It seems similiar to building a calendar to me.

here's an example of what I'm talking about

 

[Pat Hartman]

I agree with the others. Just assign each item to a row/column. You can use buttons named appropriately and use the item name as the caption and the ID as the tag so you have a direct key back to the table.