Multi select List box to filter query for editing

[Guest]

I have been through all the posts in both Forms forums on this site looking
for my answer but don't seem to be finding it.

I have a Select Query that is being used in Datasheet, Edit mode.
I originally created a form called FMCHDETAILS that allows the user to:
1. Select CLientName from a combo list box (lstClient)
2. enter a date range using seperate date fields (between beginning date and
ending date)
3. Click on cmd button to run query.

the Criteria fields in the query are set to:
ClientName = [Forms]![fmchdetails].[lstClient]
Date = Between [Forms]![fmchdetails]![Beginning Date] and
[Forms]![fmchdetails]![Ending Date]

All is working fine, but now the user would like to select more than one
ClientName each time the query is run.

I know I have to change the combo box to a list box and change the
MultiSelect to Simple.

I found information on the forum relating to using a multi select list box
passing information to an underlying query and displaying the results as a
report (which I used for a report and it works great!)

But I can't find any information that works with passing the criteria from
my form to a query and displaying the filtered records for editing.

Can this be done?

Thanks!

 

[Guest]

You can build a filter from the items selected in the list box and apply it
to the query.

First remove the criteria in your query for the ClientName field.
Then add code similar to the following your cmd button on click event.
I'll assuming that the bound column for lstClient is text.

----------------------
Dim mFilter As String
Dim i As Variant
Dim c As Integer
c = 0
For Each i In lstClient.ItemsSelected
If c = 0 Then
mFilter = "'" & lstClient.ItemData(i) & "', "
Else
mFilter = mFilter & "'" & lstClient.ItemData(i) & "', "
End If
c = c + 1
Next i
mFilter = Left(mFilter, Len(mFilter) - 2)
mFilter = "ClientName in (" & mFilter & ")"
DoCmd.OpenQuery "queryname"
DoCmd.ApplyFilter , mFilter

 

[Guest]

I copied the code as you suggested (making appropriate field name changes)
and got a runtime 3075 error: Syntax Error in String in query expression
'ClientName in (False'3)'.

I am guessing this was because you assumed my List box was bound to the TEXT
column. I checked and it was bound to column 1 - ClientID. So I changed it to
column 2 which corresponds to the text field ClientName.

I then got this runtime error: 3075: Syntax Error in String in query express
'ClientName in (TCAPS'3)'. {TCAPS is a client name - had chosen 2 client
names JPEC and TCAPS}

When I get the runtime error and choose the DEBUG button it highlights the
last line in the code: DoCmd.ApplyFilter , mFilter

Any suggestions? What else would you need from me to help troubleshoot?

You can build a filter from the items selected in the list box and apply it
to the query.

First remove the criteria in your query for the ClientName field.
Then add code similar to the following your cmd button on click event.
I'll assuming that the bound column for lstClient is text.

----------------------
Dim mFilter As String
Dim i As Variant
Dim c As Integer
c = 0
For Each i In lstClient.ItemsSelected
If c = 0 Then
mFilter = "'" & lstClient.ItemData(i) & "', "
Else
mFilter = mFilter & "'" & lstClient.ItemData(i) & "', "
End If
c = c + 1
Next i
mFilter = Left(mFilter, Len(mFilter) - 2)
mFilter = "ClientName in (" & mFilter & ")"
DoCmd.OpenQuery "queryname"
DoCmd.ApplyFilter , mFilter
--------------------------------

I have been through all the posts in both Forms forums on this site looking
for my answer but don't seem to be finding it.

I have a Select Query that is being used in Datasheet, Edit mode.
I originally created a form called FMCHDETAILS that allows the user to:
1. Select CLientName from a combo list box (lstClient)
2. enter a date range using seperate date fields (between beginning date and
ending date)
3. Click on cmd button to run query.

the Criteria fields in the query are set to:
ClientName = [Forms]![fmchdetails].[lstClient]
Date = Between [Forms]![fmchdetails]![Beginning Date] and
[Forms]![fmchdetails]![Ending Date]

All is working fine, but now the user would like to select more than one
ClientName each time the query is run.

I know I have to change the combo box to a list box and change the
MultiSelect to Simple.

