Allowing Multiple Select in Combo Boxes

  • Thread starter Thread starter MonTpython
  • Start date Start date
M

MonTpython

I am working on a form driven database that primarily works as an advanced
filtering system.
One of the main drawbacks to the system as it stands is that the user must
currently run seperate queries/reports if they wish to select more than one
option from a drop down combo box.
Is there a way to allow the user to multiple select items from the drop down?
 
No, not in a combo box, but you could use a list box, or a continuous subform
with checkboxes.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
It is not possible to make multiple selections from a combo box.

However, it is possible to allow users to make multiple selections a list
box by setting the "Multi Select" property of the list box. Check the Help
file in Access for more info on the options you have.

With that said, if you want to allow the user to make multiple selections
and then use those selections for filtering a query then you will have to use
VBA code to process the selections made by the user and develop an sql
statement that can then be used as the record source for your query, form or
report.
 
Thanks Doug,
This looks perfect but I still have one huge problem: while I'm decent at
VBA, I don't understand SQL worth anything.
The VBA code it refers to is relatively straightforward, but what I don't
understand is how you would link the query's SQL WHERE statement to a command
on the form in such a way that it passes the results back into the query.
As it stands the SQL Statement reads as follows:

SELECT tblMasterStoreTable.DistrictID, tblMasterStoreTable.StoreID
FROM tblMasterStoreTable
WHERE
(((tblMyTable.DistrictID)=IIf(IsNull([Forms]![frmMyForm]![lbDistrictID]),[DistrictID],IIf(([DistrictID]=[Forms]![frmMyForm]![lbDistrictID]),[DistrictID]))));

Thanks Again,
MonT
 
Your existing Where clause makes no sense to me.

I'm assuming lbDistrictID is the listbox on form frmMyForm. I'll further
assume that DistrictID is a numeric field.

You need to have code like the following:

Dim qdfCurr As DAO.QueryDef
Dim strDistricts As String
Dim strSQL As String
Dim varSelected As Variant

strSQL = "SELECT DistrictID, StoreID " & _
"FROM tblMasterStoreTable "

If Me![lbDistrictID].ItemsSelected.Count > 0 Then
For Each varSelected In Me![lbDistrictID].ItemsSelected
strDistricts = strDistrict & Me![lbDistrictID].ItemData(varSelected) &
", "
Next varSelected
' Remove the extraneous ", " from the end
strDistricts = Left(strDistricts, Len(strDistricts) - 2)
strSQL = strSQL & "WHERE DistrictID IN (" & strDistricts & ")"
End If

Set qdfCurr = CurrentDb.QueryDefs("NameOfQuery")
qdfCurr.SQL = strSQL

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


MonTpython said:
Thanks Doug,
This looks perfect but I still have one huge problem: while I'm decent at
VBA, I don't understand SQL worth anything.
The VBA code it refers to is relatively straightforward, but what I don't
understand is how you would link the query's SQL WHERE statement to a
command
on the form in such a way that it passes the results back into the query.
As it stands the SQL Statement reads as follows:

SELECT tblMasterStoreTable.DistrictID, tblMasterStoreTable.StoreID
FROM tblMasterStoreTable
WHERE
(((tblMyTable.DistrictID)=IIf(IsNull([Forms]![frmMyForm]![lbDistrictID]),[DistrictID],IIf(([DistrictID]=[Forms]![frmMyForm]![lbDistrictID]),[DistrictID]))));

Thanks Again,
MonT


Douglas J. Steele said:
No. Combo boxes are restricted to single-select.

List boxes allow multi-select, but you can't simply refer to a
multi-select
list box as a parameter. You need to dynamically alter the SQL of the
query,
as shown in http://www.mvps.org/access/forms/frm0007.htm at "The Access
Web"
Click to expand...
Click to expand...
 
Sorry,
Like I said, I know virtually nothing about SQL. The WHERE clause I pasted
in below is what I would get when I view the query itself in SQL view.
What it is doing is asking if the user input anything into the form (if null
-> yield everything) then testing if the value entered into the listbox
(previously a combo box) matches the values in the source table.

With that said, I tried putting the code below into the form itself attached
to the listbox and set to run on LostFocus. When it runs I get "Run-Time
Error 3265: Item not found in this collection." And when debugging it is
highlighting

