Data is a List Box

[Guest]

I'm trying to remember ( and I wasn't ever really good at it) how to take
infromation from a list box, save selected items in another to be used as
query crieteria. I can generate the second list. However, the query doesn't
like the info. Now what. Here's what I have so far.

Public myTablist As Variant
Public varTablist As Variant
Public myTabNames

Sub RowsSelected()
Dim ctlTabs As Control
Dim myInt1 As Integer
Dim myVar1
' Return Control object variable pointing to list box.
Set ctlTabs = Forms!frmMainQuery!cboTabList
' Enumerate through selected items.

For Each varTablist In ctlTabs.ItemsSelected
' Print value of bound column.

If myInt1 = 0 Then
cmbTabListRpt.RowSource = ctlTabs.ItemData(varTablist)
myTablist = ctlTabs.ItemData(varTablist)
Else
cmbTabListRpt.RowSource = myVar1 & ";" &
ctlTabs.ItemData(varTablist)
myTablist = "'" & myTablist & "'" & " Or " & "'" &
ctlTabs.ItemData(varTablist) & "'"
End If

myVar1 = cmbTabListRpt.RowSource
myInt1 = myInt1 + 1
Next varTablist

txtTabList = myTablist
End Sub

Private Sub cboTabList_AfterUpdate()
Call RowsSelected
End Sub

 

[Graham Mandeno]

Hi Eric

It appears that you are using myTabList as a WHERE clause. Presumably this
is then used to open a form or report.

The content of myTabList will be something like:
'dog' Or 'cat' Or 'elephant'

This will not work. What you should have is either:
[Species]='dog' Or [Species]='cat' Or [Species]='elephant'
or
[Species] IN ('dog', 'cat', 'elephant')

 

[Guest]

Thanks - I can get the SQL Query to work with the info you've supplied. And
Yes - sorry I wasn't clear - I have a form that a user will interface with to
select criteria to run a query query (this will be duplicated to run from
another form to run reports). I'm still not getting the data to transfer
correctly into the query.

The SQL query is

SELECT tblDevData.Dev, tblDevData.Qtr, tblDevData.Tab
FROM tblDevData INNER JOIN tblPlanData ON (tblDevData.Qtr = tblPlanData.Qtr)
AND (tblDevData.Tab = tblPlanData.Tab) AND (tblDevData.Dev = tblPlanData.Dev)
WHERE (((tblDevData.Tab) In ([Forms]![frmMainQuery]![txtTabList]![text])));

[Forms]![frmMainQuery]![txtTabList]![text] = 'Area1','Area2','Area3'

Hi Eric

It appears that you are using myTabList as a WHERE clause. Presumably this
is then used to open a form or report.

The content of myTabList will be something like:
'dog' Or 'cat' Or 'elephant'

This will not work. What you should have is either:
[Species]='dog' Or [Species]='cat' Or [Species]='elephant'
or
[Species] IN ('dog', 'cat', 'elephant')
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm trying to remember ( and I wasn't ever really good at it) how to take
infromation from a list box, save selected items in another to be used as
query crieteria. I can generate the second list. However, the query
doesn't
like the info. Now what. Here's what I have so far.

Public myTablist As Variant
Public varTablist As Variant
Public myTabNames

Sub RowsSelected()
Dim ctlTabs As Control
Dim myInt1 As Integer
Dim myVar1
' Return Control object variable pointing to list box.
Set ctlTabs = Forms!frmMainQuery!cboTabList
' Enumerate through selected items.

For Each varTablist In ctlTabs.ItemsSelected
' Print value of bound column.

If myInt1 = 0 Then
cmbTabListRpt.RowSource = ctlTabs.ItemData(varTablist)
myTablist = ctlTabs.ItemData(varTablist)
Else
cmbTabListRpt.RowSource = myVar1 & ";" &
ctlTabs.ItemData(varTablist)
myTablist = "'" & myTablist & "'" & " Or " & "'" &
ctlTabs.ItemData(varTablist) & "'"
End If

myVar1 = cmbTabListRpt.RowSource
myInt1 = myInt1 + 1
Next varTablist

txtTabList = myTablist
End Sub

Private Sub cboTabList_AfterUpdate()
Call RowsSelected
End Sub

 

[Graham Mandeno]

Hi Eric

Remove the WHERE clause from the query and provide it when you open the
form/report:

DoCmd.OpenForm "YourForm", WhereCondition:="Tab=" & Me!txtTabList

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Thanks - I can get the SQL Query to work with the info you've supplied.
And
Yes - sorry I wasn't clear - I have a form that a user will interface with
to
select criteria to run a query query (this will be duplicated to run from
another form to run reports). I'm still not getting the data to transfer
correctly into the query.

The SQL query is

SELECT tblDevData.Dev, tblDevData.Qtr, tblDevData.Tab
FROM tblDevData INNER JOIN tblPlanData ON (tblDevData.Qtr =
tblPlanData.Qtr)
AND (tblDevData.Tab = tblPlanData.Tab) AND (tblDevData.Dev =
tblPlanData.Dev)
WHERE (((tblDevData.Tab) In
([Forms]![frmMainQuery]![txtTabList]![text])));

[Forms]![frmMainQuery]![txtTabList]![text] = 'Area1','Area2','Area3'

Hi Eric

It appears that you are using myTabList as a WHERE clause. Presumably
this
is then used to open a form or report.

The content of myTabList will be something like:
'dog' Or 'cat' Or 'elephant'

This will not work. What you should have is either:
[Species]='dog' Or [Species]='cat' Or [Species]='elephant'
or
[Species] IN ('dog', 'cat', 'elephant')
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I'm trying to remember ( and I wasn't ever really good at it) how to
take
infromation from a list box, save selected items in another to be used
as
query crieteria. I can generate the second list. However, the query
doesn't
like the info. Now what. Here's what I have so far.

Public myTablist As Variant
Public varTablist As Variant
Public myTabNames

Sub RowsSelected()
Dim ctlTabs As Control
Dim myInt1 As Integer
Dim myVar1
' Return Control object variable pointing to list box.
Set ctlTabs = Forms!frmMainQuery!cboTabList
' Enumerate through selected items.

For Each varTablist In ctlTabs.ItemsSelected
' Print value of bound column.

If myInt1 = 0 Then
cmbTabListRpt.RowSource = ctlTabs.ItemData(varTablist)
myTablist = ctlTabs.ItemData(varTablist)
Else
cmbTabListRpt.RowSource = myVar1 & ";" &
ctlTabs.ItemData(varTablist)
myTablist = "'" & myTablist & "'" & " Or " & "'" &
ctlTabs.ItemData(varTablist) & "'"
End If

myVar1 = cmbTabListRpt.RowSource
myInt1 = myInt1 + 1
Next varTablist

txtTabList = myTablist
End Sub

Private Sub cboTabList_AfterUpdate()
Call RowsSelected
End Sub