I found information on the forum relating to using a multi select list box
passing information to an underlying query and displaying the results as a
report (which I used for a report and it works great!)

But I can't find any information that works with passing the criteria from
my form to a query and displaying the filtered records for editing.

Can this be done?

Thanks!

 

[Guest]

The row source for the list box and its bound column and data type.
Also sql for the query that you open and the code in your cmd button's on
click event.

I copied the code as you suggested (making appropriate field name changes)
and got a runtime 3075 error: Syntax Error in String in query expression
'ClientName in (False'3)'.

I am guessing this was because you assumed my List box was bound to the TEXT
column. I checked and it was bound to column 1 - ClientID. So I changed it to
column 2 which corresponds to the text field ClientName.

I then got this runtime error: 3075: Syntax Error in String in query express
'ClientName in (TCAPS'3)'. {TCAPS is a client name - had chosen 2 client
names JPEC and TCAPS}

When I get the runtime error and choose the DEBUG button it highlights the
last line in the code: DoCmd.ApplyFilter , mFilter

Any suggestions? What else would you need from me to help troubleshoot?

You can build a filter from the items selected in the list box and apply it
to the query.

First remove the criteria in your query for the ClientName field.
Then add code similar to the following your cmd button on click event.
I'll assuming that the bound column for lstClient is text.

----------------------
Dim mFilter As String
Dim i As Variant
Dim c As Integer
c = 0
For Each i In lstClient.ItemsSelected
If c = 0 Then
mFilter = "'" & lstClient.ItemData(i) & "', "
Else
mFilter = mFilter & "'" & lstClient.ItemData(i) & "', "
End If
c = c + 1
Next i
mFilter = Left(mFilter, Len(mFilter) - 2)
mFilter = "ClientName in (" & mFilter & ")"
DoCmd.OpenQuery "queryname"
DoCmd.ApplyFilter , mFilter
--------------------------------

I have been through all the posts in both Forms forums on this site looking
for my answer but don't seem to be finding it.

I have a Select Query that is being used in Datasheet, Edit mode.
I originally created a form called FMCHDETAILS that allows the user to:
1. Select CLientName from a combo list box (lstClient)
2. enter a date range using seperate date fields (between beginning date and
ending date)
3. Click on cmd button to run query.

the Criteria fields in the query are set to:
ClientName = [Forms]![fmchdetails].[lstClient]
Date = Between [Forms]![fmchdetails]![Beginning Date] and
[Forms]![fmchdetails]![Ending Date]

All is working fine, but now the user would like to select more than one
ClientName each time the query is run.

I know I have to change the combo box to a list box and change the
MultiSelect to Simple.

I found information on the forum relating to using a multi select list box
passing information to an underlying query and displaying the results as a
report (which I used for a report and it works great!)

But I can't find any information that works with passing the criteria from
my form to a query and displaying the filtered records for editing.

Can this be done?

Thanks!

 

[Guest]

The row source for the list box:
SELECT tblclient.ClientID, tblclient.ClientName FROM tblclient ORDER BY
[ClientName];
Bound column is 2 = ClientName
data type: TEXT

SQL for the underlying query {named qryclienthours-detail}:
SELECT tblclient.ClientID, tblclient.ClientName, tblProject.[Project Name],
tblinput.Date, tblinput.[Start Time], tblinput.[Stop Time], Sum(([stop
time]-[start time])*24) AS [total time], tblinput.[Task Description]
FROM (tblclient INNER JOIN tblProject ON tblclient.ClientID =
tblProject.Client) INNER JOIN tblinput ON (tblProject.ProjectID =
tblinput.Project) AND (tblclient.ClientID = tblinput.Client)
GROUP BY tblclient.ClientID, tblclient.ClientName, tblProject.[Project
Name], tblinput.Date, tblinput.[Start Time], tblinput.[Stop Time],
tblinput.[Task Description]
HAVING (((tblinput.Date) Between [forms]![fmchdetails].[Beginning Date] And
[forms]![fmchdetails].[Ending Date]))
ORDER BY tblProject.[Project Name], tblinput.Date;