Set qdfCurr = CurrentDb.QueryDefs("qryNestMasterA")

Did I enter the query name in wrong? If not what am I doing wrong?
Thanks again,
MonT


Douglas J. Steele said:
Your existing Where clause makes no sense to me.

I'm assuming lbDistrictID is the listbox on form frmMyForm. I'll further
assume that DistrictID is a numeric field.

You need to have code like the following:

Dim qdfCurr As DAO.QueryDef
Dim strDistricts As String
Dim strSQL As String
Dim varSelected As Variant

strSQL = "SELECT DistrictID, StoreID " & _
"FROM tblMasterStoreTable "

If Me![lbDistrictID].ItemsSelected.Count > 0 Then
For Each varSelected In Me![lbDistrictID].ItemsSelected
strDistricts = strDistrict & Me![lbDistrictID].ItemData(varSelected) &
", "
Next varSelected
' Remove the extraneous ", " from the end
strDistricts = Left(strDistricts, Len(strDistricts) - 2)
strSQL = strSQL & "WHERE DistrictID IN (" & strDistricts & ")"
End If

Set qdfCurr = CurrentDb.QueryDefs("NameOfQuery")
qdfCurr.SQL = strSQL

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


MonTpython said:
Thanks Doug,
This looks perfect but I still have one huge problem: while I'm decent at
VBA, I don't understand SQL worth anything.
The VBA code it refers to is relatively straightforward, but what I don't
understand is how you would link the query's SQL WHERE statement to a
command
on the form in such a way that it passes the results back into the query.
As it stands the SQL Statement reads as follows:

SELECT tblMasterStoreTable.DistrictID, tblMasterStoreTable.StoreID
FROM tblMasterStoreTable
WHERE
(((tblMyTable.DistrictID)=IIf(IsNull([Forms]![frmMyForm]![lbDistrictID]),[DistrictID],IIf(([DistrictID]=[Forms]![frmMyForm]![lbDistrictID]),[DistrictID]))));

Thanks Again,
MonT


Douglas J. Steele said:
No. Combo boxes are restricted to single-select.

List boxes allow multi-select, but you can't simply refer to a
multi-select
list box as a parameter. You need to dynamically alter the SQL of the
query,
as shown in http://www.mvps.org/access/forms/frm0007.htm at "The Access
Web"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am working on a form driven database that primarily works as an
advanced
filtering system.
One of the main drawbacks to the system as it stands is that the user
must
currently run seperate queries/reports if they wish to select more than
one
option from a drop down combo box.
Is there a way to allow the user to multiple select items from the drop
down?
Click to expand...
Click to expand...
Click to expand...
 
From the error message, I'd have to say yes, you did get the query name
wrong. Unfortunately, only you can determine what the correct name is it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


MonTpython said:
Sorry,
Like I said, I know virtually nothing about SQL. The WHERE clause I pasted
in below is what I would get when I view the query itself in SQL view.
What it is doing is asking if the user input anything into the form (if
null
-> yield everything) then testing if the value entered into the listbox
(previously a combo box) matches the values in the source table.

With that said, I tried putting the code below into the form itself
attached
to the listbox and set to run on LostFocus. When it runs I get "Run-Time
Error 3265: Item not found in this collection." And when debugging it is
highlighting

Set qdfCurr = CurrentDb.QueryDefs("qryNestMasterA")

Did I enter the query name in wrong? If not what am I doing wrong?
Thanks again,
MonT


Douglas J. Steele said:
Your existing Where clause makes no sense to me.

I'm assuming lbDistrictID is the listbox on form frmMyForm. I'll further
assume that DistrictID is a numeric field.

You need to have code like the following:

Dim qdfCurr As DAO.QueryDef
Dim strDistricts As String
Dim strSQL As String
Dim varSelected As Variant

strSQL = "SELECT DistrictID, StoreID " & _
"FROM tblMasterStoreTable "

If Me![lbDistrictID].ItemsSelected.Count > 0 Then
For Each varSelected In Me![lbDistrictID].ItemsSelected
strDistricts = strDistrict &
Me![lbDistrictID].ItemData(varSelected) &
", "
Next varSelected
' Remove the extraneous ", " from the end
strDistricts = Left(strDistricts, Len(strDistricts) - 2)
strSQL = strSQL & "WHERE DistrictID IN (" & strDistricts & ")"
End If

