Checkbox decides combo contents

[andy_dyer]

Hi,,

Hi,

I have a form with a list of staff in a combobox cboStaff which displays a list of staff based on a query "qryStaff"

SELECT tblStaff.ID, tblStaff.[User Name]
FROM tblStaff
WHERE (((tblStaff.Enabled)="enabled"))
GROUP BY tblStaff.ID, tblStaff.[User Name];

I want to now add a checkbox to my form "chkAllStaff" which when ticked causes the qryStaff to pick all staff and not just those with the enabled setting.

I've found that I'll need to add some afterupdate code to requery this after the checbox has been updated but can't work out what I need to do to my query to get this to work...

Apologies if this is in the wrong place couldn't decide if this was a forms or queries problem...

 

[Dennisk]

the easiest way is to create another query without the where clause.
The in after update event of the check box, test if true or not and set the record source of the form to the approriate query then requery the form.

 

[andy_dyer]

the easiest way is to create another query without the where clause.
The in after update event of the check box, test if true or not and set the record source of the form to the approriate query then requery the form.

Sorry... bit new to VBA...

Are you able to point in the right direction?

If I type me.cboStaff. it only gives me recordset and not recordsource as an option...

Told you I'm a bit new...

 

[DCrake]

Andy,

Here we go again. First Enabled is a bad field name as it also matches an Access Property. You need to brush up on naming conventions.

Your normal SQL for your combo box should be

"SELECT ID, [User Name] FROM tblStaff WHERE [Enabled]='enabled' ORDER BY [User Name];"

You do not need the group by as there should only be one of each member of staff.

Now if the user clicks on the checkbox you need to decide what to do in response to it being ticked or unticked (True or False)

Sub CheckBox OnClick()

Dim sSql As String

If Me.CheckBox = True Then
   sSql = "SELECT ID, [User Name] FROM tblStaff Order By [User Name];"
Else
   sSql = "SELECT ID, [User Name] FROM tblStaff WHERE [Enabled]='enabled' Order By [User Name];"
End If

Me.ComboBox.RowSource = sSql

EndSub

David

 

[andy_dyer]

Thank you David so much - sorry to have called on you so much in the last few weeks but you have been a life saver!

 

[DCrake]

No Probs, I have clients in your area so if you ever need any bigger/more complicated Access/VB issues visit my website for more info.

David