Results 1 to 4 of 4

Linking Auto lookup Field values

  1. #1
    Crystan is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    19

    Linking Auto lookup Field values

    Hi there, I'm having some trouble getting this to work...

    I have a few tables with data that needs linking and I'm thinking its through either VBA or Queries, not entirely sure so wondering if you guys can help me.

    Here's a simplified description of the issue, I'll use this as an example:

    Table 1- (Goods In)
    ING_ID - ingredient ID (number)
    ING_NAME - Ingredient Name (short text)
    STOCK_ID - Stock ID (Autonumber)

    Table 2 - (Recipe)
    REC_ID - Recipe ID (Autonumber)
    ING_ID - Ingredient ID (number)


    ING_AMO - Amount required of each ingredient (number)

    Table 3 - (Collate)
    JOB_ID - This specific jobs batch code (Autonumber)
    REC_ID - Recipe selected from list - populates the next two fields on the form with the requirements
    ING_ID - Lists the ingredients used in this recipe
    ING_AMO - shows amount required per ingredient for batch
    ******* STOCK_ID - drop down list shows values per ingredient of its STOCK_IDs in stock currently

    Its the **STOCK_ID i'm having issues with. I need it to show the autonumber of the ING_ID in the list. Currently it seems to simply fill the entire list with the same number again and again. There is no filter?

    How can I filter it so each on ingredient stock ID, the drop down list shows only please?

    Screenshot provided - There is obviously more information than what I have listed here however for this exercise the above fields are only required.

    Thank you for your help!

    Click image for larger version. 

Name:	accessforum.png 
Views:	22 
Size:	42.0 KB 
ID:	39945

  2. #2
    Join Date
    Apr 2017
    Posts
    984
    What is connection between ingredient and stock? Can there be several stock ID's for same ingredient, and when yes, then why? The only reason for this I can think of is, that ingredients have a term of use, and stock id you use is really batch id for ingredient (i.e. batches o ingredient registered in storage t different dates have different batch/stock ID's).

  3. #3
    Crystan is offline Novice
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    19
    ok so on my db i have a goods in record that assigns an autonumber to goods when they come in. We have around 70 ingredients and deliverys twice a week. So for example, a bag of flour that comes in on Monday witha batch number of L9282, will get assigned an autnumber value "4111" for example. Then when we get the next bag of flour delivery in on Thursday, it may not be the same supplier batch code so to ensure we can trace ingredients correctly the bag of flour that comes in on Thursday will get the next autonumber as a stock ID... We log expiration dates, delivery dates and supplier batch codes and amounts with this information, but feel due to the different types of batch codes that come in, we should assign them a simple "Stock_ID" numberic code when collating. Its this stock_ID that will be put in the collation, enabling us to trace each ingredient correctly and accurately.

    The 2nd table refers to the recipe database. The ING_IDs are linked to the recipe. A recipe is given a code, and in each recipe we "built" it by adding the correct ingredient and its required amounts.

    The third table pulls the recipe data from the 2nd table, and allows us to record actual yields of ingredients used and "hopefully" the Stock_IDs used, but I can't get it preloading the correct Stock_IDs that correspond with the ING_IDs in the recipies list..

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,445
    First - you are misusing the Autonumber field type.

    AutoNumber
    ----------------
    Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key in a table.

    Also see:
    Autonumbers--What they are NOT and What They Are
    Microsoft Access Tables: Primary Key Tips and Techniques



    ************************************************** ************************
    You didn't show the relationship window, but from your description, you might need another table.
    Consider

    Table 1 tblIngredients
    IngredientID_PK (Autonumber)
    ING_NAME - (short text) - Ingredient Name (Flower, Eggs, Butter, Oil, etc)

    Table 2 tblStockDeliveries
    StockID_PK (Autonumber)
    IngredientID_FK (Long) Foreign key to Ingredient
    BatchNumber (Text)
    DeliveryDate (Date)
    ExpirationDate (Date)
    DeliveryAmount (Double)
    DeliveryUnits (Text) (Bag, Carton, Case, Pounds, etc)

    Table 3 - tblRecipes
    REC_ID_PK - Recipe ID (Autonumber)
    ING_ID_FK - Ingredient ID (number)
    ING_AMO - Amount required of each ingredient (number)

    Table 4 - tblCollate
    JOB_ID_PK - This specific jobs batch code (Autonumber)
    REC_ID_FK - Recipe selected from list - populates the next two fields on the form with the requirements
    ING_ID_FK - Lists the ingredients used in this recipe
    ING_AMO - shows amount required per ingredient for batch


    You might also need to add another field in either Table 3 or Table 4 to keep track of the Batch ID - for example if you needed to know which batch of flour was used in a specific Recipe.

    As I said, just something to consider.



    My $0.02.........
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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

Similar Threads

  1. Linking two tables to auto populate a field.
    By oollie2 in forum Database Design
    Replies: 9
    Last Post: 04-20-2018, 03:35 PM
  2. Lookup values not appearing in Lookup field?
    By dominover in forum Access
    Replies: 4
    Last Post: 03-05-2016, 05:01 PM
  3. Auto fill a field from multiple lookup
    By shahemaan in forum Forms
    Replies: 4
    Last Post: 06-13-2014, 04:00 PM
  4. Replies: 7
    Last Post: 05-31-2012, 02:25 PM
  5. Replies: 3
    Last Post: 01-27-2012, 06:45 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
  •  
Tech Forums: Microsoft Office Forums