How to populate label controls on a form to act as a restaurant menu (1 Viewer)

nectorprime

New member
Local time
Tomorrow, 00:57
Joined
Oct 16, 2021
Messages
2
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.

Code:
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
 

Attachments

  • PosSales.accdb
    1 MB · Views: 259

Gasman

Enthusiastic Amateur
Local time
Today, 22:57
Joined
Sep 21, 2011
Messages
14,048
Admittedly, you do have your onions with your beverages, but the tea is there?
1634379148540.png
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 06:57
Joined
May 7, 2009
Messages
19,169
see the code i added / removed from cmdItem_MouseDown event.
you add the "other" textbox values inside my code.
 

Attachments

  • PosSales.accdb
    1.6 MB · Views: 485

nectorch

Member
Local time
Tomorrow, 00:57
Joined
Aug 4, 2021
Messages
41
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
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 22:57
Joined
Sep 21, 2011
Messages
14,048
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?
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 22:57
Joined
Sep 21, 2011
Messages
14,048
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?
Code:
'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

Enthusiastic Amateur
Local time
Today, 22:57
Joined
Sep 21, 2011
Messages
14,048
@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

Member
Local time
Tomorrow, 00:57
Joined
Jan 21, 2020
Messages
346
'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

Member
Local time
Tomorrow, 00:57
Joined
Jan 21, 2020
Messages
346
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

Enthusiastic Amateur
Local time
Today, 22:57
Joined
Sep 21, 2011
Messages
14,048
This user is changing usernames more often than I eat meals. In fact is here as 3 different users????? :mad:
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:57
Joined
Feb 19, 2002
Messages
42,976
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

Member
Local time
Tomorrow, 00:57
Joined
Jan 21, 2020
Messages
346
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
 

Attachments

  • PosSales.accdb
    2.1 MB · Views: 439

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:57
Joined
Feb 19, 2002
Messages
42,976
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.
 

moke123

AWF VIP
Local time
Today, 18:57
Joined
Jan 11, 2013
Messages
3,852
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
 

Attachments

  • PosSales.accdb
    556 KB · Views: 391

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:57
Joined
Feb 19, 2002
Messages
42,976
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.
 

Users who are viewing this thread

Top Bottom