Displaying multiple records on one form

[Juicejam]

Plz help, forum, thanks in advance.

I am designing a restaurant kitchen display screen. I have a query structured from two tables, one with the order numbers and the other with the items in each order.

I would like the results of the query displayed in several list boxes (aiming for at least 12 to show current orders) on one single form to display all the current orders on a screen.

Is there any easy way to do this without code? I would need to refresh/requery the form to update the query results as new orders are taken.

Thanks a million...

[Juicejam]

Or is it easier to accomplish this with sub-forms???

[ketbdnetbp]

There are many different ways you can display such data. Much depends on who, what, where, when, why, and how? In order to narrow down your choices, a few questions need to be answered.

What is the size of the combined data fields you wish to display? Do you just want to concatenating fields from the query and just display a single string, line by line, in a field? How are your tables set up? List tables, fields, and relationships.

What would you like to have happen when orders exceed 12? What do you want to have happen when the opposite is true?

Who or what will trigger the requery, person taking the order (event), auto when new record added, etc.?

What happens when an order is completed before a new order is added? Further, how is a displayed order marked as completed and thus, not displayed any longer?

Who will be using the system, i.e. Manager/Supervisor or Line/Department Employees? One person or many?

Item modifiers (3rd Table?), or are they part of the original query?

As a veteran restaurant person, I admire your ambition.

Let me know the details and I'll try to help you the best I can.

All the best.

[Juicejam]

There are many different ways you can display such data. Much depends on who, what, where, when, why, and how? In order to narrow down your choices, a few questions need to be answered.

What is the size of the combined data fields you wish to display? Do you just want to concatenating fields from the query and just display a single string, line by line, in a field? How are your tables set up? List tables, fields, and relationships.

What would you like to have happen when orders exceed 12? What do you want to have happen when the opposite is true?

Who or what will trigger the requery, person taking the order (event), auto when new record added, etc.?

What happens when an order is completed before a new order is added? Further, how is a displayed order marked as completed and thus, not displayed any longer?

Who will be using the system, i.e. Manager/Supervisor or Line/Department Employees? One person or many?

Item modifiers (3rd Table?), or are they part of the original query?

As a veteran restaurant person, I admire your ambition.

Let me know the details and I'll try to help you the best I can.

All the best.

Wow, I am very impressed with your knowledge, Sir, both in Access and in the restaurant business!! Thanks so much for your interest. I will try to answer as many of your questions as I can.

Firstly, I am not looking to concatenate or combine data fields. I am already able to get the data I want in query form, it's just to display it now on a single form. I prefer to use list boxes to list all the items in each order. I would propose to do a requery action event every 10 seconds to refresh the page as new orders get added.

If the total uncooked/unserved/outstanding orders are less than 12, then those list boxes would be empty/blank on the screen. If the outstanding orders exceed 12, then I would want to be able to shift to another page to display those. I hardly expect it to exceed 12 though, or else I'll really have disgruntled customers on my hand.

The screen would be controlled by the kitchen manager via touchscreen or mouse click and once the order is completed, they would click a "completed" button to bump the order off the screen.

I am attaching a copy of the tables that comprise the query, modifiers do form a part of the query output.

To stress again, I would like the screen to show up to 12 current orders as follows:

Order No. 86754 Order No. 86755 Order No. 86756
1 Chicken fajita 1 Cheeseburger 1 Large Pizza
1 Cheesecake slice 1 Bcued Wings Pepperoni
1 Coke 1 Sprite Extra Cheese


Really hope you can help!!!!

[ketbdnetbp]

JuiceJam - I briefly took a look at your last post and, again, I wanted to praise your ambition and initiative. It's easy to see that you have spent a great deal of time and effort on this project. There are, however, a few areas which might need some scrutiny and, because it's a little complicated, I need some more time to digest that which you sent to me. I'll reply, in detail, as soon as I can. In the interim, all the best!

[Juicejam]

JuiceJam - I briefly took a look at your last post and, again, I wanted to praise your ambition and initiative. It's easy to see that you have spent a great deal of time and effort on this project. There are, however, a few areas which might need some scrutiny and, because it's a little complicated, I need some more time to digest that which you sent to me. I'll reply, in detail, as soon as I can. In the interim, all the best!

Thanks very much for the help, Sir. I appreciate it.

I believe I can manage the record actions (order bumping, requerying, etc) so I am really only focussed on how to display the query data in the format I desire.

Perhaps this may narrow your thinking and focus as well. I am attaching another pic, this time of the mock-up form I have put together (but without the bump button). Now it's just to get the data in the textboxes and list boxes.