Set qdfCurr = CurrentDb.QueryDefs("NameOfQuery")
qdfCurr.SQL = strSQL

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


MonTpython said:
Thanks Doug,
This looks perfect but I still have one huge problem: while I'm decent
at
VBA, I don't understand SQL worth anything.
The VBA code it refers to is relatively straightforward, but what I
don't
understand is how you would link the query's SQL WHERE statement to a
command
on the form in such a way that it passes the results back into the
query.
As it stands the SQL Statement reads as follows:

SELECT tblMasterStoreTable.DistrictID, tblMasterStoreTable.StoreID
FROM tblMasterStoreTable
WHERE
(((tblMyTable.DistrictID)=IIf(IsNull([Forms]![frmMyForm]![lbDistrictID]),[DistrictID],IIf(([DistrictID]=[Forms]![frmMyForm]![lbDistrictID]),[DistrictID]))));

Thanks Again,
MonT


:

No. Combo boxes are restricted to single-select.

List boxes allow multi-select, but you can't simply refer to a
multi-select
list box as a parameter. You need to dynamically alter the SQL of the
query,
as shown in http://www.mvps.org/access/forms/frm0007.htm at "The
Access
Web"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am working on a form driven database that primarily works as an
advanced
filtering system.
One of the main drawbacks to the system as it stands is that the
user
must
currently run seperate queries/reports if they wish to select more
than
one
option from a drop down combo box.
Is there a way to allow the user to multiple select items from the
drop
down?
Click to expand...
Click to expand...
Click to expand...
 
Unless there aren't supposed to be the quotation marks, the name below
(qryNestMasterA) is the name of the query that the list box should be passing
the values to.

Douglas J. Steele said:
From the error message, I'd have to say yes, you did get the query name
wrong. Unfortunately, only you can determine what the correct name is it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


MonTpython said:
Sorry,
Like I said, I know virtually nothing about SQL. The WHERE clause I pasted
in below is what I would get when I view the query itself in SQL view.
What it is doing is asking if the user input anything into the form (if
null
-> yield everything) then testing if the value entered into the listbox
(previously a combo box) matches the values in the source table.

With that said, I tried putting the code below into the form itself
attached
to the listbox and set to run on LostFocus. When it runs I get "Run-Time
Error 3265: Item not found in this collection." And when debugging it is
highlighting

Set qdfCurr = CurrentDb.QueryDefs("qryNestMasterA")

Did I enter the query name in wrong? If not what am I doing wrong?
Thanks again,
MonT


Douglas J. Steele said:
Your existing Where clause makes no sense to me.

I'm assuming lbDistrictID is the listbox on form frmMyForm. I'll further
assume that DistrictID is a numeric field.

You need to have code like the following:

Dim qdfCurr As DAO.QueryDef
Dim strDistricts As String
Dim strSQL As String
Dim varSelected As Variant

strSQL = "SELECT DistrictID, StoreID " & _
"FROM tblMasterStoreTable "

If Me![lbDistrictID].ItemsSelected.Count > 0 Then
For Each varSelected In Me![lbDistrictID].ItemsSelected
strDistricts = strDistrict &
Me![lbDistrictID].ItemData(varSelected) &
", "
Next varSelected
' Remove the extraneous ", " from the end
strDistricts = Left(strDistricts, Len(strDistricts) - 2)
strSQL = strSQL & "WHERE DistrictID IN (" & strDistricts & ")"
End If

Set qdfCurr = CurrentDb.QueryDefs("NameOfQuery")
qdfCurr.SQL = strSQL

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks Doug,
This looks perfect but I still have one huge problem: while I'm decent
at
VBA, I don't understand SQL worth anything.
The VBA code it refers to is relatively straightforward, but what I
don't
understand is how you would link the query's SQL WHERE statement to a
command
on the form in such a way that it passes the results back into the
query.
As it stands the SQL Statement reads as follows:

SELECT tblMasterStoreTable.DistrictID, tblMasterStoreTable.StoreID
FROM tblMasterStoreTable
WHERE
(((tblMyTable.DistrictID)=IIf(IsNull([Forms]![frmMyForm]![lbDistrictID]),[DistrictID],IIf(([DistrictID]=[Forms]![frmMyForm]![lbDistrictID]),[DistrictID]))));

