Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2011
    Posts
    5

    Access 2007 - Creating New Records Based on Existing Records

    Hello All,

    Any help on this would be hugely appreciated as I am somewhat poking around in the dark right now. The goal of this form is to be able to create a new record with the following fields:

    UPC: ITEM NAME: MANUFACTURER: FORECASTED SALES:

    Now UPC, ITEM NAME, and MANUFACTURER are all just text fields basically, and will be manually input. However, FORECASTED SALES will be based on items that already have sales. I want to be able to display a table in a subform which I could check off items that I believe this new item will sell like, and then have Access sum the old sales and enter it into the FORECASTED SALES field.



    My biggest questions are:
    - How do I make my form capable of creating new records?
    - I already have my table in the subform, but how do I enable a user to check off items and have it sum sales?

    Again, any help or step towards a tutorial of any kind will be greatly appreciated! Thank you!

    -George

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Relational databases are not really intended for 'What If' analysis. Check this article: http://wiki.answers.com/Q/What_are_M...rge_data_files

    I suppose could be done. Do you really need to save a record with forecast value?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    Oct 2011
    Posts
    5
    Quote Originally Posted by June7 View Post
    Relational databases are not really intended for 'What If' analysis. Check this article: http://wiki.answers.com/Q/What_are_M...rge_data_files

    I suppose could be done. Do you really need to save a record with forecast value?
    Well basically we want to make it easy for a user to say "I have this new item, and I think it will sell as well as x,y, and z combined." So they check off x,y, and z from the table in the subform, and then save an Order/Forecast record which would read: UPC, DESCRIPTION, MANUFACTURER, QUANTITY (the total sales of x,y,z).

    They could then add more items later to this same order, which would also follow the same process.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    I think selecting subform records has big issues. Try using a multi-select Listbox instead. The items selected in the listbox could be the criteria for a SELECT aggregate query to return the sum of the selected products costs. Will require VBA code to construct the SQL statement. Check this http://access.mvps.org/access/forms/frm0007.htm

    The VBA could populate a textbox on form with the sum. User enters the other info.

    How many products would user have to choose from?

    What is purpose of the form? Enter new product or new order?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Join Date
    Oct 2011
    Posts
    5
    Quote Originally Posted by June7 View Post
    I think selecting subform records has big issues. Try using a multi-select Listbox instead. The items selected in the listbox could be the criteria for a SELECT aggregate query to return the sum of the selected products costs. Will require VBA code to construct the SQL statement. Check this http://access.mvps.org/access/forms/frm0007.htm

    The VBA could populate a textbox on form with the sum. User enters the other info.

    How many products would user have to choose from?

    What is purpose of the form? Enter new product or new order?
    Essentially this is an Order Creation form for new products.

    It should look similar to this pic I've attached:

    The search functionality is already built into the table I've put into the subform, simply because Access has this feature. The Lift Factor thing I will be doing later. But once they hit save I need a new Record in a table with: NEW UPC, NEW DESC, NEW MANF, HISTORICAL ORDER QTY

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Previous comments still apply.

    If you really must have choices appear as populated records and have a checkbox then requires a Yes/No field in table. The subform would have RecordSource of the products table. Problem with this is if there are multiple users. As this is a table all users see, any one user's edits of the checkboxes updates the table. Only one user at a time could use the table otherwise will be conflicts. Will still need an aggregate query or DSUM to determine sum of selected records.

    An option instead of the bound checkbox is a button. Click the button and code will add that record's price to a cumulative sum in another textbox or to construct a filter string that will be used in an aggregate query or DSUM to determine sum of selected records. How many products could user select? String could get long. Nothing to prevent user accidentally clicking same item more than once. Could have the selected items 'transferred' to a listbox so the selections can be verified then click button to get sum.

    Another alternative is an unbound form with maybe ten rows of controls (checkbox, combobox, textbox). The controls are also unbound. But combobox has RowSource of available products. Users select up to ten products as basis for forecast.

    As I said, selecting records has big issues.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Join Date
    Oct 2011
    Posts
    5
    Quote Originally Posted by June7 View Post
    Previous comments still apply.

    If you really must have choices appear as populated records then requires a Yes/No field in table. The subform would have RecordSource of the products table. Problem with this is if there are multiple users. As this is a table all users see, any one user's edits of the checkboxes updates the table. Only one user at a time could use the table otherwise will be conflicts.

    An alternative is an unbound form with maybe ten rows of controls (checkbox, combobox, textbox). The controls are also unbound. But combobox has RowSource of available products. Users select up to ten products as basis for forecast.

    As I said, selecting records has big issues.
    There would only be one user at a time for this anyway. And probably not more than 1-10 other items would be used for historical data. I was thinking, what about an OnDoubleClick Event, when they Double Click on the UPC in that table it would add a record to the Order Table with all the New info + the Historical UPC in the proper fields.

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    And you posted that just as I was editing my post with similar suggestion. However, I am not understanding something. Why would you want a new record for each selected item? I thought the selections were to determine the cost of another new product? You want a permanent record of the selections as documentation for the determined cost?

    Double click is not intuitive. Would need an instruction statement on form. A button's purpose is apparent and it's caption could be explanatory. Can be a button or a label with caption 'Click to Add cost'
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    Join Date
    Oct 2011
    Posts
    5
    Quote Originally Posted by June7 View Post
    And you posted that just as I was editing my post with similar suggestion. However, I am not understanding something. Why would you want a new record for each selected item? I thought the selections were to determine the cost of another new product? You want a permanent record of the selections as documentation for the determined cost?

    Double click is not intuitive. Would need an instruction statement on form. A button's purpose is apparent and it's caption could be explanatory. Can be a button or a label with caption 'Click to Add cost'
    Sorry for the confusion, this is not for calculating the new items cost, but rather how many of this new item we should order. Say we see this new widget and we expect that it would sell as well as these two old widgets we were selling, well then we would enter in the new widgets info, click on those two old widgets, and viola we've got an expected order quantity for the new widget.

    Most new items would only need one historical item to estimate order quantity with, but some require multiple. Say we used to sell a blue, red, and green t-shirt, but we found that customers did not care about color, so we switched to all Black t-shirts. Well that black t-shirt is expected to sell as well as all three combined.

    I know this is a hassle and it's much more confusing to try and type it all out online rather than in person. But even just discussing it with you has helped me sharpen my focus. Thank you!

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Sum of cost or quantity, really doesn't change the issue. So do you have enough to proceed and attempt something? When you have a specific problem to resolve, such as code syntax, post question.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 12
    Last Post: 08-30-2011, 03:36 PM
  2. check existing records
    By zul in forum Programming
    Replies: 2
    Last Post: 08-24-2011, 03:41 AM
  3. Adding records to existing table
    By Mclaren in forum Programming
    Replies: 5
    Last Post: 05-18-2011, 12:44 PM
  4. Populate existing records from Excel
    By oleBucky in forum Import/Export Data
    Replies: 2
    Last Post: 03-08-2011, 12:23 AM
  5. Cloning data to other existing records.
    By GraemeG in forum Programming
    Replies: 0
    Last Post: 02-16-2011, 11:53 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