Prev: publish option is greyed out in access 2007
Next: Running Yearly Sum
From: Zach on 6 Apr 2010 17:56
Hello, I am trying to work though getting multiple muliselect listboxes to
work. Got the first one to work but I can't get the second one to work.
They are going to be cascading to filter a subform.

my code that doesn't work is:

Private Sub BarType_Click()
Dim MyDB2 As Database
Dim qdf2 As QueryDef
Dim i2 As Integer, strSQL2 As String
Dim strwhere2 As String, strIN2 As String
Dim ctl2 As Control
Dim frm2 As Form
Dim varItm2 As Variant

sDocName2 = "BarInquiry SearchBarTypes"

Set MyDB2 = CurrentDb()
Set frm2 = Forms!BarInquiry
Set ctl2 = frm2!BarType


strSQL2 = "SELECT bar.[BAR TYPE], bar.COUNTDATE , bar.length, bar.[bar
size] FROM Bar" 'this is the table that the listbox is built from.

For Each varItm2 In ctl2.ItemsSelected
strIN2 = strIN2 & "'" & ctl2.ItemData(varItm2) & "',"
Next varItm2

strwhere2 = " WHERE ((([Bar].[BAR TYPE]) in (" & Left(strIN2,
Len(strIN2) - 1) & ")))"

strSQL2 = strSQL2 & strwhere2
Me.Refresh
On Error Resume Next
MyDB2.QueryDefs.Delete sDocName2

Forms![BarInquiry subform].Form!RecordSource = strSQL2
End Sub

any help?

Thanks!
From: Tom Wickerath AOS168b AT comcast DOT on 7 Apr 2010 01:20
Hi Zach,

Try inserting some Debug.Print statements, so that you can see if the
expected output is printed to the Immediate Window. For example:

For Each varItm2 In ctl2.ItemsSelected
strIN2 = strIN2 & "'" & ctl2.ItemData(varItm2) & "',"
Next varItm2

Debug.print strIN2

strwhere2 = " WHERE ((([Bar].[BAR TYPE]) in (" & Left(strIN2,
Len(strIN2) - 1) & ")))"

Debug.print strwhere2

strSQL2 = strSQL2 & strwhere2

Debug.print strSQL2


Examine the output of each print statement, checking for things like SQL
keywords that do not have spaces in-between them. Copy the final SQL
statement, strSQL2, and paste it into the SQL view of a new query. Try
running it.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

"Zach" wrote:

> Hello, I am trying to work though getting multiple muliselect listboxes to
> work. Got the first one to work but I can't get the second one to work.
> They are going to be cascading to filter a subform.
>
> my code that doesn't work is:
>
> Private Sub BarType_Click()
> Dim MyDB2 As Database
> Dim qdf2 As QueryDef
> Dim i2 As Integer, strSQL2 As String
> Dim strwhere2 As String, strIN2 As String
> Dim ctl2 As Control
> Dim frm2 As Form
> Dim varItm2 As Variant
>
> sDocName2 = "BarInquiry SearchBarTypes"
>
> Set MyDB2 = CurrentDb()
> Set frm2 = Forms!BarInquiry
> Set ctl2 = frm2!BarType
>
>
> strSQL2 = "SELECT bar.[BAR TYPE], bar.COUNTDATE , bar.length, bar.[bar
> size] FROM Bar" 'this is the table that the listbox is built from.
>
> For Each varItm2 In ctl2.ItemsSelected
> strIN2 = strIN2 & "'" & ctl2.ItemData(varItm2) & "',"
> Next varItm2
>
> strwhere2 = " WHERE ((([Bar].[BAR TYPE]) in (" & Left(strIN2,
> Len(strIN2) - 1) & ")))"
>
> strSQL2 = strSQL2 & strwhere2
> Me.Refresh
> On Error Resume Next
> MyDB2.QueryDefs.Delete sDocName2
>
> Forms![BarInquiry subform].Form!RecordSource = strSQL2
> End Sub
>
> any help?
>
> Thanks!
 | 
Pages: 1
Prev: publish option is greyed out in access 2007
Next: Running Yearly Sum