Hope this helps you to suggest a solution as soon as you can. I;ve been battling with this for months now!!!

Thanks again..

[ketbdnetbp]

Juicejam - I had a chance to review the material that you posted. Unfortunately, I can't think of a way to accomplish your goal, without using VBA, in A2003. Other forum members may be able to offer suggestions but, I'm at a loss.

However, you may wish to consider the following because, in my opinion, you may be able to accomplish your goal. It does require some code.

Concept:
Pull needed data into a recordset. Use a VBA function that steps through the recordset, record by record, applies necessary logic, and then, writes data into an array. After cycling through the records, data from the array is pushed into pre-determined controls on the unbound form.

The function could be called in response to a variety of events. A timer, when a new order was added, when an order was changed, when an order was marked as complete, etc.

However, in your case, one drawback may be size. Size as it relates to the number of lines in the field that lists MenuItems and modifiers. Using your template and data examples, it doesn’t seem to be a problem. But, let’s say for the sake of argument, a party of twenty walks in the door, and each of them order three items, and some of those have modifiers, all the data in that particular field may not be visible, given the size of the field on your template, without using a scroll bar. You may be able to get around that by limiting the number of orders being displayed and increasing the vertical dimension of that field.

Because this technique is not new, you should be able to find a fairly large number of code samples in the public domain. Oftentimes, they can be copied and only need minor modifications to fit your circumstances. If you can’t find any, or you need help with the modifications, reach back out and I’ll be happy to help.

All the best !!!

[Juicejam]

THanks so much again, Sir!!

I know very little of VBA and nothing about arrays so if you could point me to a source example where similar code is used, I would be very grateful. I believe my basic knowledge of SQL and access may help me with the modifications but it is the array source I would need pointing to especially since I wouldn't know what I am looking for.

Thanks once again. I really need appreciate and need the help.

[ketbdnetbp]

Juicejam - Think of an array as just a container for data, much like a variable, however, it can contain a lot of data and handle multiple data types (Variant data type). It's dimensions control how much data it can hold.

For example:

A simple array -
arrFavoriteColor(1 to 3, 1 to 3)

(The dimensions are: (1 to 3, 1 to 3), organization of the data is similiar to a speadsheet or grid. 3 Rows and 3 Columns, Spreadsheet- A1:C3)

Example Data/Recordset -
Person FC1 FC2 (Field Names)
John Green Yellow
Suzie Blue Red
Mary Brown Gold


arrFavoriteColor(1,1) = John (Row 1, column 1)
arrFavoriteColor(1,2) = Green (Row 1, column 2)
arrFavoriteColor(1,3) = Yellow (Row 1, column 3)
arrFavoriteColor(2,1) = Suzie (Row 2, column 1)
arrFavoriteColor(2,2) = Blue (Row 2, column 2)
arrFavoriteColor(2,3) = Red (Row 2, column 3)
arrFavoriteColor(3,1) = Mary (Row 3, column 1)
arrFavoriteColor(3,2) = Brown (Row 3, column 2)
arrFavoriteColor(3,3) = Gold (Row 3, column 3)

So, just for the sake of argument, I opened up the above recordset, went to the first row, and, using an expression, put "John" into arrFavoriteColor(1,1). Then, at a later time, if I wanted to set the value of a control on another form to "John", my expression may look something like: [Forms]![MyForm]![FName] = arrFavoriteColor(1,1) or Me![FName] = arrFavoriteColor(1,1).

This should give a starting point and food for thought. I'll search around in my code and see if I can find something applicable to your project.

All the best !

[Juicejam]

Hello Sir, quick thought I've had on all this. If I am able (and I will admit I don't know how to at this point) to populate up to 12 unbound text boxes from an array showing 12 open order id's, couldn't I then simply populate the 12 listboxes using queries pointing the textbox contents as a criteria?

The listboxes have scrollbars so the number of items in one order would not be a problem.

In effect, I am distilling my request/problem now to simply populating 12 unbound textboxes on one form with data from the query. Once I have these populated, I believe I can requery the form to update each listbox.

I have no clue how to get this done using arrays nor how to keep the data refreshed as new orders are added or old ones are bumped off as cooked.

Hope this makes it easier????? Thanks again for all the help...

[Juicejam]

Attaching the excel file. Pls see other attachments as well.

Thanks much

[ketbdnetbp]

Got the file. I'll take a look and get back to you as soon as I can.

[Juicejam]

Am still struggling with this project.... someone plz help.