code in the cmd button's on click event:
Private Sub Command14_Click()
Dim mFilter As String
Dim i As Variant
Dim c As Integer
c = 0
For Each i In lstClient.ItemsSelected
If c = 0 Then
mFilter = mFilter = "'" & lstClient.ItemData(i) & "',"
Else
mFilter = mFilter & "'" & lstClient.ItemData(i) & "',"
End If
c = c + 1
Next i
mFilter = Left(mFilter, Len(mFilter) - 2)
mFilter = "ClientName in (" & mFilter & ")"
DoCmd.OpenQuery "qryclienthours-detail"
DoCmd.ApplyFilter , mFilter

End Sub

The row source for the list box and its bound column and data type.
Also sql for the query that you open and the code in your cmd button's on
click event.

I copied the code as you suggested (making appropriate field name changes)
and got a runtime 3075 error: Syntax Error in String in query expression
'ClientName in (False'3)'.

I am guessing this was because you assumed my List box was bound to the TEXT
column. I checked and it was bound to column 1 - ClientID. So I changed it to
column 2 which corresponds to the text field ClientName.

I then got this runtime error: 3075: Syntax Error in String in query express
'ClientName in (TCAPS'3)'. {TCAPS is a client name - had chosen 2 client
names JPEC and TCAPS}

When I get the runtime error and choose the DEBUG button it highlights the
last line in the code: DoCmd.ApplyFilter , mFilter

Any suggestions? What else would you need from me to help troubleshoot?

You can build a filter from the items selected in the list box and apply it
to the query.

First remove the criteria in your query for the ClientName field.
Then add code similar to the following your cmd button on click event.
I'll assuming that the bound column for lstClient is text.

----------------------
Dim mFilter As String
Dim i As Variant
Dim c As Integer
c = 0
For Each i In lstClient.ItemsSelected
If c = 0 Then
mFilter = "'" & lstClient.ItemData(i) & "', "
Else
mFilter = mFilter & "'" & lstClient.ItemData(i) & "', "
End If
c = c + 1
Next i
mFilter = Left(mFilter, Len(mFilter) - 2)
mFilter = "ClientName in (" & mFilter & ")"
DoCmd.OpenQuery "queryname"
DoCmd.ApplyFilter , mFilter
--------------------------------

:

I have been through all the posts in both Forms forums on this site looking
for my answer but don't seem to be finding it.

I have a Select Query that is being used in Datasheet, Edit mode.
I originally created a form called FMCHDETAILS that allows the user to:
1. Select CLientName from a combo list box (lstClient)
2. enter a date range using seperate date fields (between beginning date and
ending date)
3. Click on cmd button to run query.

the Criteria fields in the query are set to:
ClientName = [Forms]![fmchdetails].[lstClient]
Date = Between [Forms]![fmchdetails]![Beginning Date] and
[Forms]![fmchdetails]![Ending Date]

All is working fine, but now the user would like to select more than one
ClientName each time the query is run.

I know I have to change the combo box to a list box and change the
MultiSelect to Simple.

I found information on the forum relating to using a multi select list box
passing information to an underlying query and displaying the results as a
report (which I used for a report and it works great!)

But I can't find any information that works with passing the criteria from
my form to a query and displaying the filtered records for editing.

Can this be done?

Thanks!

 

[Guest]

I think I see your problem. It's in the if c=0 then...else statement.
Recopy it from my original reply and think it should work ok for you.

The row source for the list box:
SELECT tblclient.ClientID, tblclient.ClientName FROM tblclient ORDER BY
[ClientName];
Bound column is 2 = ClientName
data type: TEXT

SQL for the underlying query {named qryclienthours-detail}:
SELECT tblclient.ClientID, tblclient.ClientName, tblProject.[Project Name],
tblinput.Date, tblinput.[Start Time], tblinput.[Stop Time], Sum(([stop
time]-[start time])*24) AS [total time], tblinput.[Task Description]
FROM (tblclient INNER JOIN tblProject ON tblclient.ClientID =
tblProject.Client) INNER JOIN tblinput ON (tblProject.ProjectID =
tblinput.Project) AND (tblclient.ClientID = tblinput.Client)
GROUP BY tblclient.ClientID, tblclient.ClientName, tblProject.[Project
Name], tblinput.Date, tblinput.[Start Time], tblinput.[Stop Time],
tblinput.[Task Description]
HAVING (((tblinput.Date) Between [forms]![fmchdetails].[Beginning Date] And
[forms]![fmchdetails].[Ending Date]))
ORDER BY tblProject.[Project Name], tblinput.Date;

