Results 1 to 5 of 5
  1. #1
    Mwhiterabbbit is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    8

    Arrow Combines cells and add object based on contents

    Hello,



    I am trying to build a recipe and ingredient database that also can create reports allowing the recipes to be used. However, I cannot make the transition from excel yet as I am running into problems. The appeal of access was that I can use the same form and template for everything and I could consolidate 4 spreadsheets into 1 database but I'm stuck now on what I thought would be the easiest part.

    Recipes are broken down into 3 or more steps with different ingredients in each step such like the following

    Code:
    Step          Ingredient           Description
    1               100                   Flour
    1               300                   Sugar
    2               129                   Eggs
    3               600                   Chocolate Chips
    3               650                   Milk Chocolate chips
    Ideally I want to combine the cells that say step 1 and replace with instructions that correspond to those steps. Or find a way to group the steps together so its clear these 5 ingredients are all in step 1. Below the 1's were replaced with Mix 30s High, 2 with mix 10s Low, and 3 with Mix 20s High Mix 10s Low.


    Code:
    Step               Ingredient           Description
    Mix 30s High          100                   Flour
                               300                   Sugar
    Mix 10s Low           129                   Eggs
    Mix 20s High          600                   Chocolate Chips
    Mix 10s Low           650                   Milk Chocolate chips
    The problem is that I do not know how to make it dynamic. Some recipes have 10 different ingredients in stage 1 while others have only 4. Currently I have 30 different ingredient spots available per recipe, with their item code, description, weight, and stage stored with them. Is there a way to dynamically replace the stage number by consolidating them together then adding the mixing instructions for that stage in their place? This way I can use only 1 report for the recipe when it comes time to retrieve it. Is what I am trying to do feasible? Or will I have to have several different reports for different sized recipes?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Database design does not sound normalized. "30 different ingredient spots" seems to indicate 30 fields. Instead of a field, each ingredient should be a record.

    Might find this of interest http://access.microsofttemplates.org...-template.html
    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
    Mwhiterabbbit is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    8
    Hello,

    I have 3 tables set up right now. I have a table for ingredients. Such as their number, name, where to buy, substitute if low, etc. A table for the actual recipe, that has all the ingredients, quantities, its own name, and steps. It could be something like choc fudge dough: simmer 20 lbs chocolate, add 3 lbs milk, sprinkle 6 lbs sugar... Then the last table is for use after its been prepared or more like the finished good or combining recipes together to make something such as add recipes 200, 300, and 500 together to make a pie, or chocolate chip cookies is recipe 210 that has bigger ball sizes and a different bake time than chocolate chip 200.


    The blank recipe report has a possible 30 ingredients spaces but if they are blank or empty then they are hidden.

    Below is the format I wish to use. I know how to add the space between the two steps but don't know how to automatically make that big procedure cell merge dynamically.
    Code:
    Click image for larger version. 
    
    Name:	Format.JPG 
    Views:	8 
    Size:	54.4 KB 
    ID:	21408
    I currently enter in recipes by entering in the step, ingredient #, then the weight on a separate form that is very utilitarian and wish to have them come back in a neater form with extra info from the other two tables. That part is easy but I'm having trouble grouping them together. The only thing I can think of, that I'm trying to avoid, is have 5 different groups of 30 blank spots representing possible steps that each ingredient can be. It would then be filled with VBA and if blank can be hidden or if in the wrong step can be hidden. It would be very cumbersome but i hope there is another way.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    I am not understanding structure of '30 ingredients spaces'.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    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
    Mwhiterabbbit is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2013
    Posts
    8
    hello,

    Please see attached DB below. I stripped most of the stuff out so its not that pretty. But you can see in the VBA code me trying to hide the empty ingredients spaces.

    The Add new Formula button opens up the entry page or just open FormulaEntering.

    FormulaPrint was the form I was working on before I realized it was to be a report. Report1 is the basic template also before I started to ask for help.



    Example DB.zip

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

Similar Threads

  1. Replies: 12
    Last Post: 07-07-2015, 08:12 PM
  2. Delete Contents of worksheet before export - xlWSh.Cells.Select
    By allenjasonbrown@gmail.com in forum Programming
    Replies: 1
    Last Post: 10-14-2012, 10:38 PM
  3. Replies: 6
    Last Post: 07-13-2012, 11:09 AM
  4. code to delete rows based of empty cells
    By jcbrackett in forum Programming
    Replies: 3
    Last Post: 03-05-2012, 02:27 PM
  5. Replies: 0
    Last Post: 03-29-2011, 04:11 PM

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