Results 1 to 5 of 5
  1. #1
    steveducraggin is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2020
    Posts
    2

    populating new table with duplicate data via a form

    HI,



    I'm sure this has been asked, and answered, numerous times already, but I will go through what I'm attempting:

    I have a reference table with two fields: product code (which is unique) and product description.

    What I want to do is populate another table with the product code (must allow duplicates) and the related product description via a form.

    i.e. open a form with various fields. Click on the product code to display a combo/dropdown box (I've tried using a combo) with all the product codes and associated descriptions, click on the relevant one, which then populates that Product code field and also the description field on the form. Because the log table I'm populating has many records with the same product code and description, plus other fields with unique serial number, date of purchase etc, I can't use the product code in this log table as a key as it has to be unique of course, though It can be in the reference table:

    So, the reference table has the following fields:

    Field type size
    Gelpadcode text 10 (unique item code
    Gelpaddesc text 60


    Log table to be populated:
    Gelpadcode text 10 (many duplicates)
    Gelpaddesc text 60 (as per reference table)

    plus......various other fields to be entered by the user on the form to populate the log table.

    I know the relationship is one to many, but just how to create a form/code to do this. I watched an excellent You Tube video on how to populate a training record based on an employee record but although works, only appears to be to create unique records in the table to be populated, i.e. one to one, as the key is the employee ID.


    Not sure if that makes sense, but please ask if you require more info.

    Much thanks in advance

    steve

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Then entry form has a combo box using a 2 column query of product codes. ProdCode,Description.
    set the combo properties:
    columns=2
    bound col.=1

    when the user picks the ProdCode,use code to fill in the ProdDescrip. Via the AfterUpdate event.
    Code:
    sub cboProdCode_afterupdate()
    txtDescrip= cboProdCode.column(1)
    end sub
    the description field is filled from the col.2.
    Note: in vb,columns begin with zero.

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    One to many relationships are usually represented with a Main form which has a subform within it. Data from the One side is shown on the Main from. Data from the Many side is shown on the subform.
    If you use the wizard to create the subform it will create the link between the forms for you.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Never duplicate same info in different tables!

    Let's say, you have a items table: tblItems: ItemID, ItemDescription, ... (ItemID is PK - usually an autonumeric field is used as one, but you can use the one generated by some code, or entered manually with UNIQUE property preventing double entries).
    And you need some log table to record some actions with those items. The log table will be like tblLog: LogID, LogDateTime, ItemID, ... (LogID will be definitely autonumeric, LogDate is entered automatically when record is created. Users can't edit any info after the log record is saved).

    Then you need a form linked with tblLog, where user can enter new log entries (and see previous ones). When a new record is activated, for combo box linked to field ItemID of tblLog editing is allowed. The RowSource of combo will be "SELECT ItemID, ItemDescription from tblItems ORDER BY 2", BoundColumn = 1, ColumnCount = 2, and ColumnWidths = "0,2.5". The combo allows to select from descriptions for all items. When you select an item, the combo's value is set to according ItemID.
    After you selected an item, editing is allowed for other controls except ones linked to ItemID and LogDateTime. When user attempts to move to another record, or commands Save, the BeforeUpdate event of form is triggered. The event sets the value for control linked to logDateTime to current datetime, and checks for any other required conditions, like item combo not value Null, UserID creating the entry entered into appropriate control, etc. When checks are passed, the record is saved, othewise Save is cancelled.
    NB! in log file, only ItemID is stored! Whenever you need additional info for item, you get it from tblItems!

    In case you want register log entries from some other form, you need to write a VBA procedure, which does all described above.

    Now about seeing item description instead of ItemID when looking about log info:
    In forms like log form, the combo I described above displays item description (and unless you set the width of 1st column > 0, hides ItemID, which is actual value in log table);
    For reports, you need to use queries as report source. A query like
    [CODE]SELECT it.ItemDescription, lt.LogDate, ... FROM tblLog lg INNER JOIN tblItems it ON it.ItemID = lg.ItemID/CODE]
    , and as follows a report based on it, returns item description instead of item id.

  5. #5
    steveducraggin is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Dec 2020
    Posts
    2
    Hi,
    Much thanks for all the responses, all assisted to various degrees. The solution was to remove the key field for the product code in the log table, as I need a one to many relationship, but keep it as the key in the product table, hence, one product code in the product table to many in the log table. I then used the same code technique as before (as per the youtube video on employee Id) and, bingo, I can now populate the log table with many of the same codes and descriptions. I did this on a test table to make sure I didn't delete any live data on the existing log table. Thanks all again for your advice regards Steve (now to break open a chilled bottle of cider to celebrate on this hot Aussie Friday Arvo!)

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

Similar Threads

  1. Replies: 8
    Last Post: 10-25-2019, 09:06 AM
  2. Populating table with data from the Web
    By ser01 in forum Programming
    Replies: 8
    Last Post: 06-15-2012, 08:27 AM
  3. Replies: 4
    Last Post: 12-18-2011, 05:55 PM
  4. Populating a form with table data!
    By Extracash in forum Forms
    Replies: 6
    Last Post: 09-13-2010, 05:47 AM
  5. Form data not populating in table
    By sabrown in forum Forms
    Replies: 0
    Last Post: 08-27-2009, 08:19 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