Multi Select List Box

W

Wendy

I am trying to use a form with a multi select list box to create a query and
then export the data to Excel. Everything works, except the query that is
exported includes all of my data, not just my selections in the list box.

I read in another post that I would need to Save the query, as a new query
with the results and then export. I didn't quite understand the explanation
given.

Here is the code I am using...could use some specific help.

Thank you...Wendy

Private Sub OK_Click()

Dim varItem As Variant
Dim strInClause As String
If Me!lstGLAccount.ItemsSelected.Count = 0 Then
MsgBox ("Please select at least one GL Account.")
Else
strInClause = "[GL_Account] IN ("
For Each varItem In Me!lstGLAccount.ItemsSelected
strInClause = strInClause & """" & Me!lstGLAccount.Column(0,
varItem) & """" & ","
Next varItem
strInClause = Left(strInClause, Len(strInClause) - 1) & ")"
End If

DoCmd.OutputTo acOutputQuery, "qryF2GLSummary_Export", acFormatXLS, , True
DoCmd.Close acForm, "frmGLAccount_Export", acSaveYes
 
D

Dirk Goldgar

Wendy said:
I am trying to use a form with a multi select list box to create a
query and then export the data to Excel. Everything works, except the
query that is exported includes all of my data, not just my
selections in the list box.

I read in another post that I would need to Save the query, as a new
query with the results and then export. I didn't quite understand the
explanation given.

Here is the code I am using...could use some specific help.

Thank you...Wendy

Private Sub OK_Click()

Dim varItem As Variant
Dim strInClause As String
If Me!lstGLAccount.ItemsSelected.Count = 0 Then
MsgBox ("Please select at least one GL Account.")
Else
strInClause = "[GL_Account] IN ("
For Each varItem In Me!lstGLAccount.ItemsSelected
strInClause = strInClause & """" & Me!lstGLAccount.Column(0,
varItem) & """" & ","
Next varItem
strInClause = Left(strInClause, Len(strInClause) - 1) & ")"
End If

DoCmd.OutputTo acOutputQuery, "qryF2GLSummary_Export", acFormatXLS, ,
True DoCmd.Close acForm, "frmGLAccount_Export", acSaveYes
Click to expand...


There's nothing in that code that actually applied the criterion you've
built to the query you're exporting. To make this approach work, you
need to update the SQL of the query being exported to add that criterion
to a WHERE clause.

Let's try doing this by using a special query intended for the purpose.
Leave qryF2GLSummary_Export as it is, but create a new query with this
SQL:

SELECT * FROM qryF2GLSummary_Export;

Save it with the name "qryF2GLSummary_Export_Filtered".

Now change your code so that, after building strInClause, it does this:

'----- start of code -----
' (after building strInClause)

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

CurrentDb.QueryDefs("qryF2GLSummary_Export_Filtered").SQL = _
"SELECT * FROM qryF2GLSummary_Export " & _
"WHERE " & strInClause & ";"

DoCmd.OutputTo _
acOutputQuery, _
"qryF2GLSummary_Export_Filtered", _
acFormatXLS, , True

DoCmd.Close acForm, "frmGLAccount_Export", acSaveYes
'----- end of code -----

That's untested, but something like it ought to work.
 
W

Wendy

Thank you.
I am getting close. I am getting a syntax error in the FROM clause.
Any ideas to help steer me in the right direction?


Dirk Goldgar said:
Wendy said:
I am trying to use a form with a multi select list box to create a
query and then export the data to Excel. Everything works, except the
query that is exported includes all of my data, not just my
selections in the list box.

I read in another post that I would need to Save the query, as a new
query with the results and then export. I didn't quite understand the
explanation given.

Here is the code I am using...could use some specific help.

Thank you...Wendy

Private Sub OK_Click()

Dim varItem As Variant
Dim strInClause As String
If Me!lstGLAccount.ItemsSelected.Count = 0 Then
MsgBox ("Please select at least one GL Account.")
Else
strInClause = "[GL_Account] IN ("
For Each varItem In Me!lstGLAccount.ItemsSelected
strInClause = strInClause & """" & Me!lstGLAccount.Column(0,
varItem) & """" & ","
Next varItem
strInClause = Left(strInClause, Len(strInClause) - 1) & ")"
End If

DoCmd.OutputTo acOutputQuery, "qryF2GLSummary_Export", acFormatXLS, ,
True DoCmd.Close acForm, "frmGLAccount_Export", acSaveYes
Click to expand...


There's nothing in that code that actually applied the criterion you've
built to the query you're exporting. To make this approach work, you
need to update the SQL of the query being exported to add that criterion
to a WHERE clause.

Let's try doing this by using a special query intended for the purpose.
Leave qryF2GLSummary_Export as it is, but create a new query with this
SQL:

SELECT * FROM qryF2GLSummary_Export;

Save it with the name "qryF2GLSummary_Export_Filtered".

Now change your code so that, after building strInClause, it does this:

'----- start of code -----
' (after building strInClause)

Dim db As DAO.Database
Dim qdf As DAO.QueryDef

CurrentDb.QueryDefs("qryF2GLSummary_Export_Filtered").SQL = _
"SELECT * FROM qryF2GLSummary_Export " & _
"WHERE " & strInClause & ";"

DoCmd.OutputTo _
acOutputQuery, _
"qryF2GLSummary_Export_Filtered", _
acFormatXLS, , True

DoCmd.Close acForm, "frmGLAccount_Export", acSaveYes
'----- end of code -----

That's untested, but something like it ought to work.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

