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.

if then macros

Discussion in 'Business Applications' started by JAMESMAINE, Aug 27, 2018.

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

    JAMESMAINE Thread Starter

    Joined:
    Aug 27, 2018
    Messages:
    2
    Good Morning! I have a database for work that uses a lot of macros (since I am not good at visual basic), and has lots of querys. I have several different companies, and hundreds of employees at each company. I am attempting to do the following:
    1-Use dropdown field on Access Form, listing company a, b, c, d (by name)
    2-based on the result from the above field, I would like to then be able to fill another field with the last name of the employee from that company.

    for example: if company=1, then only show all employees from company 1 (and be able to select the one I need), or if company =2, then only show all employees from company 2.

    Does that make sense? I have 4 companies, and have just been typing in the last names but feel it would be easier and faster to have drop downs to select.
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Yes it makes perfect sense, except you do not need a macro, you need some simple VBA.
    When you create the Drop Down Combo you base it on a query that uses the Form and Company field as a Query Parameter.
    You then have some simple VBA in the Company Field After Update Event
    me.comboname.requery
    where comboname is the actual name of your Employee Combo.
    You can also have the same VBA in the Form's on Current Event Procedure to ensure that the combo always reflects the Company in the record you select.

    ps it is 10:10pm here in the UK.
     
  3. Chawbacon

    Chawbacon

    Joined:
    Jul 9, 2018
    Messages:
    540
    First Name:
    Jack
    Hello James,

    If you would post a copy of your DB, with sample data only, we can better assist you with a specific solution.

    Thanks,
     
  4. JAMESMAINE

    JAMESMAINE Thread Starter

    Joined:
    Aug 27, 2018
    Messages:
    2
    Sorry for the delay in replying-I did what OBP suggested. I created a query, using Company Name, Last, First. I created the drop down combo on the access form. I now have a drop down combo box that shows everyone alphabetically from all 4 companies. I cannot get it limit the employees for company "1" when I select company 1 in my company dropdown.
     
  5. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    First of all the Employee combo query should have a criteria set in the CompanyID column.
    It should be written like this
    forms![formname]![companycomboname]
    where the formname is the name of your form and is the actual name of your company combo.
    That will limit the employees to the company selected in the form combo.

    Then in the Company combo you need to select the "After Update Event Procedure" and enter the following code.

    me.employeecomboname.requery

    where employeecomboname is the actual name of your employee combo.
    This will requery the employee when you select a new company.
     
  6. 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...
Tags:
Thread Status:
Not open for further replies.

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

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice