1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

MS Access: Updating Data In One Field Should Update Data In All Relevant Fields

Discussion in 'Business Applications' started by fkk, Feb 21, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. fkk

    fkk Thread Starter

    Joined:
    Feb 21, 2009
    Messages:
    7
    I am an absolute begineer in MS Access. Will try to put my question as simply n clearly as possible.

    I have designed a database to keep history of our company's projects. The 'Master Project History Table' contains fields including 'Project Ref', 'Project Title', 'Project Budget' and 'Project Year'. The users are expected to populate the table through 'Master Project HistoryForm'.

    The key field is set as 'Project Ref'.

    Some of the projects are combined with each other and to display this info, 'Master Project History Form' also has the fields 'Project Ref-1', 'Project Title-1', 'Project Budget-1' and 'Project Year-1'.

    I am loking for a code for the ('On Update' event) that will fetch and display data in 'Project Title-1', 'Project Budget-1' and 'Project Year-1' fields when 'Project Ref-1' is updated. Please note that all this info is coming from the same 'Master Project History Table'.

    My IT guys told me to use a code with variable for each field that goes something like this (to update Project Title-1 field):

    Dim varX As Variant

    varX = DLookup("[Project Title]" , "Master Project History Table" , "[Project Ref]=" & Forms![Master Project History Form]![Project Ref-1])

    Me![Project Title-1] = varX

    And then repeat for each other field by defining new variables.

    The above is not working giving syntex errors etc.

    Will appreciate help from fellow members.
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    fkk, welcome to the Forum.
    Your IT guys are obviously High Level Access Programmers and not very helpful for someone at your level of Access knowledge.
    First of all having fields with names like 'Project Ref-1' suggests that your Main Table is trying to do too much and that you need a sub-table.
    Is the data that is going to be stored in 'Project Ref-1' actually also stored in Project Ref as a Project in it's own right?
    What I don't understand is why if the 'Project Ref-1' etc are in the same table as the Project Ref and presumably the same record, they are not included in the Field list of the Form so that you can just have them displayed on the form anyway.
    If there is more than one associated Project they should be shown on a Subform on the Project's Mainform.
    Can you take a screenshot of the Table Relationships with all fields showing in each table and post it on here for us to look at?
     
  3. fkk

    fkk Thread Starter

    Joined:
    Feb 21, 2009
    Messages:
    7
    OBP - Thanx for reply.

    Am attaching the screenshot of the actual form after removing all irrelevant fields.

    My problem, stated in another way, is:

    When I update the Project Ref-1 field using the query therein, the data in Project Title-1, Project Location-1 and Project Year-1 should be updated automatically - all coming from the same parent table.

    In other words, the Company is handling say 5 projects at the moment that include PFE 42, PFE 44, PFE 45, PFE 47 and PIE 01. At the same time PFE 44 is combined (commercially and scope wise) with PFE 42. While on the record screen of PFE 42, I wish to enter PFE 44 in the Project Ref-1 field that should fetch the relevant data for its title, location and year.


    I would love to have a sub-table since sometimes upto 9 projects may be combined togather. Problem: being a beginner I did not know how to link the sub-table to main table so that updating data in the main table automatically updates the sub-table data since both the tables will be containing exactly the same info. Also, since the info is for display purpose only, with no further processing, thought it convenient to let it remain part of the main table.

    Am alos attaching the database in .zip format so that you can have a look at the actual structure, if possible.

    Looking forward to your reply!
     

    Attached Files:

  4. fkk

    fkk Thread Starter

    Joined:
    Feb 21, 2009
    Messages:
    7
    Attaching another image to clarify the problem - in pictorial way!!!!
     

    Attached Files:

    • Form.jpg
      Form.jpg
      File size:
      117.7 KB
      Views:
      248
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    I see what you are trying to do now and why it doesn't work.
    You do not need a dlookup to populate those fields, you just need to include them in the Combo's SQL and then use some very simple VBA to put the values in the fields.
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    OK, I have completed the Project Title field for you.
    I have added the required columns to the 2 Queries and the Combo.
    I have added the VBA to the Combo's "After Update" event procedure so that you can see how it works.
     

    Attached Files:

  7. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    In this version I have added a Combined Projects Table,Query and Form.
    The new form is now a Sub Form with the Main Form's ID linked to the MainProjectID and a Combo to select the SubProjects that go with this main project.
    You can now have as many Projects connected together as you like, however with this version ther is always a "Master" Poject that links them together, but only in one direction, i.e, the master lists all the sub projects. a Sub project won't list the current master or any of the other sub projects unless you make each one the master by selecting it on your main form and then selecting the others as sub projects .
     

    Attached Files:

  8. fkk

    fkk Thread Starter

    Joined:
    Feb 21, 2009
    Messages:
    7
    OBP - thanxxxxx a lot!

    I'll be very honest. I went ahead with the first simple vba method and applied it to get the data successfully. I did check the second one with sub-form, but that exceeds my requirement for this particular database.

    Am attaching the database again. The following code is now used for update event to populate all fields:

    Me.[Project Title-1] = Me.Text433.Column(2)
    Me.[Project Year-1] = Me.Text433.Column(3)
    Me.[Project Budget-1 (Approved)] = Me.Text433.Column(4)

    It works ok. The only problem - I have to click on the year ad budget fields to get the updated value unlike the Title field that appears instantly.

    What should I do to get all the values updated/appear at the same time?

    Regards
     

    Attached Files:

  9. fkk

    fkk Thread Starter

    Joined:
    Feb 21, 2009
    Messages:
    7
    Second problem (or probably the same stated differently):

    Using the code above updates the then values after clicking on each box one by one. However, if I navigate away from record X, update something in a record (say title, budget etc.) that is supposed to be combined with X, coming back to the X I still find the old values. These are updated only if I select the same ref. no. from Project Ref combo box and then click on each box as before.

    Any ideas???
     
  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Yes, you have been naughty :eek:, your fields aren't actually called
    Me.[Project Year-1]
    Me.[Project Budget-1 (Approved)]
    they are called
    Combo306 (is the Year)
    Text190 (is the Budget)

    In my example I changed the name to the same as the Control Source. ;)
     
  11. fkk

    fkk Thread Starter

    Joined:
    Feb 21, 2009
    Messages:
    7
    OPB. Thanks. That did the trick. I was putting the wrong field names.

    The second problem however remains.

    Using the code updates the then values. However, if I navigate away from record X, update something in record Y (say title, budget etc.) that is supposed to be combined with X, coming back to X I still find the old values. These are updated only if I re-select the same ref. no. from Project Ref combo box.

    Hope this will be the last issue for now!!!!
     
  12. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Yes they will be because you are storing the Actual Text in those fields, not references to them. That is why you need a separate the table & Subform that I posted.
    That only stores the ID numbers, so whatever changes you make to the data is always reflected in your Subform.
     
  13. fkk

    fkk Thread Starter

    Joined:
    Feb 21, 2009
    Messages:
    7
    OBP. Thanx a lot for your very very cordial help in this issue (y). My laptop crashed two days back hence the delay in posting.

    Though I've modified the form to work with update thru code for the time being, am learning to work with sub-tables/sub-forms now - that obviously is the more appropriate way out.

    Will be posting back again when successful - or when encounter a problem - whichever comes first :)
     
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/802904