List box Form Criteria

[Build or Die]

I've tried to do my pre-post homework but I'm coming up short....

(Access 2000 by the way)

I have a main form (frmCalendar) with two multi-select list boxes to
dynamically populate the subform(s). There are two hidden textboxes
(Department and Event) that hold each of the 'items Selected' from each list
box. These text boxes are then refered to in the subform query's criteria.
Here's the problem: If type in the Design mode of the query's criteria for
Department '2 or 3' and the same for Event '2 or 3', the query runs just
fine. However if I have the same info placed into the related text box and
refer to the text box content for the query criteria it doesn't work.

I've seen a post about generating sub-querys for the criteria... so I tried
to insert the dynamic sub-query text into the text box and refer to the text
box containing the sub query and that doesn't seem to work either.

Am I not allowed to refer to a textbox for criteria?

Here's my code:
each list box's AfterUpdate:

Dim i As Variant
Dim DEPTcriteria As String
Set frm = [Forms]![frmcalendar]
Set ctl = frm![DepartmentSelect]
DEPTcriteria = "Select * from tblschedule where [department]="
For Each i In ctl.ItemsSelected
DEPTcriteria = DEPTcriteria & ctl.ItemData(i) & " OR [Department]="
Next i

'trim end
DEPTcriteria = Left$(DEPTcriteria, Len(DEPTcriteria) - 17)

Me.DepartmentFilter = DEPTcriteria

SQL for subform:
SELECT tblSchedule.Date, tblSchedule.Department, tblSchedule.Event,
tblSchedule.ProjectID, tblSchedule.Time, tblSchedule.ScheduleItemID,
tblLookupProjectDepartment.DepartmentABRV, tblLookupEvent.EventABRV,
tblSchedule.Notes, tblSchedule.Completed, tblSchedule.Employee, (select
left(firstname,1) & left (lastname,1) from [tblemployees] where
tblschedule.[employee] = tblemployees.[employeeid]) AS EmployeeInitials1,
tblSchedule.Employee1, [devider] & (select left(firstname,1) & left
(lastname,1) from [tblemployees] where tblschedule.[employee1] =
tblemployees.[employeeid]) AS EmployeeInitials2, tblSchedule.Employee2,
[devider2] & (select left(firstname,1) & left (lastname,1) from
[tblemployees] where tblschedule.[employee2] = tblemployees.[employeeid]) AS
EmployeeInitials3, tblschedule.ProjectID & " " & " " & tblproject.ProjectName
AS ProjectNameandNumber, [departmentabrv] & " " & [EventABRV] & " " &
[Location] & " " & [Employeeinitials1] & [employeeinitials2] &
[employeeinitials3] & " " & [NOtes] AS CalendarINFO, tblSchedule.Location,
tblSchedule.Devider, tblSchedule.Devider2
FROM tblProject RIGHT JOIN (tblLookupProjectDepartment RIGHT JOIN
(tblLookupEvent RIGHT JOIN (tblEmployees RIGHT JOIN tblSchedule ON
(tblEmployees.EmployeeID = tblSchedule.Employee2) AND
(tblEmployees.EmployeeID = tblSchedule.Employee1) AND
(tblEmployees.EmployeeID = tblSchedule.Employee)) ON tblLookupEvent.EventID =
tblSchedule.Event) ON tblLookupProjectDepartment.DepartmentID =
tblSchedule.Department) ON tblProject.ProjectID = tblSchedule.ProjectID
WHERE (((tblSchedule.Department) Like
[forms]![frmcalendar].[departmentfilter]) AND ((tblSchedule.Event) Like
[forms]![frmcalendar].[eventfilter] And (tblSchedule.Event)<>12))
ORDER BY tblSchedule.Department, tblSchedule.Event, tblSchedule.ProjectID,
tblSchedule.Time;

Thanks to all who put up with this ultra-long post!

 

[sanfu]

I've tried to do my pre-post homework but I'm coming up short....

(Access 2000 by the way)

I have a main form (frmCalendar) with two multi-select list boxes to
dynamically populate the subform(s). There are two hidden textboxes
(Department and Event) that hold each of the 'items Selected' from each list
box. These text boxes are then refered to in the subform query's criteria.
Here's the problem: If type in the Design mode of the query's criteria for
Department '2 or 3' and the same for Event '2 or 3', the query runs just
fine. However if I have the same info placed into the related text box and
refer to the text box content for the query criteria it doesn't work.

I've seen a post about generating sub-querys for the criteria... so I tried
to insert the dynamic sub-query text into the text box and refer to the text
box containing the sub query and that doesn't seem to work either.

Am I not allowed to refer to a textbox for criteria?

Here's my code:
each list box's AfterUpdate:

Dim i As Variant
Dim DEPTcriteria As String
Set frm = [Forms]![frmcalendar]
Set ctl = frm![DepartmentSelect]
DEPTcriteria = "Select * from tblschedule where [department]="
For Each i In ctl.ItemsSelected
DEPTcriteria = DEPTcriteria & ctl.ItemData(i) & " OR [Department]="
Next i

'trim end
DEPTcriteria = Left$(DEPTcriteria, Len(DEPTcriteria) - 17)

Me.DepartmentFilter = DEPTcriteria

SQL for subform:
SELECT tblSchedule.Date, tblSchedule.Department, tblSchedule.Event,
tblSchedule.ProjectID, tblSchedule.Time, tblSchedule.ScheduleItemID,
tblLookupProjectDepartment.DepartmentABRV, tblLookupEvent.EventABRV,
tblSchedule.Notes, tblSchedule.Completed, tblSchedule.Employee, (select
left(firstname,1) & left (lastname,1) from [tblemployees] where
tblschedule.[employee] = tblemployees.[employeeid]) AS EmployeeInitials1,
tblSchedule.Employee1, [devider] & (select left(firstname,1) & left
(lastname,1) from [tblemployees] where tblschedule.[employee1] =
tblemployees.[employeeid]) AS EmployeeInitials2, tblSchedule.Employee2,
[devider2] & (select left(firstname,1) & left (lastname,1) from
[tblemployees] where tblschedule.[employee2] = tblemployees.[employeeid]) AS
EmployeeInitials3, tblschedule.ProjectID & " " & " " & tblproject.ProjectName
AS ProjectNameandNumber, [departmentabrv] & " " & [EventABRV] & " " &
[Location] & " " & [Employeeinitials1] & [employeeinitials2] &
[employeeinitials3] & " " & [NOtes] AS CalendarINFO, tblSchedule.Location,
tblSchedule.Devider, tblSchedule.Devider2
FROM tblProject RIGHT JOIN (tblLookupProjectDepartment RIGHT JOIN
(tblLookupEvent RIGHT JOIN (tblEmployees RIGHT JOIN tblSchedule ON
(tblEmployees.EmployeeID = tblSchedule.Employee2) AND
(tblEmployees.EmployeeID = tblSchedule.Employee1) AND
(tblEmployees.EmployeeID = tblSchedule.Employee)) ON tblLookupEvent.EventID =
tblSchedule.Event) ON tblLookupProjectDepartment.DepartmentID =
tblSchedule.Department) ON tblProject.ProjectID = tblSchedule.ProjectID
WHERE (((tblSchedule.Department) Like
[forms]![frmcalendar].[departmentfilter]) AND ((tblSchedule.Event) Like
[forms]![frmcalendar].[eventfilter] And (tblSchedule.Event)<>12))
ORDER BY tblSchedule.Department, tblSchedule.Event, tblSchedule.ProjectID,
tblSchedule.Time;

Thanks to all who put up with this ultra-long post!

Yes, you can refer to a textbox for criteria. And the records (most likely
none) are being returned that match the Where conditions in the query.

I don't know if I can give you a solution, but I think I know why 0 records
are being returned.

I don't think you posted all of the list box's code in the AfterUpdate
event. There are two lines missing:

Dim frm as Form
Dim ctl as Control

BTW, you could have written

Set ctl = [Forms]![frmcalendar]![DepartmentSelect]

(it's less code and easier to read)

Lets say that three departments are selected in the list box: "Sales",
"Accounting" and "Marketing". when the code runs, by the time you get to the
last line of code

Me.DepartmentFilter = DEPTcriteria

the hidden textbox on the form, Me.DepartmentFilter, will contain something
like

"Select * from tblschedule where [department]= 'Sales' OR [Department]=
'Accounting' OR [Department]= 'Marketing'".


In the SQL for subform, the first part of the WHERE clause would look like

WHERE (((tblSchedule.Department) Like
[forms]![frmcalendar].[departmentfilter])

Replacing the reference to [forms]![frmcalendar].[departmentfilter] with the
string in the textbox, you get

WHERE (((tblSchedule.Department) Like "Select * from tblschedule where
[department]= 'Sales' OR [Department]= 'Accounting' OR [Department]=
'Marketing'".....

Obviously, this is not what you expect or want since there will not be a
department named like that.

One way to solve this might be to generate the record source for the subform
"on the fly" in the list box AfterUpdate event.

HTH