Thanks Again,
MonT


:

No. Combo boxes are restricted to single-select.

List boxes allow multi-select, but you can't simply refer to a
multi-select
list box as a parameter. You need to dynamically alter the SQL of the
query,
as shown in http://www.mvps.org/access/forms/frm0007.htm at "The
Access
Web"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I am working on a form driven database that primarily works as an
advanced
filtering system.
One of the main drawbacks to the system as it stands is that the
user
must
currently run seperate queries/reports if they wish to select more
than
one
option from a drop down combo box.
Is there a way to allow the user to multiple select items from the
drop
down?
Click to expand...
Click to expand...
Click to expand...
 
The quotes are supposed to be there.

You sure there aren't spaces or something like that in the query name?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


MonTpython said:
Unless there aren't supposed to be the quotation marks, the name below
(qryNestMasterA) is the name of the query that the list box should be
passing
the values to.

Douglas J. Steele said:
From the error message, I'd have to say yes, you did get the query name
wrong. Unfortunately, only you can determine what the correct name is it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


MonTpython said:
Sorry,
Like I said, I know virtually nothing about SQL. The WHERE clause I
pasted
in below is what I would get when I view the query itself in SQL view.
What it is doing is asking if the user input anything into the form (if
null
-> yield everything) then testing if the value entered into the listbox
(previously a combo box) matches the values in the source table.

With that said, I tried putting the code below into the form itself
attached
to the listbox and set to run on LostFocus. When it runs I get
"Run-Time
Error 3265: Item not found in this collection." And when debugging it
is
highlighting

Set qdfCurr = CurrentDb.QueryDefs("qryNestMasterA")

Did I enter the query name in wrong? If not what am I doing wrong?
Thanks again,
MonT


:

Your existing Where clause makes no sense to me.

I'm assuming lbDistrictID is the listbox on form frmMyForm. I'll
further
assume that DistrictID is a numeric field.

You need to have code like the following:

Dim qdfCurr As DAO.QueryDef
Dim strDistricts As String
Dim strSQL As String
Dim varSelected As Variant

strSQL = "SELECT DistrictID, StoreID " & _
"FROM tblMasterStoreTable "

If Me![lbDistrictID].ItemsSelected.Count > 0 Then
For Each varSelected In Me![lbDistrictID].ItemsSelected
strDistricts = strDistrict &
Me![lbDistrictID].ItemData(varSelected) &
", "
Next varSelected
' Remove the extraneous ", " from the end
strDistricts = Left(strDistricts, Len(strDistricts) - 2)
strSQL = strSQL & "WHERE DistrictID IN (" & strDistricts & ")"
End If

Set qdfCurr = CurrentDb.QueryDefs("NameOfQuery")
qdfCurr.SQL = strSQL

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks Doug,
This looks perfect but I still have one huge problem: while I'm
decent
at
VBA, I don't understand SQL worth anything.
The VBA code it refers to is relatively straightforward, but what I
don't
understand is how you would link the query's SQL WHERE statement to
a
command
on the form in such a way that it passes the results back into the
query.
As it stands the SQL Statement reads as follows:

SELECT tblMasterStoreTable.DistrictID, tblMasterStoreTable.StoreID
FROM tblMasterStoreTable
WHERE
(((tblMyTable.DistrictID)=IIf(IsNull([Forms]![frmMyForm]![lbDistrictID]),[DistrictID],IIf(([DistrictID]=[Forms]![frmMyForm]![lbDistrictID]),[DistrictID]))));

Thanks Again,
MonT


:

No. Combo boxes are restricted to single-select.

List boxes allow multi-select, but you can't simply refer to a
multi-select
list box as a parameter. You need to dynamically alter the SQL of
the
query,
as shown in http://www.mvps.org/access/forms/frm0007.htm at "The
Access
Web"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I am working on a form driven database that primarily works as an
advanced
filtering system.
One of the main drawbacks to the system as it stands is that the
user
must
currently run seperate queries/reports if they wish to select
more
than
one
option from a drop down combo box.
Is there a way to allow the user to multiple select items from
the
drop
down?
Click to expand...
Click to expand...
Click to expand...
 