code in the cmd button's on click event:
Private Sub Command14_Click()
Dim mFilter As String
Dim i As Variant
Dim c As Integer
c = 0
For Each i In lstClient.ItemsSelected
If c = 0 Then
mFilter = mFilter = "'" & lstClient.ItemData(i) & "',"
Else
mFilter = mFilter & "'" & lstClient.ItemData(i) & "',"
End If
c = c + 1
Next i
mFilter = Left(mFilter, Len(mFilter) - 2)
mFilter = "ClientName in (" & mFilter & ")"
DoCmd.OpenQuery "qryclienthours-detail"
DoCmd.ApplyFilter , mFilter

End Sub

The row source for the list box and its bound column and data type.
Also sql for the query that you open and the code in your cmd button's on
click event.

I copied the code as you suggested (making appropriate field name changes)
and got a runtime 3075 error: Syntax Error in String in query expression
'ClientName in (False'3)'.

I am guessing this was because you assumed my List box was bound to the TEXT
column. I checked and it was bound to column 1 - ClientID. So I changed it to
column 2 which corresponds to the text field ClientName.

I then got this runtime error: 3075: Syntax Error in String in query express
'ClientName in (TCAPS'3)'. {TCAPS is a client name - had chosen 2 client
names JPEC and TCAPS}

When I get the runtime error and choose the DEBUG button it highlights the
last line in the code: DoCmd.ApplyFilter , mFilter

Any suggestions? What else would you need from me to help troubleshoot?

:

You can build a filter from the items selected in the list box and apply it
to the query.

First remove the criteria in your query for the ClientName field.
Then add code similar to the following your cmd button on click event.
I'll assuming that the bound column for lstClient is text.

----------------------
Dim mFilter As String
Dim i As Variant
Dim c As Integer
c = 0
For Each i In lstClient.ItemsSelected
If c = 0 Then
mFilter = "'" & lstClient.ItemData(i) & "', "
Else
mFilter = mFilter & "'" & lstClient.ItemData(i) & "', "
End If
c = c + 1
Next i
mFilter = Left(mFilter, Len(mFilter) - 2)
mFilter = "ClientName in (" & mFilter & ")"
DoCmd.OpenQuery "queryname"
DoCmd.ApplyFilter , mFilter
--------------------------------

:

I have been through all the posts in both Forms forums on this site looking
for my answer but don't seem to be finding it.

I have a Select Query that is being used in Datasheet, Edit mode.
I originally created a form called FMCHDETAILS that allows the user to:
1. Select CLientName from a combo list box (lstClient)
2. enter a date range using seperate date fields (between beginning date and
ending date)
3. Click on cmd button to run query.

the Criteria fields in the query are set to:
ClientName = [Forms]![fmchdetails].[lstClient]
Date = Between [Forms]![fmchdetails]![Beginning Date] and
[Forms]![fmchdetails]![Ending Date]

All is working fine, but now the user would like to select more than one
ClientName each time the query is run.

I know I have to change the combo box to a list box and change the
MultiSelect to Simple.

I found information on the forum relating to using a multi select list box
passing information to an underlying query and displaying the results as a
report (which I used for a report and it works great!)

But I can't find any information that works with passing the criteria from
my form to a query and displaying the filtered records for editing.

Can this be done?

Thanks!

 

[Guest]

I now see that I had mFilter in the 3rd line twice. I corrected, and
rechecked the entire code - It still does not work.

Still get the same error code, 3075 with this message: Syntax error in
string query expression 'ClientName in ('JPEC,'TCAPS)'. {JPEC & TCAPS are
the 2 client names that I chose for running the query}.

I do appreciate your help. Any other thoughts? I am beginning to think it is
not possible to use a multi select list box to display a query.

