Results 1 to 8 of 8
  1. #1
    ndbennett is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2017
    Posts
    18

    Selecting and Updating Existing Table Entry

    I am trying to create a database of office equipment and to whom each piece is assigned.



    I have the following tables created:
    1) Users
    2) Equipment type
    3) Equipment status (to record whether it is still in use etc)

    I then created another table, Equipment Master, where I record the individual pieces of equipment and their serial numbers, with Look-Ups to select one of each of the other three table entries.

    This all works fine, and I created the forms to allow me to add new entries to each of the above. But what I cannot find a way to do is to select an existing entry in the Equipment Master table and change any of the parameters, for example to indicate that a laptop has been reassigned to a different user.

    Any help would be much appreciated.

  2. #2
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Could you post your DB here for us to see?

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    You may find this data model helpful

    Getting your tables and relationships designed to match your business requirement is often the hardest part.
    Lookups at the table field level is not considered a good practice.

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I created the forms to allow me to add new entries to each of the above
    The form for equipment master had to be modified (or copied to a new version) to allow it to display and update existing entries. You do this by changing its DataEntry property to "No". You might have to tweak it a bit to enable searching, for example.

  5. #5
    ndbennett is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2017
    Posts
    18
    Apologies for the delay in responding to you all - been a hectic week.

    I will continue to use your guidance but for interest's sake I have uploaded the database as a zip file. If you take a look, please also feel free to point out rookie errors in general - I am sure there are many.

    Thank you very much.Equipment Database.zip

  6. #6
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    davegri_EquipmentDatabase.zip

    See attached. Added form frmEquipmentMaster that lets you assign all the related data via combo boxes.
    Also removed lookups from table fields and added referential integrity to the relationships.

  7. #7
    ndbennett is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Jul 2017
    Posts
    18
    Thank you so much! I really appreciate your help - I will study what you did and hopefully learn from it!

  8. #8
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Glad to help. Good luck with the rest of your project!

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

Similar Threads

  1. Selecting most recent entry in a table
    By mjwillyone in forum Programming
    Replies: 4
    Last Post: 01-04-2016, 09:54 AM
  2. Replies: 4
    Last Post: 02-05-2014, 04:35 PM
  3. Replies: 1
    Last Post: 09-18-2012, 05:10 PM
  4. updating existing report with new data in table
    By newtoaccess123 in forum Reports
    Replies: 2
    Last Post: 10-18-2011, 09:50 AM
  5. Updating an Existing Table
    By jo15765 in forum Access
    Replies: 2
    Last Post: 11-21-2010, 09: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