How to show all records on cascading CboBox if the other is blank?

[Galuppo]

Hi guys!

I have two cascading comboboxes.

The first is CboCompany, which is the list of all companies on record.

The second is CboEmployee, which is the list of the companies' employees.

I've ran the row source query, filtering the CboEmployee by the CboCompany on the criteria.

I would like to know how I can make CboEmployee to show all the employees of all companies if CboCompany is blank.

Thank you so much!

 

[arnelgp]

you change the rowsource of the cboEmployee, this can be done on the form's load event:

private sub form_load()
If trim(me.cboCompany & "")="" then
me.cboEmployee.rowsource = "select * from employee table;"
else
me.cboEmployee.rowsource = "select * from [employee table] where [company id] = " & me.cboCompany
end if
end sub

on the afterupdate of the cboCompany, you call the form's load event to update the cboEmployee recordsource:

private sub cboCompany_AfterUpdate()
call form_load
end sub

 

[CJ_London]

in your employee combobox rowsource put

select empPK, empName from [tblEmployees] where [CompanyFK] = nz([cboCompany],[CompanyFK])

and in the employee combobox on enter event put

cboEmployee.requery

 

[Galuppo]

in your employee combobox rowsource put

select empPK, empName from [tblEmployees] where [CompanyFK] = nz([cboCompany],[CompanyFK])

and in the employee combobox on enter event put

cboEmployee.requery

Hey, CJ

Could you tell me what empPK and CompanyFK refer to?

Thanks a lot!

 

[CJ_London]

you said

The second is CboEmployee, which is the list of the companies' employees.

You didn't provide the rowsource so I made some assumptions about what your table and fields were called.

 

[Galuppo]

you said

You didn't provide the rowsource so I made some assumptions about what your table and fields were called.

Yeah, my bad!

I meant which fields they refer to.

Is CompanyFK the CompanyID and EmpPK the EmployeeID?

Sorry again for not being clear!

 

[CJ_London]

yes - both in the employee table

 

[Galuppo]

you change the rowsource of the cboEmployee, this can be done on the form's load event:

Arnel, thank you so much for your help! The reason I used CJ's solution was because I'm not very familiar with the VBA implementation. Still a long way to go from my total noob status, haha!

I hope one day to reach a fraction of your knowledge!

yes - both in the employee table

CJ, I don't know what kind of wizardry that was, but it totally worked! Coming from intermediate Excel to Access is definitely a big jump for me and I'm still stumped by so much stuff, haha.

I appreciate the straightforward solution so that even a noob like me can understand. Your services are definitely invaluable for the community!

Overall guys, I appreciate everything you've done to help me!

Thanks a lot!

 

[CJ_London]

happy to help - good luck with your project