I think I see your problem. It's in the if c=0 then...else statement.
Recopy it from my original reply and think it should work ok for you.

The row source for the list box:
SELECT tblclient.ClientID, tblclient.ClientName FROM tblclient ORDER BY
[ClientName];
Bound column is 2 = ClientName
data type: TEXT

SQL for the underlying query {named qryclienthours-detail}:
SELECT tblclient.ClientID, tblclient.ClientName, tblProject.[Project Name],
tblinput.Date, tblinput.[Start Time], tblinput.[Stop Time], Sum(([stop
time]-[start time])*24) AS [total time], tblinput.[Task Description]
FROM (tblclient INNER JOIN tblProject ON tblclient.ClientID =
tblProject.Client) INNER JOIN tblinput ON (tblProject.ProjectID =
tblinput.Project) AND (tblclient.ClientID = tblinput.Client)
GROUP BY tblclient.ClientID, tblclient.ClientName, tblProject.[Project
Name], tblinput.Date, tblinput.[Start Time], tblinput.[Stop Time],
tblinput.[Task Description]
HAVING (((tblinput.Date) Between [forms]![fmchdetails].[Beginning Date] And
[forms]![fmchdetails].[Ending Date]))
ORDER BY tblProject.[Project Name], tblinput.Date;

code in the cmd button's on click event:
Private Sub Command14_Click()
Dim mFilter As String
Dim i As Variant
Dim c As Integer
c = 0
For Each i In lstClient.ItemsSelected
If c = 0 Then
mFilter = mFilter = "'" & lstClient.ItemData(i) & "',"
Else
mFilter = mFilter & "'" & lstClient.ItemData(i) & "',"
End If
c = c + 1
Next i
mFilter = Left(mFilter, Len(mFilter) - 2)
mFilter = "ClientName in (" & mFilter & ")"
DoCmd.OpenQuery "qryclienthours-detail"
DoCmd.ApplyFilter , mFilter

End Sub

The row source for the list box and its bound column and data type.
Also sql for the query that you open and the code in your cmd button's on
click event.

:

I copied the code as you suggested (making appropriate field name changes)
and got a runtime 3075 error: Syntax Error in String in query expression
'ClientName in (False'3)'.

I am guessing this was because you assumed my List box was bound to the TEXT
column. I checked and it was bound to column 1 - ClientID. So I changed it to
column 2 which corresponds to the text field ClientName.

I then got this runtime error: 3075: Syntax Error in String in query express
'ClientName in (TCAPS'3)'. {TCAPS is a client name - had chosen 2 client
names JPEC and TCAPS}

When I get the runtime error and choose the DEBUG button it highlights the
last line in the code: DoCmd.ApplyFilter , mFilter

Any suggestions? What else would you need from me to help troubleshoot?

:

You can build a filter from the items selected in the list box and apply it
to the query.

First remove the criteria in your query for the ClientName field.
Then add code similar to the following your cmd button on click event.
I'll assuming that the bound column for lstClient is text.

----------------------
Dim mFilter As String
Dim i As Variant
Dim c As Integer
c = 0
For Each i In lstClient.ItemsSelected
If c = 0 Then
mFilter = "'" & lstClient.ItemData(i) & "', "
Else
mFilter = mFilter & "'" & lstClient.ItemData(i) & "', "
End If
c = c + 1
Next i
mFilter = Left(mFilter, Len(mFilter) - 2)
mFilter = "ClientName in (" & mFilter & ")"
DoCmd.OpenQuery "queryname"
DoCmd.ApplyFilter , mFilter
--------------------------------

:

I have been through all the posts in both Forms forums on this site looking
for my answer but don't seem to be finding it.

I have a Select Query that is being used in Datasheet, Edit mode.
I originally created a form called FMCHDETAILS that allows the user to:
1. Select CLientName from a combo list box (lstClient)
2. enter a date range using seperate date fields (between beginning date and
ending date)
3. Click on cmd button to run query.

the Criteria fields in the query are set to:
ClientName = [Forms]![fmchdetails].[lstClient]
Date = Between [Forms]![fmchdetails]![Beginning Date] and
[Forms]![fmchdetails]![Ending Date]

All is working fine, but now the user would like to select more than one
ClientName each time the query is run.

I know I have to change the combo box to a list box and change the
MultiSelect to Simple.

I found information on the forum relating to using a multi select list box
passing information to an underlying query and displaying the results as a
report (which I used for a report and it works great!)

But I can't find any information that works with passing the criteria from
my form to a query and displaying the filtered records for editing.

Can this be done?

Thanks!

 

[Guest]

From the syntax error it looks like you haven't copied the concatenated
strings correctly. Here's the code again with explanation

Dim mFilter As String
Dim i As Variant
Dim c As Integer
c = 0
' For each item selected in list box lstClient
For Each i In lstClient.ItemsSelected
' If first selected item, enclose in single quotes
' and separate with comma and space
If c = 0 Then
mFilter = "'" & lstClient.ItemData(i) & "', "
Else
' Concatenate string adding next selection
' enclosed in single quotes and
' separated with comma and space
mFilter = mFilter & "'" & lstClient.ItemData(i) & "', "
End
c = c + 1
Next i
' Remove comma and space at end of string
mFilter = Left(mFilter, Len(mFilter) - 2)
' Complete string for filter to apply to query
mFilter = "ClientName in (" & mFilter & ")"
' Open query
DoCmd.OpenQuery "queryname"
' Apply filter
DoCmd.ApplyFilter, mFilter

I now see that I had mFilter in the 3rd line twice. I corrected, and
rechecked the entire code - It still does not work.

Still get the same error code, 3075 with this message: Syntax error in
string query expression 'ClientName in ('JPEC,'TCAPS)'. {JPEC & TCAPS are
the 2 client names that I chose for running the query}.

I do appreciate your help. Any other thoughts? I am beginning to think it is
not possible to use a multi select list box to display a query.

I think I see your problem. It's in the if c=0 then...else statement.
Recopy it from my original reply and think it should work ok for you.

The row source for the list box:
SELECT tblclient.ClientID, tblclient.ClientName FROM tblclient ORDER BY
[ClientName];
Bound column is 2 = ClientName
data type: TEXT

SQL for the underlying query {named qryclienthours-detail}:
SELECT tblclient.ClientID, tblclient.ClientName, tblProject.[Project Name],
tblinput.Date, tblinput.[Start Time], tblinput.[Stop Time], Sum(([stop
time]-[start time])*24) AS [total time], tblinput.[Task Description]
FROM (tblclient INNER JOIN tblProject ON tblclient.ClientID =
tblProject.Client) INNER JOIN tblinput ON (tblProject.ProjectID =
tblinput.Project) AND (tblclient.ClientID = tblinput.Client)
GROUP BY tblclient.ClientID, tblclient.ClientName, tblProject.[Project
Name], tblinput.Date, tblinput.[Start Time], tblinput.[Stop Time],
tblinput.[Task Description]
HAVING (((tblinput.Date) Between [forms]![fmchdetails].[Beginning Date] And
[forms]![fmchdetails].[Ending Date]))
ORDER BY tblProject.[Project Name], tblinput.Date;

code in the cmd button's on click event:
Private Sub Command14_Click()
Dim mFilter As String
Dim i As Variant
Dim c As Integer
c = 0
For Each i In lstClient.ItemsSelected
If c = 0 Then
mFilter = mFilter = "'" & lstClient.ItemData(i) & "',"
Else
mFilter = mFilter & "'" & lstClient.ItemData(i) & "',"
End If
c = c + 1
Next i
mFilter = Left(mFilter, Len(mFilter) - 2)
mFilter = "ClientName in (" & mFilter & ")"
DoCmd.OpenQuery "qryclienthours-detail"
DoCmd.ApplyFilter , mFilter

End Sub

:

The row source for the list box and its bound column and data type.
Also sql for the query that you open and the code in your cmd button's on
click event.

:

I copied the code as you suggested (making appropriate field name changes)
and got a runtime 3075 error: Syntax Error in String in query expression
'ClientName in (False'3)'.

I am guessing this was because you assumed my List box was bound to the TEXT
column. I checked and it was bound to column 1 - ClientID. So I changed it to
column 2 which corresponds to the text field ClientName.

