Results 1 to 9 of 9
  1. #1
    tranquillity is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    4

    populate a field from multiple source fields using a combobox

    Hello,

    I am trying to create a database that is used to describe the equipment and piping (and their links) around a chemical plant.
    The desired output is a report table that contains the following data:
    Size Code Seq Spec From To
    100 PG 001 A1 Inlet 1 150-PG-002-A1
    150 PG 002 A1 Inlet 2 V-001 Separator
    150 PG 003 A1 V-001 Seperator Outlet 1
    50 PG 004 A1 150-PG-003-A1 Outlet 2
    50 PL 005 A1 V-001 Separator P-001 Pump
    50 PL 006 A1 P-001 Pump Outlet 3


    I have created two tables with the following data (to allow for other queries and sorting)



    Size Code Seq Spec
    100 PG 001 A1
    150 PG 002 A1
    150 PG 003 A1
    50 PG 004 A1
    50 PL 005 A1
    50 PL 006 A1

    Tag Seq Description
    V 001 Separator
    P 001 Pump

    What I would like to do is being able to populate the "from" and "to" fields using a combobox referencing either the lines or equipment tables or alternately entering in a value manually such as the "inlet" or "outlet" entries above. I have also attached a picture to illustrate the arrangement I wish to describe. Of course any changes made in the line or equipment tables should automatically be reflected in the "from" and "to" fields.
    I am new to MS Access and databases in general so hopefully I have explained my problem properly. Any help would be greatly appreciated.

    Click image for larger version. 

Name:	Link Schematic.jpg 
Views:	23 
Size:	113.1 KB 
ID:	13746

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Don't know how to interpret that schematic into a db structure.

    Not sure what your issue is. Using a combobox to select values from a 'lookup' table is basic Access functionality. If you want edits to the values to be reflected in all records, then just save record ID, not the actual text descriptor. This will not allow manual input of values since a record ID is required. This would require adding a new record to the 'lookup' table. Use the combobox NotInList event to accomplish. http://www.blueclaw-db.com/access_no...ed_example.htm
    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
    mrojas is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Sep 2013
    Location
    Concord California
    Posts
    72
    Database2.zipI've created what I understand the problem to be.

  4. #4
    tranquillity is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    4
    After a bit of searching I have developed a union query to combine the equipment and line lists into a useful list of connections. I can then select any of them using a combo box or input my own string. An issue I am having now is that after I have selected an entry using the combo box it appears highlighted and the "FromID" or "ToID" field changes as expected. However if I then type to change the entry it does not update "FromID" or "ToID" to a blank value unless I leave the record and return to it to make another change.

    It appears the "on not in list" event doesnt trigger after a valid selection has been made unless i leave the record and return to it to make the change.

    Find attached the database file I have created for reference.
    Attached Files Attached Files

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If entered item is not in the combobox RowSource, the NotInList event should be coded to open a form for entry of new record then requery the combobox and populate with the new value. That's what the referenced link demonstrates. However, in your case there are two source tables involved. How will the code know which to open?
    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.

  6. #6
    tranquillity is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    4
    June,

    Where I have manually entered data I do not necessarily want it to be stored in any table. Data entered manually should only be for nominal descriptors where a line is entering or leaving a system from outside the scope of the plant. If it is better, then I could potentially populate a third table with these other nominal descriptors and add that to the union query as well? If the user wants to refer to a line or equipment item within the scope then they should first create the record in the respective list and then refer to it.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I suggest you review database design concepts. I recommend this tutorial. It starts with a business description and some rules and leads you to a normalized database.
    http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip


    Last edited by orange; 09-13-2013 at 05:13 PM. Reason: additional info

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you don't care to save the new value to a source table and then have it be available as an item in the combobox RowSource, then set LimitToList to No and the RowSource is merely a list of suggestions and user can enter whatever they want.

    Not sure what you mean by 'nominal descriptors'. What is happening with the item selected/typed in combobox?

    As orange advises, you might benefit from a review of db design concepts.
    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.

  9. #9
    tranquillity is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    4
    After reading orange's link I have decided to try to write out the entire scope as an entity-relationship narrative in order to build up all the required tables and define their relationships before I proceed any further.

    The items that appear in the "To" or "From" fields only serve to inform the reader of the line list about the configuration of equipment around the plant and the general configuration of lines. I do not intend to have them serve any further function in the database. 'nominal descriptors' could be anything that does not belong in the equipment or line list. Examples would be in-plant equipment or lines that are outside the scope of the project and therefore will not appear in the equipment or line list, or plant boundary connections eg. "Tanker Loadout" or "Well Site #1". I have now decided that these should be included in a separate table to minimise the risk of spelling mistakes or duplicate entries that have been described slightly differently but actually mean the same thing.

    Currently, these lists are built up using spreadsheets with all the data entered manually. As a result, large lists become increasingly difficult and time consuming to check for errors and consistency. The aim of this database is to eliminate many of the consistency checks that require minimal thought by the checker, are tedious and are therefore not done correctly.

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

Similar Threads

  1. Replies: 1
    Last Post: 06-20-2013, 10:35 AM
  2. Replies: 1
    Last Post: 10-28-2012, 07:23 PM
  3. Replies: 8
    Last Post: 08-02-2012, 10:50 AM
  4. Populate a field based on combobox selection
    By rscott7706 in forum Access
    Replies: 5
    Last Post: 06-02-2011, 03:18 PM
  5. Replies: 16
    Last Post: 11-18-2010, 11:08 AM

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