Checkbox to control list view using VBA

[gsandy]

I have a form (frmHourEnter) with a combo box (Staff_ID) that lists staff members from a table (tblStaff). The table form (frmStaff) has a checkbox (ckEmploy) which indicates if a staff member is still employed. If the checkbox is checked how do I exclude that staff member from the combo box Staff_ID in the form frmHourEnter?
Thanks Sandy

 

[JHB]

By using a Where clause in the query.
http://office.microsoft.com/en-us/access-help/where-clause-HA001231526.aspx

 

[gsandy]

Followed the link, Thanks JHB. But I am still unsure of how I tie the code into the form with the checkbox to limit what is viewed. Cheers Sandy

 

[JHB]

Okay - then post your database with some sample data, + name of the form.
Zip it because you've not post 10 post yet.

 

[gsandy]

Hi JHB, file attached. Thanks for your help, Sandy

 

[JHB]

You need a "Yes/No" field in the staff table, (I have called it "NoLongerEmployed"), you also need to set the unbound checkbox controlsource in the form frmStaff to that ""Yes/No" field.
Set the combobox "Row source" in form (frmHourEnter) to :

SELECT tblStaff.StaffID, tblStaff.FirstName, tblStaff.LastName FROM tblStaff WHERE (((tblStaff.NoLongerEmployed)=False));

I've attached the database, so you can see the changes.

 

[gsandy]

That worked great, thanks.
Question - the form (frmStaff) has the checkbox (ckEmploy) and by ticking this checkbox the field (NoLongerEmployed) in table (tblStaff) gets ticked. How are the two linked? Cheers Sandy

 

[gsandy]

Hi JHB, I found the link in the Control Source of ckEmploy.
Thanks again Sandy

 

[JHB]

Exactly, the controlsource!
You're welcome, luck with it.