Populate unbound listbox with VBA

[usmcgrunt]

Ok, I have taken a swag a trying to populate a unbound listbox on a form with VBA. I'm here so it means that my swag didn't fair so well. My ultimate goal is to limit the records that show in a form based on a users access level that is stored in a hidden form.



I have figured out how to filter the actual records displayed in the form in vba using a where stmt in the DoCmd.OpenForm code but I cannot figure out how to do the same for the listbox. If you are curious, I use the list box as a means to quickly select a record in the form rather that use the navigation buttons.

In general terms I envision some vba code that goes something like this:

If userAccess < X THEN
display all records in the listbox.

ELSEIF userAccess = X THEN
display only records A,B,C in the list box

ELSE
display no records in the listbox

My test code is below. As expected MS ACCESS popups a big error message that reads "HA HA HA, you have no idea what you are doing; keep trying." For one I have no idea which event to put this code in. ON top of that I have no idea if the code below is even close to being right.

I have tried ON Open for the form: Compile error: expected: expression
I have tried BEFORE UPDATE for the listbox: same error.

Me.lbLessonList.RowSource = SELECT qryLessonCardData.LNumber, qryLessonCardData.LTitle _
FROM qryLessonCardData _
WHERE qryLessonCardData.Inactive = 0 _
AND qryLessonCardData.LDivision = 1 _
ORDER BY qryLessonCardData.LNumber;

Help?
Sean

[pbaldy]

The row source property is a string:

Me.lbLessonList.RowSource = "SELECT ..."