1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Sub Form - Display Information

Discussion in 'Business Applications' started by SlowHnds, Nov 29, 2011.

Thread Status:
Not open for further replies.
Advertisement
  1. SlowHnds

    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.
     

    Attached Files:

  2. Rockn

    Rockn

    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.
     
  3. OBP

    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?
     
  4. SlowHnds

    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.
     
  5. Rockn

    Rockn

    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.
     

    Attached Files:

  6. SlowHnds

    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.
     
  7. SlowHnds

    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.
     

    Attached Files:

  8. Rockn

    Rockn

    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.
     
  9. SlowHnds

    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.
     
  10. Rockn

    Rockn

    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.
     
  11. OBP

    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.
     
  12. SlowHnds

    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.
     
  13. Rockn

    Rockn

    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.
     
  14. SlowHnds

    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.
     
  15. SlowHnds

    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.
     
  16. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1028989

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice