Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2010
    Posts
    11

    Unanswered: Make a auto-populate field in a form report back to the table

    I've been able to successfully get some of my form fields to autopopulate from choosing a selection from a combo box. (ie: ProductID1 pulls info from the inventory table where the primary key is the ProductID)

    So on my Sales Order Form I have a place for 10 products. (ProductID1 - ProductID10)

    To pull the description (column(1)), and the Sale Price (column(2)), I did the whole...[ProductID1].column(1) to pull the description based on the ProductID that was selected for ProductID1 and then [ProductID1].column(2) to pull the Sale Price based on the same info. SO...it's populating in the form. HOWEVER...it's not showing up in the table. Which in turn is making it not show up on my query. Which in turn is making it not show up on my report. SO...how do I get this information to report back to the table so that I can click a button and it will print my customized Sales Order with all of the Auto-Populated information there??

    PS...if you know of an easier way to do this, by all means PLEASE tell me!

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'm confused. It sounds like though you need to create a field in your table and then store the info from your combobox to that field in the table.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    If it is not showing up in the table, but it is showing up on the form. My guess is that the textbox that shows the SalesPrice is not Bound to the underlying table.

    But more than that, I believe you are making a "rookie" mistake. Typically a database that stores order information will have at least two tables related to an order. The first table stores the Order header information. This is information like the order number, the date the order was placed, who took the order, who the order is for, when the order was shipped, and the status of the order (cancelled, shipped, returned).

    The second table is used for the line items related to the order (Order Details). In your case, what products were ordered, how much each product costs, how many of a product was ordered, and finally the OrderID that this detail relates to.

    For an order form, you would create a main form with all the Order Header information. Then a second subform for the Order Details. When you place the subform on the main form Access asks how the two forms are related. You would relate the two forms based on the OrderID field. Then Access does all of the work recording which detail record goes with which header record.

    The advantage of this approach is only using the space that is required to store and order. Your approach stores 10 items of data regardless of the number of items actually ordered. If someone only orders one product, your database is holding unused space for 9 more items (wasted space). Then if someone orders 11 items (and it will happen), you will have to resort to taking two orders for that customer. Not the worst thing in the world, but definitely not the best.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Good diagnoses DCKunkle!
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Feb 2010
    Posts
    11
    Quote Originally Posted by DCKunkle View Post
    If it is not showing up in the table, but it is showing up on the form. My guess is that the textbox that shows the SalesPrice is not Bound to the underlying table.

    But more than that, I believe you are making a "rookie" mistake. Typically a database that stores order information will have at least two tables related to an order. The first table stores the Order header information. This is information like the order number, the date the order was placed, who took the order, who the order is for, when the order was shipped, and the status of the order (cancelled, shipped, returned).

    The second table is used for the line items related to the order (Order Details). In your case, what products were ordered, how much each product costs, how many of a product was ordered, and finally the OrderID that this detail relates to.

    For an order form, you would create a main form with all the Order Header information. Then a second subform for the Order Details. When you place the subform on the main form Access asks how the two forms are related. You would relate the two forms based on the OrderID field. Then Access does all of the work recording which detail record goes with which header record.

    The advantage of this approach is only using the space that is required to store and order. Your approach stores 10 items of data regardless of the number of items actually ordered. If someone only orders one product, your database is holding unused space for 9 more items (wasted space). Then if someone orders 11 items (and it will happen), you will have to resort to taking two orders for that customer. Not the worst thing in the world, but definitely not the best.
    Well, I have never claimed to be an expert at this by any means lol. I am actually learning this as I go. Ok. So, I've already created this "rookie" form and it's going to have to do for now just so I can get something in the works until I am able to do the form in form thing. So...you said it looks like that field is not bound to the underlying table. I have a column in the table that is labeled ProductID1, ProductID2....-ProductID10 (and trust me, if these people order more than 10 items...OMG they've got some money cuz these products are approx $950-$2000 a piece!. Anyway. Then I've got the ProductDescr1, ProductDescr2....-ProductDescr10...as well as SellPrice1, SellPrice2...-SellPrice10. You get the idea.

    Well, when I create the ProductID1 - 10 boxes to be combo boxes that pull info from my Inventory table., then the control source for the coordinating ProductDescr(1-10) boxes and the SellPrice(1-10) boxes changes to " =[ProductID1].column(1) " Or whatever is needed to get that field to automatically pull the information from my Inventory table. Once I change that control source, it takes away the reference to the underlying SalesOrder table, so it doesn't populate the table...only the form. How do I make it to where it will pull the information as requested and send it back to the SalesOrder table?

  6. #6
    Join Date
    Aug 2005
    Posts
    5
    DCKnunkle

    I think what you described is exactly what I want to do.
    I have 4 tables now and think that I really only need 3 since it feels like I am duplicating data.

    The tables that I have are products, suppliers, materials, and contents.

    Products (name, sku(key), price, size, etc)
    Suppliers (name(key), phone, fax, email, address, etc)
    Materials (supplier_name, material, part_no, order_link(website link to order said part)
    Contents (quantity, sku, material, supplier, part_no, base_price)

    The way I have it set up is I have a Products form and Contents as a subform. This way the subform lists all the materials needed to make the product. The problem that I am having is that I want to be able to have a subform on the Products form that pulls from the Materials Table.

    Example:
    sku: 123-4 name: Bar soap price: 3000$

    Subform
    3 Cups Soapy stuff, Soapy stuff Supplier, ss1234, 3$ cup
    5 tsp Smelly stuff, Smelly stuff Supplier, ss5678, 10$ tsp
    1 Bar soap container, container supplier, bc91011, 1$ container

    Right now I am entering all this manually and creating records in the contents table linked by sku. I would think that I should be able to link the materials from the material table without creating new records?

    Since a lot of the products will be using the same materials I would like to be able to type in my quantity, select my material and it will populate the supplier and part number automagically.

    I think the concept on how to do it is escaping me but what you wrote sounded like what I wanted to do. If you could point me in the right direction I would appreciate it. I haven't messed with this stuff for YEARS and have minimal VBA experience.

    If I need to clarify something please let me know.

    Thanks,
    Brian

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •