Displaying multiple records horizontally

[Juicejam]

I am novice access user (2003) but am generally well versed with extracting data in queries etc.



I am building a restaurant kitchen screen and need to show the top say 16 orders all on one screen but listed horizontally. the data is stored in at least 2 tables, one for order number, type, time of order etc and one with the order details (the kinds of food items). I dont have a problem with getting this data represented in a query. THat was the easy party.

Now to the hard part. I need to show the orders to be fulfilled by the kitchen across the screen, i.e. order 1, order 2, order 3 etc. so continuous forms do not work. I've also tried to use separate forms with subforms (datasheets) and use the GoToRecord command to go to a specific record in the query, e.g. form2 would show the second outstanding order, with a timer controlled Requery command to constantly refresh the forms to reflect the latest orders. I had a button on each form which when clicked would then say that the order was fulfilled and the Requery command would then refresh that form and load the next record in the query.

The problem i have encountered (apart from needing to position the 16 forms properly on the screen with the MoveSize command) is that Access is giving me error code 3048 Can't open any more databases.

Is there anyway around this or is there an easier way to get what I want accomplished say using listboxes? I have tried using one main form with 16 subforms but whenever i run the GoToRecord command it says the forms aren't open. Pls help me, I have been at this for months now trying to get this done.

Thanks a million..

[June7]

Displaying records in left-to-right (horizontal) arrangement discussed here https://www.accessforums.net/showthr...9062#post99062

[Juicejam]

Thanks very much, this gets me closer than all the other tips/suggestions I've received on this board.

I presume you are suggesting using the DLOOKUP function and not crosstab queries.

If so, then I have one additional question. This example returns the value contained in the LastName field of the first record in the Employees table: =DLookUp("[LastName]", "Employees")
Question is how do I return the second, third, fourth, etc record out of the query set?
My query returns the top 16 records.

[June7]

Domain aggregate function (DSum, DCount, DLookup) is only one possible approach out of at least 5. Here is #5 http://allenbrowne.com/func-concat.html

Don't know your data so can't say which is optimal.

The earlier referenced thread wasn't really talking about DLookup function. There was an aside comment about setting the lookup property in table fields.

[Juicejam]

Ok, I missed that. Not sure what I am to look for then as I thought the Dlookup sample was where you were pointing me, but here is a sample of my data:

Order No. Time Status Type
------------ ------ -------- -------
21342 1:32 0 Dine
21363 1:34 0 TO
21367 1:38 0 Dine
21369 1:40 0 TO

What I want to do is display these orders (at least one field from each record, preferably order no) horizontally on one form. I can then show the order details stored in another table utilising either an unbound listbox or a subform in datasheet format. I would also need some way of updating the status field of the order (preferably through a button click) to indicate that the order has been fulfilled.

Am I asking too much of Access?

[June7]

It's easy to show the records in conventional vertical arrangement. Horizontal can be done as already shown, just have to figure out method suitable for your data. It might involve an elaborate VBA procedure as evidenced by the referenced links or a series of queries, or a combination.

So with those 4 records, what should the output look like? How is this connected to getting Employee name? Show sample source and output data. Can type in post, or attach file (Excel or Access).

You want to show on form or report?

[Juicejam]

Ok here is a copy of the project file. I hope it attaches well. I have included the file where I used separate individual forms for each record. This is the file which kept coming up with the too many databases open error message but it gives you a fair idea of what I am trying to accomplish. To your question, there is no employee name, I was only using this as an example. The forms all have subforms which use child field links to another table with the order details.

I would like to display all the orders on one form but with the User interface as in the attached picture. The actual Mdb file is over 50 megs but i will try to get it down smaller so I can attach for you to look at the bits of code and queries in there.

I really appreciate your help!!!

[June7]

That looks like an image of data entry form. It doesn't give me any data to work with. However, all my direction has been with report output in mind. That you were saying 'form' didn't sink in till now. The data manipulation options I presented would result in a non-editable dataset.

I see now what you are trying to do for data entry. I am not sure binding 3 forms to the same table and opening them at the same time will work, never tried, and since you are encountering that error, indicates won't work. What you might need is multiple instances of the same form. Review this recent thread https://www.accessforums.net/forms/u...orm-20624.html

I have never done this either and don't know if it will work with bound form. The only alternative might be unbound form.

The db is split design? Have you run Compact & Repair?

[Juicejam]

Thanks June, I was a bit tied up this morning. I am attaching the actual mdb now. Can you please have a look? I actually was trying to open about 10 different forms at the same time then use the to gotorecord command for each then positioning them on the screen with Movetosize command.

If I get multiple instances of the same form displaying separate records then great. I dont mind using unbound forms as long as I can mark the status field of the order as fulfilled.

Thanks so much for your help.

[June7]

Interesting concept for a db, something new to me. Get it working and maybe could submit as template to MS, assuming there isn't one already out there that would serve, and I am not finding one.

I think the multiple instances approach would be more desirable than maintaining multiple copies of the same form structure. But in reading Allen Browne's tutorial I see major drawbacks for your situation.

When I open your forms I get an input prompt. What should I input? And which form should I open - FrmResized or FrmScreen?

[Juicejam]

Dont think there is anything or template out there. I searched high and low.

From your response I guess there is no easy way to crack this. I was hoping to use Dlookup to show each order no in text boxes horizontally on one form then show the order details in subforms or listboxes. What do you think?

I cant make heads or tails from Allen Browne's tutorial. I am a real novice in code.

[Juicejam]

open all the forms with name frmresized....

When I open your forms I get an input prompt. What should I input? And which form should I open - FrmResized or FrmScreen?[/QUOTE]

[June7]

How do I respond to the input prompts? Are those supposed to happen?

[Juicejam]

no, should not happen. open frmresize and then go to frmresize2 etc etc.

[June7]

Well, unfortunately I am getting prompts I don't know what to do with. The prompt says:

Enter Parameter Value
SELECT QryOpenOrder1.Item.name FROM QryOpenOrder WHERE (((QryOpenOrder1.menuitemid)=Form!FrmScreen!TxtIte mNo));

This happens with both FrmScreen and FrmResized.