Results 1 to 5 of 5
  1. #1
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91

    Post New Database Design Concept & Forms Question

    I am starting a new project here and want to make sure I am on the right track with how I'm laying this out...

    The idea is to be able to track the ingredients that go into each recipe and easily track the costs as ingredient prices change.

    I plan to use a many-to-many relationship which will store a list of ingredients per product. Is this the right way to do something like this?

    What I'm trying to do now is to make a form based on the Recipes table where you can select an existing Product and add Ingredients to it... but I'm having trouble with this. Anyone have a resource for a similar database? I started by thinking one form with two subforms, one each for Ingredients and Products. I'm just kind of stuck on where to go from here.



    Click image for larger version. 

Name:	AccessQuestion.JPG 
Views:	24 
Size:	24.1 KB 
ID:	44964

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Would not have two subforms. Have main form bound to Products with subform bound to Recipes and a combobox to select ingredients. Use combobox NotInList event to add new ingredient 'on the fly' during data entry.

    Recipes database is a fairly common topic. Forum search has many returns. Start with https://www.accessforums.net/showthread.php?t=75334
    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
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91
    I downloaded that DB and took a look. It gave me some good ideas, but they are using lookup fields in their tables, which I will avoid. I actually started down that path before talking myself out of it.

    Here's where I'm really confused though. This is the Recipe subform. All of these fields are just numeric. I need to display the column from the related table that has the actual name of the foreign key... but I need to enter the numeric value into the field that its currently bound to, which is on this subform's data source. The Recipe table has no descriptive data on it. How can I have the combobox display the related descriptive name and yet enter just the numeric key into the field on the Recipes table?

    Click image for larger version. 

Name:	Rec Form.JPG 
Views:	19 
Size:	28.2 KB 
ID:	44965

  4. #4
    Pawtang's Avatar
    Pawtang is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2021
    Posts
    91
    Alright, alright... I used my head again... this is my first time using Access from home and not from work, where I'm full of coffee and being paid.

    Control source and row source for a combo box... just need to keep control source set to the right field and I can have row source pull from other table.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Congratulations for recognizing issues with lookups in table and avoiding. Yes, use multi-column combobox. This is designing lookup on form, not in table.

    Users do not need to even see ID fields.

    Good luck with your project.
    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. Basic concept of database
    By Vrbic00 in forum Access
    Replies: 2
    Last Post: 07-26-2016, 06:33 PM
  2. design concept
    By Jen0dorf in forum Access
    Replies: 1
    Last Post: 01-31-2016, 04:44 PM
  3. The concept of updating a database
    By hect1c in forum Import/Export Data
    Replies: 3
    Last Post: 11-16-2014, 08:45 PM
  4. Database design question
    By udigold1 in forum Database Design
    Replies: 3
    Last Post: 03-23-2012, 02:20 PM
  5. Concept Forms
    By pkstormy in forum Code Repository
    Replies: 0
    Last Post: 08-31-2010, 01:51 AM

Tags for this Thread

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