Wendy said:
Thank you.
I am getting close. I am getting a syntax error in the FROM clause.
Any ideas to help steer me in the right direction?
Click to expand...

Could you please post the code you're using and indicate at what point
you get the error?
 
W

Wendy

Appreciate your help...this is advanced coding for me.

Error occurs at CurrentDb

On my Form, in addition to the Multi Select List Box I have two fileds for
date range [StartDate] and [EndDate] as well as a combo box to select
[StepType]...these parameters are in the underlying qryF2Summary_Export


Could this cause the error in the FROM clause?

Private Sub OK_Click()

Dim varItem As Variant
Dim strInClause As String
If Me!lstGLAccount.ItemsSelected.Count = 0 Then
MsgBox ("Please select at least one GL Account.")
Else
strInClause = "[GL_Account] IN ("
For Each varItem In Me!lstGLAccount.ItemsSelected
strInClause = strInClause & """" & Me!lstGLAccount.Column(0,
varItem) & """" & ","
Next varItem
strInClause = Left(strInClause, Len(strInClause) - 1) & ")"
End If

Dim db As DAO.Database
Dim qdf As DAO.QueryDef


CurrentDb.QueryDefs("qryF2GLSummary_Export_Filtered").SQL =
"SELECT* FROM qryF2GLSummary_Export" & _
"WHERE" & strInClause & ";"

DoCmd.OutputTo acOutputQuery, "qryF2GLSummary_Export_Filtered", acFormatXLS,
, True
DoCmd.Close acForm, "frmGLAccount_Export", acSaveYes
 
D

Dirk Goldgar

Wendy said:
Appreciate your help...this is advanced coding for me.

Error occurs at CurrentDb

On my Form, in addition to the Multi Select List Box I have two
fileds for date range [StartDate] and [EndDate] as well as a combo
box to select [StepType]...these parameters are in the underlying
qryF2Summary_Export


Could this cause the error in the FROM clause?

Private Sub OK_Click()

Dim varItem As Variant
Dim strInClause As String
If Me!lstGLAccount.ItemsSelected.Count = 0 Then
MsgBox ("Please select at least one GL Account.")
Else
strInClause = "[GL_Account] IN ("
For Each varItem In Me!lstGLAccount.ItemsSelected
strInClause = strInClause & """" & Me!lstGLAccount.Column(0,
varItem) & """" & ","
Next varItem
strInClause = Left(strInClause, Len(strInClause) - 1) & ")"
End If

Dim db As DAO.Database
Dim qdf As DAO.QueryDef


CurrentDb.QueryDefs("qryF2GLSummary_Export_Filtered").SQL =
"SELECT* FROM qryF2GLSummary_Export" & _
"WHERE" & strInClause & ";"

DoCmd.OutputTo acOutputQuery, "qryF2GLSummary_Export_Filtered",
acFormatXLS, , True
DoCmd.Close acForm, "frmGLAccount_Export", acSaveYes
Click to expand...

I see what it is: you took out some of the spaces I had in the literals
that compose the SQL string. You have this:
"SELECT* FROM qryF2GLSummary_Export" & _
"WHERE" & strInClause & ";"
Click to expand...

where it should be this:

"SELECT * FROM qryF2GLSummary_Export " & _
"WHERE " & strInClause & ";"

There's a space between "SELECT" and "*", another after "_Export", and
another after "WHERE".
 
W

Wendy

Perfect!

Thank you

Dirk Goldgar said:
Wendy said:
Appreciate your help...this is advanced coding for me.

Error occurs at CurrentDb

On my Form, in addition to the Multi Select List Box I have two
fileds for date range [StartDate] and [EndDate] as well as a combo
box to select [StepType]...these parameters are in the underlying
qryF2Summary_Export


Could this cause the error in the FROM clause?

Private Sub OK_Click()

Dim varItem As Variant
Dim strInClause As String
If Me!lstGLAccount.ItemsSelected.Count = 0 Then
MsgBox ("Please select at least one GL Account.")
Else
strInClause = "[GL_Account] IN ("
For Each varItem In Me!lstGLAccount.ItemsSelected
strInClause = strInClause & """" & Me!lstGLAccount.Column(0,
varItem) & """" & ","
Next varItem
strInClause = Left(strInClause, Len(strInClause) - 1) & ")"
End If

Dim db As DAO.Database
Dim qdf As DAO.QueryDef


CurrentDb.QueryDefs("qryF2GLSummary_Export_Filtered").SQL =
"SELECT* FROM qryF2GLSummary_Export" & _
"WHERE" & strInClause & ";"

DoCmd.OutputTo acOutputQuery, "qryF2GLSummary_Export_Filtered",
acFormatXLS, , True
DoCmd.Close acForm, "frmGLAccount_Export", acSaveYes
Click to expand...

I see what it is: you took out some of the spaces I had in the literals
that compose the SQL string. You have this:
"SELECT* FROM qryF2GLSummary_Export" & _
"WHERE" & strInClause & ";"
Click to expand...

where it should be this:

"SELECT * FROM qryF2GLSummary_Export " & _
"WHERE " & strInClause & ";"

There's a space between "SELECT" and "*", another after "_Export", and
another after "WHERE".

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multi Select List 4
Multi Select Lists 5
Multi-Select List Box as Query Parameter 1
Multi-select list box as query parameter 15
Show the value of a multi-select list box selection 2
Two multi-select list boxes 3
Using Combo Box and Multi List Box on Form - Send Parameters to Qu 1
Using multi-select list box data in a Query 1

Top