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.

Solved: Access Auto-Populate a Field using a field from another table

Discussion in 'Business Applications' started by Looch, Jan 5, 2011.

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

    Looch Thread Starter

    Joined:
    Jan 5, 2011
    Messages:
    8
    I have a table titled time_slips used to track the amount of time an attorney spends with a member. In the time_slips table there is a field titled reference_id which is a combo box pulling it's displayed information from another table titled references. Also in the time_slips table is a field named details which is native to this table.

    My question is, can I have the details field auto-populate "no charge" when a certain reference id is selected (i.e. 300)?

    Thanks for any help you can provide.
     
  2. Sponsor

  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,613
    Looch, welcome to the Forum.
    Yes you can do this, but my question to you is, do you need to?
    it is one of the taboos of database design and operation to "duplicate" data unnecessarily.
    On simple technique would be to display both "Reference" and details (ie No Charge) in the Combo.
     
  4. Looch

    Looch Thread Starter

    Joined:
    Jan 5, 2011
    Messages:
    8
    It is something we would like to do, and it's not really duplicating any data - no charge is not listed any where in the database. Reference 300 is the only reference id that needs auto-populated. The remaining reference id's details will all defer between records. Never being the same.
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,613
    OK, no problem.
    Do you know how to find the Event Procedure of Form Controls (Fields, Combos etc) in design view?
    It just needs some simple VBA code in the Combo's "After Update" event procedure, like this

    if me.combo = 300 then me.details = "No Charge"
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,613
    You obviously need your actual Combo name where it says
    me.combo
     
  7. Looch

    Looch Thread Starter

    Joined:
    Jan 5, 2011
    Messages:
    8
    I am having trouble, the me.combo stands for just the combo name or both the combo name and form name?
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,613
    Just the combo name, like comb0 etc
    What is you combo called on the form?
     
  9. Looch

    Looch Thread Starter

    Joined:
    Jan 5, 2011
    Messages:
    8
    Reference_ID which is from the Reference table
     
  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,613
    OK, use this
    me.Reference_ID
    ensure that the there is an underscore between Reference & ID, if there isn't you would use
    me.[Reference ID]
     
  11. Looch

    Looch Thread Starter

    Joined:
    Jan 5, 2011
    Messages:
    8
    What field am I adding this to? Reference_ID or Details?
     
  12. Looch

    Looch Thread Starter

    Joined:
    Jan 5, 2011
    Messages:
    8
    When I add it to the Reference_ID field this is the error I get when I try to add a record using that reference id...

    "Microsoft Office Access can't find the macro 'if me.'
    the macro (or its macro group_ doesn't exist, or the macro is new but hasn't been saved. Note that when you enter the macrogroupname syntax in an argument, you must specify the name the macro's macro group was last saved under. "
     
  13. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,613
    Ok, that is why I asked if you knew about Event Procedures.
    The VBA code doesn't go in the Line that says event procedure, what you need to do is Cut that piece of code from the event line.
    Then click the drop down and Select "Event Procedure", 3 small dots will appear on the right hand side, click on the dots and it will open the VBA Editor at the Combo's "After Update" event procedure.
    paste the code between the 2 lines of code that are already there.
     
  14. Looch

    Looch Thread Starter

    Joined:
    Jan 5, 2011
    Messages:
    8
    I'm sorry, I misunderstood where I was putting that. It is now working and auto-populating just like I wanted!

    Thank you so much for your time!
     
  15. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,613
    My pleasure!
     
  16. 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/972852