if then macros

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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.
 

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.
 
Joined
Jul 9, 2018
Messages
561
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,
 

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.
 

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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Top