The quotes are supposed to be there.

You sure there aren't spaces or something like that in the query name?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


MonTpython said:
Unless there aren't supposed to be the quotation marks, the name below
(qryNestMasterA) is the name of the query that the list box should be
passing
the values to.

Douglas J. Steele said:
From the error message, I'd have to say yes, you did get the query name
wrong. Unfortunately, only you can determine what the correct name is it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


MonTpython said:
Sorry,
Like I said, I know virtually nothing about SQL. The WHERE clause I
pasted
in below is what I would get when I view the query itself in SQL view.
What it is doing is asking if the user input anything into the form (if
null
-> yield everything) then testing if the value entered into the listbox
(previously a combo box) matches the values in the source table.

With that said, I tried putting the code below into the form itself
attached
to the listbox and set to run on LostFocus. When it runs I get
"Run-Time
Error 3265: Item not found in this collection." And when debugging it
is
highlighting

Set qdfCurr = CurrentDb.QueryDefs("qryNestMasterA")

Did I enter the query name in wrong? If not what am I doing wrong?
Thanks again,
MonT


:

Your existing Where clause makes no sense to me.

I'm assuming lbDistrictID is the listbox on form frmMyForm. I'll
further
assume that DistrictID is a numeric field.

You need to have code like the following:

Dim qdfCurr As DAO.QueryDef
Dim strDistricts As String
Dim strSQL As String
Dim varSelected As Variant

strSQL = "SELECT DistrictID, StoreID " & _
"FROM tblMasterStoreTable "

If Me![lbDistrictID].ItemsSelected.Count > 0 Then
For Each varSelected In Me![lbDistrictID].ItemsSelected
strDistricts = strDistrict &
Me![lbDistrictID].ItemData(varSelected) &
", "
Next varSelected
' Remove the extraneous ", " from the end
strDistricts = Left(strDistricts, Len(strDistricts) - 2)
strSQL = strSQL & "WHERE DistrictID IN (" & strDistricts & ")"
End If

Set qdfCurr = CurrentDb.QueryDefs("NameOfQuery")
qdfCurr.SQL = strSQL

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks Doug,
This looks perfect but I still have one huge problem: while I'm
decent
at
VBA, I don't understand SQL worth anything.
The VBA code it refers to is relatively straightforward, but what I
don't
understand is how you would link the query's SQL WHERE statement to
a
command
on the form in such a way that it passes the results back into the
query.
As it stands the SQL Statement reads as follows:

SELECT tblMasterStoreTable.DistrictID, tblMasterStoreTable.StoreID
FROM tblMasterStoreTable
WHERE
(((tblMyTable.DistrictID)=IIf(IsNull([Forms]![frmMyForm]![lbDistrictID]),[DistrictID],IIf(([DistrictID]=[Forms]![frmMyForm]![lbDistrictID]),[DistrictID]))));

Thanks Again,
MonT


:

No. Combo boxes are restricted to single-select.

List boxes allow multi-select, but you can't simply refer to a
multi-select
list box as a parameter. You need to dynamically alter the SQL of
the
query,
as shown in http://www.mvps.org/access/forms/frm0007.htm at "The
Access
Web"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I am working on a form driven database that primarily works as an
advanced
filtering system.
One of the main drawbacks to the system as it stands is that the
user
must
currently run seperate queries/reports if they wish to select
more
than
one
option from a drop down combo box.
Is there a way to allow the user to multiple select items from
the
drop
down?
Click to expand...
Click to expand...
Click to expand...
 
Unfortunately, no, there are no spaces, symbols or any other naughty tidbits
in the query name.
What else might be causing it to choke?

Douglas J. Steele said:
The quotes are supposed to be there.

You sure there aren't spaces or something like that in the query name?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


MonTpython said:
Unless there aren't supposed to be the quotation marks, the name below
(qryNestMasterA) is the name of the query that the list box should be
passing
the values to.

Douglas J. Steele said:
From the error message, I'd have to say yes, you did get the query name
wrong. Unfortunately, only you can determine what the correct name is it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Sorry,
Like I said, I know virtually nothing about SQL. The WHERE clause I
pasted
in below is what I would get when I view the query itself in SQL view.
What it is doing is asking if the user input anything into the form (if
null
-> yield everything) then testing if the value entered into the listbox
(previously a combo box) matches the values in the source table.