I then got this runtime error: 3075: Syntax Error in String in query express
'ClientName in (TCAPS'3)'. {TCAPS is a client name - had chosen 2 client
names JPEC and TCAPS}

When I get the runtime error and choose the DEBUG button it highlights the
last line in the code: DoCmd.ApplyFilter , mFilter

Any suggestions? What else would you need from me to help troubleshoot?

:

You can build a filter from the items selected in the list box and apply it
to the query.

First remove the criteria in your query for the ClientName field.
Then add code similar to the following your cmd button on click event.
I'll assuming that the bound column for lstClient is text.

----------------------
Dim mFilter As String
Dim i As Variant
Dim c As Integer
c = 0
For Each i In lstClient.ItemsSelected
If c = 0 Then
mFilter = "'" & lstClient.ItemData(i) & "', "
Else
mFilter = mFilter & "'" & lstClient.ItemData(i) & "', "
End If
c = c + 1
Next i
mFilter = Left(mFilter, Len(mFilter) - 2)
mFilter = "ClientName in (" & mFilter & ")"
DoCmd.OpenQuery "queryname"
DoCmd.ApplyFilter , mFilter
--------------------------------

:

I have been through all the posts in both Forms forums on this site looking
for my answer but don't seem to be finding it.

I have a Select Query that is being used in Datasheet, Edit mode.
I originally created a form called FMCHDETAILS that allows the user to:
1. Select CLientName from a combo list box (lstClient)
2. enter a date range using seperate date fields (between beginning date and
ending date)
3. Click on cmd button to run query.

the Criteria fields in the query are set to:
ClientName = [Forms]![fmchdetails].[lstClient]
Date = Between [Forms]![fmchdetails]![Beginning Date] and
[Forms]![fmchdetails]![Ending Date]

All is working fine, but now the user would like to select more than one
ClientName each time the query is run.

I know I have to change the combo box to a list box and change the
MultiSelect to Simple.

I found information on the forum relating to using a multi select list box
passing information to an underlying query and displaying the results as a
report (which I used for a report and it works great!)

But I can't find any information that works with passing the criteria from
my form to a query and displaying the filtered records for editing.

Can this be done?

Thanks!

 

[Guest]

THANK YOU, especially for being patient! It is working correctly now.

I guess I just needed to rest my eyes from it for a bit and your
explanations helped.

From the syntax error it looks like you haven't copied the concatenated
strings correctly. Here's the code again with explanation

Dim mFilter As String
Dim i As Variant
Dim c As Integer
c = 0
' For each item selected in list box lstClient
For Each i In lstClient.ItemsSelected
' If first selected item, enclose in single quotes
' and separate with comma and space
If c = 0 Then
mFilter = "'" & lstClient.ItemData(i) & "', "
Else
' Concatenate string adding next selection
' enclosed in single quotes and
' separated with comma and space
mFilter = mFilter & "'" & lstClient.ItemData(i) & "', "
End
c = c + 1
Next i
' Remove comma and space at end of string
mFilter = Left(mFilter, Len(mFilter) - 2)
' Complete string for filter to apply to query
mFilter = "ClientName in (" & mFilter & ")"
' Open query
DoCmd.OpenQuery "queryname"
' Apply filter
DoCmd.ApplyFilter, mFilter

I now see that I had mFilter in the 3rd line twice. I corrected, and
rechecked the entire code - It still does not work.

Still get the same error code, 3075 with this message: Syntax error in
string query expression 'ClientName in ('JPEC,'TCAPS)'. {JPEC & TCAPS are
the 2 client names that I chose for running the query}.

I do appreciate your help. Any other thoughts? I am beginning to think it is
not possible to use a multi select list box to display a query.

I think I see your problem. It's in the if c=0 then...else statement.
Recopy it from my original reply and think it should work ok for you.


:

The row source for the list box:
SELECT tblclient.ClientID, tblclient.ClientName FROM tblclient ORDER BY
[ClientName];
Bound column is 2 = ClientName
data type: TEXT

