How to filter Listbox (or switch rowsource) by checkbox selection

[mbedford]

I'm creating an asset/employee/everything including the kitchen sink database for my office.

Currently I'm working on formEmployee.

It contains a listbox which contains all the employees, and fields for the employee details which populate by the selection in the listbox.

The listbox (listEmployee) has a query rowsource (qryEmployees).

There is a checkbox (checkCurrent) that I want to control whether the listbox shows all the employee records or only shows the current employee records.

Ideally I'd like it to modify the query, but based on other info I've found, I'm currently using an AfterUpdate to modify the rowsource of the listbox to switch between two similar queries, qryEmployees which has all records, and qryCurrentEmployee which only has current employees (determined by a yes/no field in tblEmployees that is or is not filtered in the query.

The issue I'm having is the syntax of the If/Then statement. I'm not refering to the query correctly. See below:

Private Sub checkCurrent_AfterUpdate()
    ' Update listEmployee RowSource based on selection in checkCurrent
    ' to show all employees or only current employees
    If checkCurrent = True Then
    listEmployee.RowSource = qryCurrentEmployee
    Else
    listEmployee.RowSource = qryEmployee
    End If
 
End Sub
 

How should I refer to the queries to make this work?

And are there other suggestions for how I ought to do this better than I am?

I'm new to VBA, having squeaked past VB over ten years ago in junior college, and narrowly failed C. But my currenct position will benefit from my getting much better and more comfortable with VBA. So a final question is: What references do you prefer (websites, books, etc) for your own VBA knowledge?

[missinglinq]

You need to place your query names in double quotes! For example:

listEmployee.RowSource = qryCurrentEmployee

needs to be

listEmployee.RowSource = "qryCurrentEmployee"

Also, be sure to double check your query names. In your post you give qryEmployees, but in your code you use qryEmployee, without the ending s.

Welcome to Bytes!

Linq ;0)>

Addendum:

BTW, being rusty in Visual Basic is actually a good thing! Despite their common origin, straight VB and Access VBA are two different languages! They share many methods and functions, but this can be tricky. Many of the functions and some Properties (such as the Text Property for Textboxes) share a common name but perform differently.

The aforementioned Text Property in straight VB refers to the value of a Textbox on a Form, and is commonly used in VB.

In Access VBA, on the other hand, the Text Property refers to the actual content of a Textbox, when that Textbox has Focus! And this is not necessarily the value held by the field bound to that Textbox.

In Access VBA, we refer to the value of a Textbox by the...are you ready for this... by the Value Property!

So, within VBA code, you can refer to the value held by a Textbox, on the current record, whether the Textbox has focus or not by

Me.TextboxName.Value

or simply

Me.TextboxName

because Value is the Default Property of a Textbox and doesn't actually have to be written out.

But you can only refer to the Text Property by

Me.TextboxName.Text

and only when it currently has the focus.

[mbedford]

Thank you Linq. I'd found that naming discrepancy late yesterday.

Thanks for the tips regarding differences between VB and VBA as well as the other info.

...and it works!

So when I am updating a property, I should always send the new value surrounded by quotation marks?

[missinglinq]

@mbedford

So when I am updating a property, I should always send the new value surrounded by quotation marks?

Table and Query names always require Quotation Marks. They are, after all, strings!

Glad you got things working!

Linq ;0)>