Results 1 to 5 of 5
  1. #1
    Tom123 is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    11

    Using a single entry on a form to add two records

    Hi,



    I have two tables; one is called SERIAL NUMBERS and the other is called SN HISTORY.

    SERIAL NUMBERS has information on parts in an inventory with unique serial numbers which includes the S/N, description, and current location. It is important to know where the part is.

    SN HISTORY needs to track the location changes in the serial numbered part. The fields are S/N, date and location.

    For example if I have a part with the serial number KG005 and it's in Boston and then I send it to New York then I want the current location in the SERIAL NUMBERS to be New York and in the SN HISTORY table I want two records. The first says it was in Boston on date A and in New York on date B.

    I want to have a form for changing the location where you input the new location, date and serial number. The form would have to query the SERIAL NUMBERS table and look for the serial number that the user inputs. Then it would replace the current location in that table with the new location in the form. It would also add all of the things entered in the form as a new record in SN HISTORY. So my question is: is it possible to link one entry in a form to two fields in two different tables?

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    That sounds like a good approach. Have you created the table? Did you define the relations and key fields? I don't really see a question here. I would start with the table. I would then build a form that will populate the table, worrying how said form will interact with the app at a later date.

  3. #3
    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
    Is this an inventory tracking? You need to show reduction of inventory in Boston and the increase in New York? If this is not inventory of expendables, just tracking location of asset, then only need to input single record in SN HISTORY to document the new location and date of transfer. There should already be a record showing asset installed in Boston so therefore it must have come from Boston.

    Regardless, should not record the current location in SERIAL NUMBERS. The current location can be determined by pulling data from the SN HISTORY table. This can be done with a TOP N query http://allenbrowne.com/subquery-01.html#TopN or DLookup domain aggregate expression.

    Otherwise, code would have to run INSERT and UPDATE sql actions.
    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.

  4. #4
    Tom123 is offline Novice
    Windows 8 Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    11
    I figured out how to remove the current location from SERIAL NUMBERS. I just put a totals in the query and set it to display last. I would still like one form to input the serial numbers for the first time. How do I make one field put the same data in two records? I cant figure it out.

  5. #5
    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
    Input record in SERIAL NUMBERS and run code that checks for record in SN HISTORY, if none for that asset, then run INSERT sql.

    Or use form/subform arrangement.

    Enter new asset info on main form (SN, description, etc.)

    Then in the subform enter the location info.

    Can use this same form/subform to enter new location record for existing asset.
    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. Sorting records from a single-record form?
    By Ally1205 in forum Forms
    Replies: 2
    Last Post: 01-28-2014, 11:15 AM
  2. Replies: 3
    Last Post: 11-16-2011, 11:53 AM
  3. Replies: 11
    Last Post: 09-27-2011, 07:19 AM
  4. Create Multiple Records from Single Form
    By mcktigger in forum Forms
    Replies: 17
    Last Post: 09-15-2011, 11:07 AM
  5. Creating multiple records from a single form
    By secretary in forum Forms
    Replies: 8
    Last Post: 07-18-2011, 04:03 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