SQL for the underlying query {named qryclienthours-detail}:
SELECT tblclient.ClientID, tblclient.ClientName, tblProject.[Project Name],
tblinput.Date, tblinput.[Start Time], tblinput.[Stop Time], Sum(([stop
time]-[start time])*24) AS [total time], tblinput.[Task Description]
FROM (tblclient INNER JOIN tblProject ON tblclient.ClientID =
tblProject.Client) INNER JOIN tblinput ON (tblProject.ProjectID =
tblinput.Project) AND (tblclient.ClientID = tblinput.Client)
GROUP BY tblclient.ClientID, tblclient.ClientName, tblProject.[Project
Name], tblinput.Date, tblinput.[Start Time], tblinput.[Stop Time],
tblinput.[Task Description]
HAVING (((tblinput.Date) Between [forms]![fmchdetails].[Beginning Date] And
[forms]![fmchdetails].[Ending Date]))
ORDER BY tblProject.[Project Name], tblinput.Date;

code in the cmd button's on click event:
Private Sub Command14_Click()
Dim mFilter As String
Dim i As Variant
Dim c As Integer
c = 0
For Each i In lstClient.ItemsSelected
If c = 0 Then
mFilter = mFilter = "'" & lstClient.ItemData(i) & "',"
Else
mFilter = mFilter & "'" & lstClient.ItemData(i) & "',"
End If
c = c + 1
Next i
mFilter = Left(mFilter, Len(mFilter) - 2)
mFilter = "ClientName in (" & mFilter & ")"
DoCmd.OpenQuery "qryclienthours-detail"
DoCmd.ApplyFilter , mFilter

End Sub

:

The row source for the list box and its bound column and data type.
Also sql for the query that you open and the code in your cmd button's on
click event.

:

I copied the code as you suggested (making appropriate field name changes)
and got a runtime 3075 error: Syntax Error in String in query expression
'ClientName in (False'3)'.

I am guessing this was because you assumed my List box was bound to the TEXT
column. I checked and it was bound to column 1 - ClientID. So I changed it to
column 2 which corresponds to the text field ClientName.

I then got this runtime error: 3075: Syntax Error in String in query express
'ClientName in (TCAPS'3)'. {TCAPS is a client name - had chosen 2 client
names JPEC and TCAPS}

When I get the runtime error and choose the DEBUG button it highlights the
last line in the code: DoCmd.ApplyFilter , mFilter

Any suggestions? What else would you need from me to help troubleshoot?

:

You can build a filter from the items selected in the list box and apply it
to the query.

First remove the criteria in your query for the ClientName field.
Then add code similar to the following your cmd button on click event.
I'll assuming that the bound column for lstClient is text.

----------------------
Dim mFilter As String
Dim i As Variant
Dim c As Integer
c = 0
For Each i In lstClient.ItemsSelected
If c = 0 Then
mFilter = "'" & lstClient.ItemData(i) & "', "
Else
mFilter = mFilter & "'" & lstClient.ItemData(i) & "', "
End If
c = c + 1
Next i
mFilter = Left(mFilter, Len(mFilter) - 2)
mFilter = "ClientName in (" & mFilter & ")"
DoCmd.OpenQuery "queryname"
DoCmd.ApplyFilter , mFilter
--------------------------------

:

I have been through all the posts in both Forms forums on this site looking
for my answer but don't seem to be finding it.

I have a Select Query that is being used in Datasheet, Edit mode.
I originally created a form called FMCHDETAILS that allows the user to:
1. Select CLientName from a combo list box (lstClient)
2. enter a date range using seperate date fields (between beginning date and
ending date)
3. Click on cmd button to run query.

the Criteria fields in the query are set to:
ClientName = [Forms]![fmchdetails].[lstClient]
Date = Between [Forms]![fmchdetails]![Beginning Date] and
[Forms]![fmchdetails]![Ending Date]

All is working fine, but now the user would like to select more than one
ClientName each time the query is run.

I know I have to change the combo box to a list box and change the
MultiSelect to Simple.

I found information on the forum relating to using a multi select list box
passing information to an underlying query and displaying the results as a
report (which I used for a report and it works great!)

But I can't find any information that works with passing the criteria from
my form to a query and displaying the filtered records for editing.

Can this be done?

Thanks!