With that said, I tried putting the code below into the form itself
attached
to the listbox and set to run on LostFocus. When it runs I get
"Run-Time
Error 3265: Item not found in this collection." And when debugging it
is
highlighting

Set qdfCurr = CurrentDb.QueryDefs("qryNestMasterA")

Did I enter the query name in wrong? If not what am I doing wrong?
Thanks again,
MonT


:

Your existing Where clause makes no sense to me.

I'm assuming lbDistrictID is the listbox on form frmMyForm. I'll
further
assume that DistrictID is a numeric field.

You need to have code like the following:

Dim qdfCurr As DAO.QueryDef
Dim strDistricts As String
Dim strSQL As String
Dim varSelected As Variant

strSQL = "SELECT DistrictID, StoreID " & _
"FROM tblMasterStoreTable "

If Me![lbDistrictID].ItemsSelected.Count > 0 Then
For Each varSelected In Me![lbDistrictID].ItemsSelected
strDistricts = strDistrict &
Me![lbDistrictID].ItemData(varSelected) &
", "
Next varSelected
' Remove the extraneous ", " from the end
strDistricts = Left(strDistricts, Len(strDistricts) - 2)
strSQL = strSQL & "WHERE DistrictID IN (" & strDistricts & ")"
End If

Set qdfCurr = CurrentDb.QueryDefs("NameOfQuery")
qdfCurr.SQL = strSQL

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks Doug,
This looks perfect but I still have one huge problem: while I'm
decent
at
VBA, I don't understand SQL worth anything.
The VBA code it refers to is relatively straightforward, but what I
don't
understand is how you would link the query's SQL WHERE statement to
a
command
on the form in such a way that it passes the results back into the
query.
As it stands the SQL Statement reads as follows:

SELECT tblMasterStoreTable.DistrictID, tblMasterStoreTable.StoreID
FROM tblMasterStoreTable
WHERE
(((tblMyTable.DistrictID)=IIf(IsNull([Forms]![frmMyForm]![lbDistrictID]),[DistrictID],IIf(([DistrictID]=[Forms]![frmMyForm]![lbDistrictID]),[DistrictID]))));

Thanks Again,
MonT


:

No. Combo boxes are restricted to single-select.

List boxes allow multi-select, but you can't simply refer to a
multi-select
list box as a parameter. You need to dynamically alter the SQL of
the
query,
as shown in http://www.mvps.org/access/forms/frm0007.htm at "The
Access
Web"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I am working on a form driven database that primarily works as an
advanced
filtering system.
One of the main drawbacks to the system as it stands is that the
user
must
currently run seperate queries/reports if they wish to select
more
than
one
option from a drop down combo box.
Is there a way to allow the user to multiple select items from
the
drop
down?
Click to expand...
Click to expand...
Click to expand...
 
Try creating a new query that contains the same SQL as qryNestMasterA, and
see whether that query works any better.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


MonTpython said:
Unfortunately, no, there are no spaces, symbols or any other naughty
tidbits
in the query name.
What else might be causing it to choke?

Douglas J. Steele said:
The quotes are supposed to be there.

You sure there aren't spaces or something like that in the query name?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


MonTpython said:
Unless there aren't supposed to be the quotation marks, the name below
(qryNestMasterA) is the name of the query that the list box should be
passing
the values to.

:

From the error message, I'd have to say yes, you did get the query
name
wrong. Unfortunately, only you can determine what the correct name is
it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Sorry,
Like I said, I know virtually nothing about SQL. The WHERE clause I
pasted
in below is what I would get when I view the query itself in SQL
view.
What it is doing is asking if the user input anything into the form
(if
null
-> yield everything) then testing if the value entered into the
listbox
(previously a combo box) matches the values in the source table.

With that said, I tried putting the code below into the form itself
attached
to the listbox and set to run on LostFocus. When it runs I get
"Run-Time
Error 3265: Item not found in this collection." And when debugging
it
is
highlighting

Set qdfCurr = CurrentDb.QueryDefs("qryNestMasterA")

Did I enter the query name in wrong? If not what am I doing wrong?
Thanks again,
MonT


