Results 1 to 10 of 10
  1. #1
    Scyclone is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    51

    Issues with Subform Design

    Hello Everyone,

    I am having a bit of an issue trying to get an order form to function properly with a subform.

    I have several tables designed around various things, that will be utilized in an order.

    ProdList (Contains ID, ProductName, ProductPartNo, ProdDesc, BrandID, CategoryID)
    ProdBrand (Contains ID, BrandName)
    ProdCat (Contains ID, CatName)
    OrderHeader (Contains ID, CustID, DateMade, StatusID)
    OrderDetail (Contains ID, OrderID, ProdId, Qty, UMID)
    UnitsMeasure (Contains ID, UnitMeasure)

    What I am trying to create is an order form for multiple rows

    The user loads the customer information above and then enters in the items they wish to order

    [Qty of Item] [UnitMeasure *Dropdown*] [BrandName *Dropdown*] [CatName *Dropdown based on BrandName Selected] [ItemName *Dropdown based on CatName Selected*] [ProdPartNumber *Based on ItemName*] [ProdDesc *Based on ItemName*]

    ======



    Any thoughts on the best way to go about this and allow them to enter in new OrderDetail rows, while utilizing information across multiple tables?

    Thanks,
    Steven

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    There are free data models at
    http://www.databaseanswers.org/data_models
    that may be of some use to you.

    This one deals with products and Ordersand deliveries.
    http://www.databaseanswers.org/data_...ries/index.htm

  3. #3
    Scyclone is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    51
    From a database perspective, I am perfectly fine, my issue is how to get accesses' subforms to work properly around it.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Can you give a specific issue that we can (try to) work through to get a form to work?
    I don't have Acc2007, but can work with an mdb.

    There are free video tutorials at data pig.

    Here is one http://www.datapigtechnologies.com/f...subforms1.html

  5. #5
    Scyclone is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    51
    Basically, what I am trying to do is have a subform with a couple of fields listed, each of the fields are formatted in a certain way so that I can utilize information from them.

    Qty is a text field, which will save on the Order Detail table.

    Units of Measurement is a dropdown field (populated from the UM table) now this field will need to be saved on the Order Detail table but as the ID not the displayed name. Example - UM table has Case, Each, and lets say Gallon. If a user enters in a new item for the order and selects Case, I need to take and store the value as 1 on the Order Detail table (2 for each, 3 for Gallon).

    BrandName is a visual dropdown listing off the Brand Names from the ProdBrand table. It has no value to be stored on the Order Detail table but will need to be used for populating dropdowns.

    CatName is a visual dropdown listing off the Category Names from the ProdCat table that match the BrandName previously selected. For example, Brand A contains Cat 1A, 2A, 3A. While Brand B contains Cat 1B, 2B, 3B. If I select Brand A in the previous dropdown, I want to only see Cat 1A, 2A, 3A for this dropdown (Outside of a subform I have already gotten this to work via VB-script)

    ItemName is a visual and stored dropdown listing off the various items in the Category. The displayed value is the name of the item, however, it will need to store the ID of the item on the Order Detail entry. For example, Item A has an ID of 6 and belongs to Category 1A. So if the user previously selected Brand A and Category 1A, they should be able to select Item A off a dropdown list. When saving the order, it needs to enter the ID of 6 into the Order Detail table.

    Product Description is a non-editable text field that displays the Description of the product as provided by the selected Item A. For example: Once the user selects Item A, I want to display Item A's description (held on the same row as Item A in ProdList) on the following field. This value doesn't need to be saved anywhere.

    Hope this clarifies it a little.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    There are video tutorials here that may help.Good luck with your project.
    http://www.datapigtechnologies.com/f...subforms1.html
    http://www.datapigtechnologies.com/f.../subform2.html

  7. #7
    Scyclone is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    51
    Unfortunately, neither of those seemed to be what I am looking for. They are displays of data, I need entry of data on multiple rows (since the order doesn't exist till the user creates it, they would also need to be blank rows for entry), spanning information from multiple tables.

    Anyone else have any thoughts on the best way to do this?

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Here are more subform links:
    http://www.techonthenet.com/access/subforms/
    http://www.accessmonster.com/Uwe/For...Y-MODE-PROBLEM
    http://www.tek-tips.com/viewthread.c...1537435&page=6

    Can you provide a "dumbed-down" version of your database in mdb format? (no personal or confidential data)

  9. #9
    Scyclone is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    51
    Ill try to look into the links to see if they would work for me.

    Unfortunately, the way the database is set up, I cannot get an mdb for you. There is numerous interconnections, pre-populations with our sql database and to top it all off a lot of it is confidential as well .

    ====

    https://www.accessforums.net/forms/i...html#post82894

    The post above, #5, explains what I am trying to accomplish for this.

    I am reading across multiple tables to piece together a single row. That row must be blank (allowing for new order item entry) and be able to translate Brand A (BrandName) as 1 (BrandID) so that it can save properly on the entry. Just the same, I need Brand A to become 1 and then be used as criteria to populate the dropdown Category which runs off of a brand, then the item to go off of that even further.

  10. #10
    Scyclone is offline Advanced Beginner
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    51
    Well upside is, I have created a subform and so far have gotten it to work the way I need it, sort of. However, now I have a different issue. It appears that whenever I enter a value into a field, it applies the same value to every row in the subform, is there a way to prevent this?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Database Design/Report Issues
    By j2curtis64 in forum Access
    Replies: 15
    Last Post: 07-08-2011, 08:00 AM
  2. Tabbed SubForm Issues
    By Exwarrior187 in forum Forms
    Replies: 5
    Last Post: 03-01-2011, 11:04 AM
  3. Subform and calculations issues
    By rustyCrVx in forum Access
    Replies: 1
    Last Post: 05-02-2009, 03:37 PM
  4. Replies: 0
    Last Post: 03-11-2009, 11:40 AM
  5. Subform Issues
    By dromorkid in forum Forms
    Replies: 0
    Last Post: 11-24-2008, 11:35 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums