Sub Form - Display Information

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

SlowHnds

Thread Starter
Joined
Mar 11, 2010
Messages
212
I am trying to build a database for the local Seniors Society which they will distribute to some of their members who require shopping services. So basically it's a pre-built grocery list all the client will eventually have to do is select the item enter the quantity and size and print the list/report to pdf and it will email it.

Where I'm stuck at the moment is in the sub-form for the shopping list OrderItems_FRM

When the item is selected I want the form to display what the item is ItemDescription

Tables in the Database

Table Name: Category_TBL
. CATID_FLD Autonumber Index
. Category_FLD
This holds information on the type of iteem, Ie. Produce, Dairy, Meat, Cheese, Baking, Canned Goods, Spices, etc

Table Name: Items_TBL
Field: CatID_FLD (Lookup must exist in Category_TBL)
Field: ItemId_FLD Autonumber Index Unique
Field: ItemDscp_FLD Text Description of the indiviual Item Ie. Fruit-Strawberries, Pork-Chop. Pork-Ground, Pork-Back Ribs, etc

Table Name: ShoppingList_TBL
Field: OrderNbr_FLD AutoNumber Index Unique It is the shopping order number
Field: OrderDate_FLD Date of the Order

Table Name: ShoppingListItems_TBL
Field:ItemNbr_FLD Autonumber Index Unique
Field: OrderNbr_FLD Number (this should autopopulate in the subform and not be visible
Field: ItemQTY_FLD Number Quantity Being Ordered
Field: ItemSize_FLD Text Size of the item being Ordered, 1 Ltr, 4 pack, 250 grams
Field: ItemNote_FLD Memo Any notes the client wants about the item. Specific brand or alternatives etc

On the OrderItems_FRM (the sub-form) isn't showing the item description. So I have not yet dropped it into the main form.

I have run into this problem of displaying data in another table before but again I don't design with access often enough to remember what the solution is. I think it has something to do with master - child but I don't recall what it is. Any help anyone can give on this would be greatly appreciated.

Database is attached in 2002-2003 MDB in zip.
 

Attachments

Joined
Jul 29, 2001
Messages
21,334
Why not use a cascading type of dropdown list. Select the category from the category dropdown and only items for that category show up in the next dropdown for items.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
The "OrderItems_FRM (the sub-form) isn't showing the item description" isn't showing because you are not referring to the description field correctly, it should be
=[ItemId_FLD].[column](1)

You also need to make the Combo wider and set it's first column width to 0cm, you would not then need the item description field at all.

Should there be a "Customer" table?
 

SlowHnds

Thread Starter
Joined
Mar 11, 2010
Messages
212
Thanks OBP I got that working now I knew it was something simple. The customer/client name will be hard written as the society deploys the database to individuals. So the report/shopping list shows the client name.

Rockn, if you could explain how to go about instituting that it does sound a better way to go.

Thanks for the help and ideas.
 
Joined
Jul 29, 2001
Messages
21,334
You will of course need to modify this to make it work the way you want. I just created a set of dropdowns and text boxes that are all unbound. I created a hard coded shopping cart number for the example, but you will have to figure a way to bring one in either by a customer table or some other manner so the shopping cart will be just for a specific person. Once the fields are chosen for category, item, quantity and size you click the add item button and it adds a new item to the list below. You can then print off a list any way you want.
 

Attachments

SlowHnds

Thread Starter
Joined
Mar 11, 2010
Messages
212
That is very ingenious. I didn't even know it was possible to do such a thing with unbound fields.

I know I can't do it or duplicate it. It gives me something to think about and look at and be jealous over.

I'm going to see if I can use my existing form & Sub-form and but in a filter to select items based on the category selected as an afterupdate event. I know it's possible I've seen some examples of it but I do not have the skill set to do it, VBA and/or Macros.

I'll post what I have now as soon as get a chance to run the conversion on my 2007 Database at home.
 

SlowHnds

Thread Starter
Joined
Mar 11, 2010
Messages
212
Attached is where I'm at now. This is in 2002-2003 from 2010 through 2007 and back

I have updated the order form to add an unbound combobox to use as a filter for items. However I can't get the filter to work, on update event, and I've removed it for now from the form. If you can see how to do it it would simplify the selection process which will be several thousands of individual items in perhaps a few dozen categories.

Please note that the categories and items are just test data for now and really don't mean anything, if I can get this database to work one of the local stores, we only have two grocery stores, are going to download to an excel file their full listing of items. We can then fix the listing they way we want it and load the categories and items from that listing pretty easily.

I do want to thank everyone for their time and assistance. While I haven't been able to implement all suggestions it continues to let me know all that I don't know. Your assistance is helping a service organization help others that could use a hand up.
 

Attachments

Joined
Jul 29, 2001
Messages
21,334
I don't think combo boxes will work on a continuous form like you are using and wanting it to function the way you are trying to get it to work. Every time a new entry is made it changes the first record to the same value as the new one. There is also no sub form on your main form.
 

SlowHnds

Thread Starter
Joined
Mar 11, 2010
Messages
212
With the subform not showing up must be something with conversion going from 2007 to 2010 to 2002, . When I just tried to go to 2002 from 2007 it says. "You cannot save this database in an earlier format, because it uses features which require the current file format."

If it's not possible to do in Access we (I) will either have to stick with the long route (unfiltered list) or use Excel and pick from the list and use Macro's to copy and email.

If I knew more and had better skill sets I'd try further in adapting your sample above but I've not been able to get that to work. Thanks for looking and all the suggestions.
 
Joined
Jul 29, 2001
Messages
21,334
It will work in Access and it is probably the best solution if you are going to distribute this to different locations. The only issue with that is they will all need Access installed on the computers at these locations.

If you look at the code behind the form I had created it is just an unbound form with some code for the button to populate the appropriate table. To make the whole thing work together in a really simple db would not be that hard to set up.
 

OBP

Joined
Mar 8, 2005
Messages
19,895
If Rockn can't help you any further I can assist you in Access 2003 if you are stuck.
Did you use a "Split" form in Access 2007/2010 as Access 2003 does not have that facility.
 

SlowHnds

Thread Starter
Joined
Mar 11, 2010
Messages
212
I've tried re-creating the form as you had it set in the version you did up.

I even tried saving as a 2007 database and exporting the form. I've tried just modifying your example. I am unable to make a go of it. My skill set is lacking. For instance I don't know how the data is going into the ListBox and showing up. I've looked at the code and it doesn't get into my brain. I can kind of see how it's getting into the order table.

I don't know how to clear the list for a new order etc.

As you can see I may know structure and forms. Somewhat. I'm totally out of my depth when it gets into code.
 
Joined
Jul 29, 2001
Messages
21,334
The only thing that is happening at the end of the onClick event handler for the button is that after the table gets updated the list box is re-queried or refreshed. It does take a while to figure out how to make things work together and I have found the easiest way to learn is to reverse engineer something and tailor it to what I need to do. If you would like me to create a very basic one with a client/customer input form and you can then base the shopping list off of the client displayed it really wouldn't take that much to do it.
 

SlowHnds

Thread Starter
Joined
Mar 11, 2010
Messages
212
That may be part of the issue for me then, in that it is not getting refreshed.

I am going to see if I can rework my database in 2010 to get it to save in 2002 then I'll work in 2002 to update the subform part. The one change is in the size as it's not a table but a text field where the client can enter large small medium 450 grams, 1 lb or 5Kg or 1 dozen and so on.

I'll get back to you on the offer. I'd really like to be able to learn how to do this by reverse engineering what you've given me so far.

But if anyone has any other possible solutions I'd be happy to look at them.
 

SlowHnds

Thread Starter
Joined
Mar 11, 2010
Messages
212
OBP

I used a form and subfrom.

When I view what I posted in 2002 - 2003 (using versions 2007 or 2010) it does view correctly so I'm not sure what has happened.

Now when I try to save it in 2002 - 2003 it gives me the error that it can't be saved in an older format.

Any help anyone can give in getting it to work correctly would be appreciated. It is close. Even if I could figure how to implement Rockn's unbound form to my present database that is a very possible solution. I need someway to limit/filter the Items based on the category selected, other wise the user can be indundated with 3800 possible items.

Rockn's solution is elegant and very close to what is needed. The form doesn't reset to blank, I need the field size to be a text box not a combo, and I need the order number to increment automatically. Probably a bunch of other stuff I haven't thought of yet.

Edit capability after entry? People do make mistakes and need the opportunity to correct them before emailing the list.

Like I say it's close and though I can see the end result I'm just not sure how to get there.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top