:

Your existing Where clause makes no sense to me.

I'm assuming lbDistrictID is the listbox on form frmMyForm. I'll
further
assume that DistrictID is a numeric field.

You need to have code like the following:

Dim qdfCurr As DAO.QueryDef
Dim strDistricts As String
Dim strSQL As String
Dim varSelected As Variant

strSQL = "SELECT DistrictID, StoreID " & _
"FROM tblMasterStoreTable "

If Me![lbDistrictID].ItemsSelected.Count > 0 Then
For Each varSelected In Me![lbDistrictID].ItemsSelected
strDistricts = strDistrict &
Me![lbDistrictID].ItemData(varSelected) &
", "
Next varSelected
' Remove the extraneous ", " from the end
strDistricts = Left(strDistricts, Len(strDistricts) - 2)
strSQL = strSQL & "WHERE DistrictID IN (" & strDistricts & ")"
End If

Set qdfCurr = CurrentDb.QueryDefs("NameOfQuery")
qdfCurr.SQL = strSQL

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
Thanks Doug,
This looks perfect but I still have one huge problem: while I'm
decent
at
VBA, I don't understand SQL worth anything.
The VBA code it refers to is relatively straightforward, but what
I
don't
understand is how you would link the query's SQL WHERE statement
to
a
command
on the form in such a way that it passes the results back into
the
query.
As it stands the SQL Statement reads as follows:

SELECT tblMasterStoreTable.DistrictID,
tblMasterStoreTable.StoreID
FROM tblMasterStoreTable
WHERE
(((tblMyTable.DistrictID)=IIf(IsNull([Forms]![frmMyForm]![lbDistrictID]),[DistrictID],IIf(([DistrictID]=[Forms]![frmMyForm]![lbDistrictID]),[DistrictID]))));

Thanks Again,
MonT


:

No. Combo boxes are restricted to single-select.

List boxes allow multi-select, but you can't simply refer to a
multi-select
list box as a parameter. You need to dynamically alter the SQL
of
the
query,
as shown in http://www.mvps.org/access/forms/frm0007.htm at "The
Access
Web"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I am working on a form driven database that primarily works as
an
advanced
filtering system.
One of the main drawbacks to the system as it stands is that
the
user
must
currently run seperate queries/reports if they wish to select
more
than
one
option from a drop down combo box.
Is there a way to allow the user to multiple select items from
the
drop
down?
Click to expand...
Click to expand...
Click to expand...
 
I had to ditch the "IIF(IsNull(Form!txtBox))" criteria that I was using to
return all values if the user didn't select anything but it is finally
working. I was still getting a syntax error but then realized that the code
you gave was anticipating numeric values and that I am using text. I added in
the quote delimiters (learned from Allen Browne's site) and it's running like
a champ.
Thanks for sticking with me.

MonT

PS - Any ideas on how to add the other criteria (mentioned above and in the
prior post) without having it erased every time this code runs? :)

Douglas J. Steele said:
Try creating a new query that contains the same SQL as qryNestMasterA, and
see whether that query works any better.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


MonTpython said:
Unfortunately, no, there are no spaces, symbols or any other naughty
tidbits
in the query name.
What else might be causing it to choke?

Douglas J. Steele said:
The quotes are supposed to be there.

You sure there aren't spaces or something like that in the query name?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Unless there aren't supposed to be the quotation marks, the name below
(qryNestMasterA) is the name of the query that the list box should be
passing
the values to.

:

From the error message, I'd have to say yes, you did get the query
name
wrong. Unfortunately, only you can determine what the correct name is
it.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Sorry,
Like I said, I know virtually nothing about SQL. The WHERE clause I
pasted
in below is what I would get when I view the query itself in SQL
view.
What it is doing is asking if the user input anything into the form
(if
null
-> yield everything) then testing if the value entered into the
listbox
(previously a combo box) matches the values in the source table.

With that said, I tried putting the code below into the form itself
attached
to the listbox and set to run on LostFocus. When it runs I get
"Run-Time
Error 3265: Item not found in this collection." And when debugging
it
is
highlighting

Set qdfCurr = CurrentDb.QueryDefs("qryNestMasterA")

Did I enter the query name in wrong? If not what am I doing wrong?
Thanks again,
MonT


:

Your existing Where clause makes no sense to me.

I'm assuming lbDistrictID is the listbox on form frmMyForm. I'll
further
assume that DistrictID is a numeric field.

You need to have code like the following:

Dim qdfCurr As DAO.QueryDef
Dim strDistricts As String
Dim strSQL As String
Dim varSelected As Variant

strSQL = "SELECT DistrictID, StoreID " & _
"FROM tblMasterStoreTable "

If Me![lbDistrictID].ItemsSelected.Count > 0 Then
For Each varSelected In Me![lbDistrictID].ItemsSelected
strDistricts = strDistrict &
Me![lbDistrictID].ItemData(varSelected) &
", "
Next varSelected
' Remove the extraneous ", " from the end
strDistricts = Left(strDistricts, Len(strDistricts) - 2)
strSQL = strSQL & "WHERE DistrictID IN (" & strDistricts & ")"
End If

Set qdfCurr = CurrentDb.QueryDefs("NameOfQuery")
qdfCurr.SQL = strSQL

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
Thanks Doug,
This looks perfect but I still have one huge problem: while I'm
decent
at
VBA, I don't understand SQL worth anything.
The VBA code it refers to is relatively straightforward, but what
I
don't
understand is how you would link the query's SQL WHERE statement
to
a
command
on the form in such a way that it passes the results back into
the
query.
As it stands the SQL Statement reads as follows:

SELECT tblMasterStoreTable.DistrictID,
tblMasterStoreTable.StoreID
FROM tblMasterStoreTable
WHERE
(((tblMyTable.DistrictID)=IIf(IsNull([Forms]![frmMyForm]![lbDistrictID]),[DistrictID],IIf(([DistrictID]=[Forms]![frmMyForm]![lbDistrictID]),[DistrictID]))));

Thanks Again,
MonT


:

No. Combo boxes are restricted to single-select.

List boxes allow multi-select, but you can't simply refer to a
multi-select
list box as a parameter. You need to dynamically alter the SQL
of
the
query,
as shown in http://www.mvps.org/access/forms/frm0007.htm at "The
Access
Web"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


message
I am working on a form driven database that primarily works as
an
advanced
filtering system.
One of the main drawbacks to the system as it stands is that
the
user
must
currently run seperate queries/reports if they wish to select
more
than
one
option from a drop down combo box.
Is there a way to allow the user to multiple select items from
the
drop
down?
Click to expand...
Click to expand...
Click to expand...
 
MonTpython said:
I had to ditch the "IIF(IsNull(Form!txtBox))" criteria that I was using to
return all values if the user didn't select anything but it is finally
working.
Click to expand...

Yeah, the code I gave handled the case where nothing was selected in the
list box.
PS - Any ideas on how to add the other criteria (mentioned above and in
the
prior post) without having it erased every time this code runs? :)
Click to expand...

It's not immediately apparent to me the "the other criteria" are, but since
you have to regenerate the SQL each time, you need to ensure that "the other
criteria" are included in the base SQL you use.
 
Yeah, I noticed that after playing around with it. I had it set to run on
lost focus which allowed a user to bypass the control and still get the last
users results, now it is running On Click for the submit search control so
that it captures the results regardless.

Thanks again!
 
I am trying to add a multivalue list box to frmPatients. I want this list box
to save multiple values. See my attempt in the lower right hand corner of
“frmPatientsâ€
I found a way to do this in the Access help under “Add or change a lookup
column that lets you store multiple values†I want this to look like sample
shown in thr Help.
My list of possible pain levels is in the table “tblPainLevel†and the table
I want to store the list of pains is in “CurrentPainâ€
The part that doesn’t work like I thought it should is found in
Add or change a lookup column that lets you store multiple values
Then
Using the Lookup Wizard to create multivalued lookup columns
Then
7. Under Do you want to store multiple values for this lookup?, select the
Allow Multiple Values check box.
The above doesn’t appear in the Wizard.
The beginning of the help says this is new in Access 2007.
Do I need to turn something on to make this work?
Or should I do something completely different?
Thanks for the help.
Rick B
 
Save your database as a 2007 database w/ extension .accdb. Most likely, your
database has a .mdb extension.
 
Back
Top