Multi-Select List Box 3075 Syntax Error

[trose178]

Good day all, I am working on a multi-select list box for a standard
question checklist database and I am running into a syntax error in
the code that I cannot seem to correct. I will also note that I am
using Allen Browne's multi-select list box for a report as a guide. I
should also note that my access skills are not the best so I may need
some explaining on certain things.

First let me give some background on the database:
I have a query titled Questions By Category that has the following
fields in it
Categories.ID
Standard Questions.SubCategory1
Standard Questions.Question

I played around with the code a little bit to make sure all fields
were changed to match my database, but I am running into a syntax
error 3075 on the following line:

strWhere = "[ID] IN (" & Left$(strWhere, lngLen) & ")"

I searched around a little bit on here, but couldn't really find
anything that matched this same problem. If anyone could advise I'd
greatly appreciate it. If any additional information is needed please
do not hesitate to ask.

Thank You,
Tim

[Salad]

tr******@gmail.com wrote:

Good day all, I am working on a multi-select list box for a standard
question checklist database and I am running into a syntax error in
the code that I cannot seem to correct. I will also note that I am
using Allen Browne's multi-select list box for a report as a guide. I
should also note that my access skills are not the best so I may need
some explaining on certain things.

First let me give some background on the database:
I have a query titled Questions By Category that has the following
fields in it
Categories.ID
Standard Questions.SubCategory1
Standard Questions.Question

I played around with the code a little bit to make sure all fields
were changed to match my database, but I am running into a syntax
error 3075 on the following line:

strWhere = "[ID] IN (" & Left$(strWhere, lngLen) & ")"

I searched around a little bit on here, but couldn't really find
anything that matched this same problem. If anyone could advise I'd
greatly appreciate it. If any additional information is needed please
do not hesitate to ask.

Thank You,
Tim

If you have an OnError statement in the subroutine put a ' in front of
the word OnError. When the code breaks on the line you should be able
to see the value of strWhere and lngLen by placing the cursor over those
words. Or you could enter
Debug.Print "strWhere " & strWhere
Debug.Print "lngLen " & lngLen
Debug.Print "[ID] IN (" & Left$(strWhere, lngLen) & ")"
just prior to the line that blows up to see the values in the immediate
window. Maybe there's something incorrect about the values. The
resulting set should be something like
"[ID] IN (1,2,3)"

Pate Pate
http://www.youtube.com/watch?v=9Nbxryb5YHc

[trose178]

On Jul 17, 12:05*pm, Salad <[e-mail]:

[e-mail] wrote:

Good day all, I am working on a multi-select list box for a standard
question checklist database and I am running into a syntax error in
the code that I cannot seem to correct. *I will also note that I am
using Allen Browne's multi-select list box for a report as a guide. *I
should also note that my access skills are not the best so I may need
some explaining on certain things.

First let me give some background on the database:
I have a query titled Questions By Category that has the following
fields in it
Categories.ID
Standard Questions.SubCategory1
Standard Questions.Question

I played around with the code a little bit to make sure all fields
were changed to match my database, but I am running into a syntax
error 3075 on the following line:

* * * * strWhere = "[ID] IN (" & Left$(strWhere, lngLen) & ")"

I searched around a little bit on here, but couldn't really find
anything that matched this same problem. *If anyone could advise I'd
greatly appreciate it. *If any additional information is needed please
do not hesitate to ask.

Thank You,
Tim

If you have an OnError statement in the subroutine put a ' in front of
the word OnError. *When the code breaks on the line you should be able
to see the value of strWhere and lngLen by placing the cursor over those
words. *Or you could enter
* * * * Debug.Print "strWhere " & strWhere
* * * * Debug.Print "lngLen " & lngLen
* * * * Debug.Print "[ID] IN (" & Left$(strWhere, lngLen) & ")"
just prior to the line that blows up to see the values in the immediate
window. *Maybe there's something incorrect about the values. *The
resulting set should be something like
* * * * "[ID] IN (1,2,3)"

Pate Patehttp://www.youtube.com/watch?v=9Nbxryb5YHc

Sorry I am not very familiar with VB or much debugging, I put those
lines of code in there, but where is it going to print to? I was going
to try to step through it, but it also does not let me do that.
Doesn't say why, just get the silly windows Ding! noise. I really
think that I just didn't transfer it over correctly.

http://allenbrowne.com/ser-50.html

That is the webpage where I have been getting my help from and my code
is basically the same with the line above different. I really think it
has something to do with the [ID] in the brackets because in the
example it is different, but I am trying to fit this setup into my own
database so I may have something named incorrectly. If a sample of my
database is required I would have no problem providing just let me
know. I cannot release the actual because of some of the information
in there, but I will create a sample database with random fields but
the same basic layout if need be. I would really like to get this
working.

[Salad]

tr******@gmail.com wrote:

On Jul 17, 12:05 pm, Salad <[e-mail]:

>>[e-mail] wrote:

>>>Good day all, I am working on a multi-select list box for a standard
question checklist database and I am running into a syntax error in
the code that I cannot seem to correct. I will also note that I am
using Allen Browne's multi-select list box for a report as a guide. I
should also note that my access skills are not the best so I may need
some explaining on certain things.

>>>First let me give some background on the database:
I have a query titled Questions By Category that has the following
fields in it
Categories.ID
Standard Questions.SubCategory1
Standard Questions.Question

>>>I played around with the code a little bit to make sure all fields
were changed to match my database, but I am running into a syntax
error 3075 on the following line:

>> strWhere = "[ID] IN (" & Left$(strWhere, lngLen) & ")"

>>>I searched around a little bit on here, but couldn't really find
anything that matched this same problem. If anyone could advise I'd
greatly appreciate it. If any additional information is needed please
do not hesitate to ask.

>>>Thank You,
Tim

If you have an OnError statement in the subroutine put a ' in front of
the word OnError. When the code breaks on the line you should be able
to see the value of strWhere and lngLen by placing the cursor over those
words. Or you could enter
Debug.Print "strWhere " & strWhere
Debug.Print "lngLen " & lngLen
Debug.Print "[ID] IN (" & Left$(strWhere, lngLen) & ")"
just prior to the line that blows up to see the values in the immediate
window. Maybe there's something incorrect about the values. The
resulting set should be something like
"[ID] IN (1,2,3)"

Pate Patehttp://www.youtube.com/watch?v=9Nbxryb5YHc

Sorry I am not very familiar with VB or much debugging, I put those
lines of code in there, but where is it going to print to? I was going
to try to step through it, but it also does not let me do that.
Doesn't say why, just get the silly windows Ding! noise. I really
think that I just didn't transfer it over correctly.

http://allenbrowne.com/ser-50.html

That is the webpage where I have been getting my help from and my code
is basically the same with the line above different. I really think it
has something to do with the [ID] in the brackets because in the
example it is different, but I am trying to fit this setup into my own
database so I may have something named incorrectly. If a sample of my
database is required I would have no problem providing just let me
know. I cannot release the actual because of some of the information
in there, but I will create a sample database with random fields but
the same basic layout if need be. I would really like to get this
working.

If you Debug.Print something you can check the Immediate Window...I
think it's under View or Debug. If you click to the left of the
offending line on the vertical bar and red dot should appear. It will
break at that point.

[trose178]

On Jul 17, 2:10*pm, Salad <[e-mail]:

[e-mail] wrote:

On Jul 17, 12:05 pm, Salad <[e-mail]:

>[e-mail] wrote:

>>Good day all, I am working on a multi-select list box for a standard
question checklist database and I am running into a syntax error in
the code that I cannot seem to correct. *I will also note that I am
using Allen Browne's multi-select list box for a report as a guide. *I
should also note that my access skills are not the best so I may need
some explaining on certain things.

>>First let me give some background on the database:
I have a query titled Questions By Category that has the following
fields in it
Categories.ID
Standard Questions.SubCategory1
Standard Questions.Question

>>I played around with the code a little bit to make sure all fields
were changed to match my database, but I am running into a syntax
error 3075 on the following line:

>* * * *strWhere = "[ID] IN (" & Left$(strWhere, lngLen) & ")"

>>I searched around a little bit on here, but couldn't really find
anything that matched this same problem. *If anyone could advise I'd
greatly appreciate it. *If any additional information is needed please
do not hesitate to ask.

>>Thank You,
Tim

>If you have an OnError statement in the subroutine put a ' in front of
the word OnError. *When the code breaks on the line you should be able
to see the value of strWhere and lngLen by placing the cursor over those
words. *Or you could enter
* * * *Debug.Print "strWhere " & strWhere
* * * *Debug.Print "lngLen " & lngLen
* * * *Debug.Print "[ID] IN (" & Left$(strWhere, lngLen) & ")"
just prior to the line that blows up to see the values in the immediate
window. *Maybe there's something incorrect about the values. *The
resulting set should be something like
* * * *"[ID] IN (1,2,3)"

>Pate Patehttp://www.youtube.com/watch?v=9Nbxryb5YHc

Sorry I am not very familiar with VB or much debugging, I put those
lines of code in there, but where is it going to print to? I was going
to try to step through it, but it also does not let me do that.
Doesn't say why, just get the silly windows Ding! noise. *I really
think that I just didn't transfer it over correctly.

http://allenbrowne.com/ser-50.html

That is the webpage where I have been getting my help from and my code
is basically the same with the line above different. I really think it
has something to do with the [ID] in the brackets because in the
example it is different, but I am trying to fit this setup into my own
database so I may have something named incorrectly. If a sample of my
database is required I would have no problem providing just let me
know. *I cannot release the actual because of some of the information
in there, but I will create a sample database with random fields but
the same basic layout if need be. I would really like to get this
working.

If you Debug.Print something you can check the Immediate Window...I
think it's under View or Debug. *If you click to the left of the
offending line on the vertical bar and red dot should appear. *It will
break at that point.

The values I am getting from the Debug.Print are the actual text of
the questions I am trying to fit into the report. I am not sure what
this means to be honest. I have tried changing the [ID] to a few
different instances, but I do not really know what the problem is. I
will probably have to make a sample database I don't think I am
explaining this all that well.

[Salad]

tr******@gmail.com wrote:

On Jul 17, 2:10 pm, Salad <[e-mail]:

>>[e-mail] wrote:

>>>On Jul 17, 12:05 pm, Salad <[e-mail]:

>>>>[e-mail] wrote:

>>>>>Good day all, I am working on a multi-select list box for a standard
>question checklist database and I am running into a syntax error in
>the code that I cannot seem to correct. I will also note that I am
>using Allen Browne's multi-select list box for a report as a guide. I
>should also note that my access skills are not the best so I may need
>some explaining on certain things.

>>>>>First let me give some background on the database:
>I have a query titled Questions By Category that has the following
>fields in it
>Categories.ID
>Standard Questions.SubCategory1
>Standard Questions.Question

>>>>>I played around with the code a little bit to make sure all fields
>were changed to match my database, but I am running into a syntax
>error 3075 on the following line:

>>>> strWhere = "[ID] IN (" & Left$(strWhere, lngLen) & ")"

>>>>>I searched around a little bit on here, but couldn't really find
>anything that matched this same problem. If anyone could advise I'd
>greatly appreciate it. If any additional information is needed please
>do not hesitate to ask.

>>>>>Thank You,
>Tim

>>>>If you have an OnError statement in the subroutine put a ' in front of
the word OnError. When the code breaks on the line you should be able
to see the value of strWhere and lngLen by placing the cursor over those
words. Or you could enter
Debug.Print "strWhere " & strWhere
Debug.Print "lngLen " & lngLen
Debug.Print "[ID] IN (" & Left$(strWhere, lngLen) & ")"
just prior to the line that blows up to see the values in the immediate
window. Maybe there's something incorrect about the values. The
resulting set should be something like
"[ID] IN (1,2,3)"

>>>>Pate Patehttp://www.youtube.com/watch?v=9Nbxryb5YHc

>>>Sorry I am not very familiar with VB or much debugging, I put those
lines of code in there, but where is it going to print to? I was going
to try to step through it, but it also does not let me do that.
Doesn't say why, just get the silly windows Ding! noise. I really
think that I just didn't transfer it over correctly.

>>>http://allenbrowne.com/ser-50.html

>>>That is the webpage where I have been getting my help from and my code
is basically the same with the line above different. I really think it
has something to do with the [ID] in the brackets because in the
example it is different, but I am trying to fit this setup into my own
database so I may have something named incorrectly. If a sample of my
database is required I would have no problem providing just let me
know. I cannot release the actual because of some of the information
in there, but I will create a sample database with random fields but
the same basic layout if need be. I would really like to get this
working.

If you Debug.Print something you can check the Immediate Window...I
think it's under View or Debug. If you click to the left of the
offending line on the vertical bar and red dot should appear. It will
break at that point.

The values I am getting from the Debug.Print are the actual text of
the questions I am trying to fit into the report. I am not sure what
this means to be honest. I have tried changing the [ID] to a few
different instances, but I do not really know what the problem is. I
will probably have to make a sample database I don't think I am
explaining this all that well.

Are you getting your code from http://allenbrowne.com/ser-50.html? If
not, which one? If I were you, I might want to start over and follow
all of the information contained in the document and notes. Also, is ID
numeric or alphanumeric?

strWhere, via Debug, may look like
1,2,3,4,5,
lngLen should equal the length of the string but exclude the trailing
comma ,.

Once executed, the result should be
1,2,3,4,5
Next, it should result in
[ID] In (1,2,3,4,5)

[Jamey]

The values I am getting from the Debug.Print are the actual text of

the questions I am trying to fit into the report. I am not sure what
this means to be honest. I have tried changing the [ID] to a few
different instances, but I do not really know what the problem is. I
will probably have to make a sample database I don't think I am
explaining this all that well.

What's the RowSource, ColumnCount, and BoundColumn of your list box?

Could be your 'ID' field is text rather than a number. If so your
strWhere clause will need quotation marks around each value before
passing it to the DoCmd.OpenReport procedure.

If your 'ID' field is text rather than numbers you'll need to
uncomment this line of code from Allen's example:

'strDelim = """" 'Delimiter appropriate to field type. See
note 1.

Find that line and delete the sigle quotation mark that precedes it.
It should turn from green to black in the code, and then it'll work
unless there's something else screwy going on, which we should be able
to figure out from your RowSource, ColumnCount and BoundColumn
information.

[Jamey]

On Jul 17, 4:55*pm, [e-mail] wrote:

Perfect that fixed my syntax error but now it is telling me the ID
field could be more than one things from my FROM clause. It is
probably because there are two different ID's Category.ID and Standard
Questions.ID, but only the Categories ID is actually being used in the
RowSource. *Is there a way for me to specify Categories.ID in the
code? When I try to put it in the brackets it says invalid bracketing
of name. *This also may not be the correct solution it could also have
to do with my RowSource as well.- Hide quoted text -

- Show quoted text -

[Questions By Category].[ID]

[trose178]

On Jul 17, 5:58*pm, Jamey <[e-mail]:

On Jul 17, 4:55*pm, [e-mail] wrote:

Perfect that fixed my syntax error but now it is telling me the ID
field could be more than one things from my FROM clause. It is
probably because there are two different ID's Category.ID and Standard
Questions.ID, but only the Categories ID is actually being used in the
RowSource. *Is there a way for me to specify Categories.ID in the
code? When I try to put it in the brackets it says invalid bracketing
of name. *This also may not be the correct solution it could also have
to do with my RowSource as well.- Hide quoted text -

- Show quoted text -

[Questions By Category].[ID]

Thank you for all your help, but I am now running into an issue of
where I get a prompt asking for a parameter value for the following
fields:
Questions By Category.ID
Lookup_Category.Category
Lookup_SubCategory1.SubCategory1
Lookup_SubCategory1A.SubCategory1A

This could be because the report was setup before I started doing all
this so I am going to try and make a blank report to see if it fills
it in or not. If not, how would I resolve this issues, I should be
able to select items in the list box and then have it filter them that
way instead of asking me for parameter values.

[trose178]

On Jul 18, 8:48*am, [e-mail] wrote:

On Jul 17, 5:58*pm, Jamey <[e-mail]:

On Jul 17, 4:55*pm, [e-mail] wrote:

Perfect that fixed my syntax error but now it is telling me the ID
field could be more than one things from my FROM clause. It is
probably because there are two different ID's Category.ID and Standard
Questions.ID, but only the Categories ID is actually being used in the
RowSource. *Is there a way for me to specify Categories.ID in the
code? When I try to put it in the brackets it says invalid bracketing
of name. *This also may not be the correct solution it could also have
to do with my RowSource as well.- Hide quoted text -

- Show quoted text -

[Questions By Category].[ID]

Thank you for all your help, but I am now running into an issue of
where I get a prompt asking for a parameter value for the following
fields:
Questions By Category.ID
Lookup_Category.Category
Lookup_SubCategory1.SubCategory1
Lookup_SubCategory1A.SubCategory1A

This could be because the report was setup before I started doing all
this so I am going to try and make a blank report to see if it fills
it in or not. *If not, how would I resolve this issues, I should be
able to select items in the list box and then have it filter them that
way instead of asking me for parameter values.

I created a new report and it fixed this previous problem, but now
when I select items from my list box instead of giving me individual
questions, it gives me all the questions per that category ID, I want
to be able to select individual questions and have only those show up.
I probably have to edit the RowSource stuff and BoundColumn again,
going to mess around with it. If you have any suggestions please post.

[trose178]

On Jul 18, 8:56*am, [e-mail] wrote:

On Jul 18, 8:48*am, [e-mail] wrote:

On Jul 17, 5:58*pm, Jamey <[e-mail]:

On Jul 17, 4:55*pm, [e-mail] wrote:

Perfect that fixed my syntax error but now it is telling me the ID
field could be more than one things from my FROM clause. It is
probably because there are two different ID's Category.ID and Standard
Questions.ID, but only the Categories ID is actually being used in the
RowSource. *Is there a way for me to specify Categories.ID in the
code? When I try to put it in the brackets it says invalid bracketing
of name. *This also may not be the correct solution it could alsohave
to do with my RowSource as well.- Hide quoted text -

- Show quoted text -

[Questions By Category].[ID]

Thank you for all your help, but I am now running into an issue of
where I get a prompt asking for a parameter value for the following
fields:
Questions By Category.ID
Lookup_Category.Category
Lookup_SubCategory1.SubCategory1
Lookup_SubCategory1A.SubCategory1A

This could be because the report was setup before I started doing all
this so I am going to try and make a blank report to see if it fills
it in or not. *If not, how would I resolve this issues, I should be
able to select items in the list box and then have it filter them that
way instead of asking me for parameter values.

I created a new report and it fixed this previous problem, but now
when I select items from my list box instead of giving me individual
questions, it gives me all the questions per that category ID, I want
to be able to select individual questions and have only those show up.
I probably have to edit the RowSource stuff and BoundColumn again,
going to mess around with it. *If you have any suggestions please post.

So I have the functionality working, but I still cant get it to
seperate by individual question instead of category. Can anyone look
at this code and see if it can be done, it seems like it should be
able to.

Option Compare Database

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type.
See note 1.
strDoc = "Questions by Category"

'Loop through the ItemsSelected in the list box.
With Me.lstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen 0 Then
Debug.Print "strWhere " & strWhere
Debug.Print "lngLen " & lngLen
Debug.Print "[ID] IN (" & Left$(strWhere, lngLen) & ")"
strWhere = "[Questions By Category].[ID] IN (" & Left$
(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see
note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub

[Salad]

tr******@gmail.com wrote:

On Jul 18, 8:56 am, [e-mail] wrote:

>>On Jul 18, 8:48 am, [e-mail] wrote:

>>>On Jul 17, 5:58 pm, Jamey <[e-mail]:

>>>>On Jul 17, 4:55 pm, [e-mail] wrote:

>>>>>Perfect that fixed my syntax error but now it is telling me the ID
>field could be more than one things from my FROM clause. It is
>probably because there are two different ID's Category.ID and Standard
>Questions.ID, but only the Categories ID is actually being used in the
>RowSource. Is there a way for me to specify Categories.ID in the
>code? When I try to put it in the brackets it says invalid bracketing
>of name. This also may not be the correct solution it could also have
>to do with my RowSource as well.- Hide quoted text -

>>>>>- Show quoted text -

>>>>[Questions By Category].[ID]

>>>Thank you for all your help, but I am now running into an issue of
where I get a prompt asking for a parameter value for the following
fields:
Questions By Category.ID
Lookup_Category.Category
Lookup_SubCategory1.SubCategory1
Lookup_SubCategory1A.SubCategory1A

>>>This could be because the report was setup before I started doing all
this so I am going to try and make a blank report to see if it fills
it in or not. If not, how would I resolve this issues, I should be
able to select items in the list box and then have it filter them that
way instead of asking me for parameter values.

I created a new report and it fixed this previous problem, but now
when I select items from my list box instead of giving me individual
questions, it gives me all the questions per that category ID, I want
to be able to select individual questions and have only those show up.
I probably have to edit the RowSource stuff and BoundColumn again,
going to mess around with it. If you have any suggestions please post.

So I have the functionality working, but I still cant get it to
seperate by individual question instead of category. Can anyone look
at this code and see if it can be done, it seems like it should be
able to.

Option Compare Database

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

'strDelim = """" 'Delimiter appropriate to field type.
See note 1.
strDoc = "Questions by Category"

'Loop through the ItemsSelected in the list box.
With Me.lstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "
End If
Next
End With

'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen 0 Then
Debug.Print "strWhere " & strWhere
Debug.Print "lngLen " & lngLen
Debug.Print "[ID] IN (" & Left$(strWhere, lngLen) & ")"
strWhere = "[Questions By Category].[ID] IN (" & Left$
(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

'Report will not filter if open, so close it. For Access 97, see
note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
Exit Sub

Err_Handler:
If Err.Number <2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler

End Sub

Maybe you can help us by telling us what it is you are doing. There's
too much stuff to keep up with all of this thread; syntax errors,
listbox errors, report errors, maybe other errors.

You have a listbox. What are the fields you display in the listbox?
Category, question, answer? You tag the category and you get
categories. Sounds right. But you have sub categories. Are the
subcategories in the listbox as well? Is the ID of the subcategory in
the listbox as well? Don't you need to be selecting on the question or
subcategory id, not the category id?

If you spend some time detailing what you have now, what you need, and
what you are doing without being too verbose you might get a solution.

[trose178]

On Jul 18, 1:48*pm, Salad <[e-mail]:

[e-mail] wrote:

On Jul 18, 8:56 am, [e-mail] wrote:

>On Jul 18, 8:48 am, [e-mail] wrote:

>>On Jul 17, 5:58 pm, Jamey <[e-mail]:

>>>On Jul 17, 4:55 pm, [e-mail] wrote:

>>>>Perfect that fixed my syntax error but now it is telling me the ID
field could be more than one things from my FROM clause. It is
probably because there are two different ID's Category.ID and Standard
Questions.ID, but only the Categories ID is actually being used in the
RowSource. *Is there a way for me to specify Categories.ID in the
code? When I try to put it in the brackets it says invalid bracketing
of name. *This also may not be the correct solution it could also have
to do with my RowSource as well.- Hide quoted text -

>>>>- Show quoted text -

>>>[Questions By Category].[ID]

>>Thank you for all your help, but I am now running into an issue of
where I get a prompt asking for a parameter value for the following
fields:
Questions By Category.ID
Lookup_Category.Category
Lookup_SubCategory1.SubCategory1
Lookup_SubCategory1A.SubCategory1A

>>This could be because the report was setup before I started doing all
this so I am going to try and make a blank report to see if it fills
it in or not. *If not, how would I resolve this issues, I should be
able to select items in the list box and then have it filter them that
way instead of asking me for parameter values.

>I created a new report and it fixed this previous problem, but now
when I select items from my list box instead of giving me individual
questions, it gives me all the questions per that category ID, I want
to be able to select individual questions and have only those show up.
I probably have to edit the RowSource stuff and BoundColumn again,
going to mess around with it. *If you have any suggestions please post.

So I have the functionality working, but I still cant get it to
seperate by individual question instead of category. *Can anyone look
at this code and see if it can be done, it seems like it should be
able to.

Option Compare Database

Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
* * 'Purpose: *Open the report filtered to the items selected in the
list box.
* * 'Author: * Allen J Browne, 2004. *http://allenbrowne.com
* * Dim varItem As Variant * * *'Selected items
* * Dim strWhere As String * * *'String to use as WhereCondition
* * Dim strDescrip As String * *'Description of WhereCondition
* * Dim lngLen As Long * * * * *'Length of string
* * Dim strDelim As String * * *'Delimiter for this field type.
* * Dim strDoc As String * * * *'Name of report to open.

* * 'strDelim = """" * * * * * *'Delimiter appropriate to field type.
See note 1.
* * strDoc = "Questions by Category"

* * 'Loop through the ItemsSelected in the list box.
* * With Me.lstCategory
* * * * For Each varItem In .ItemsSelected
* * * * * * If Not IsNull(varItem) Then
* * * * * * * * 'Build up the filter from the bound column (hidden).
* * * * * * * * strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
* * * * * * * * 'Build up the description from the textin the visible
column. See note 2.
* * * * * * * * strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "
* * * * * * End If
* * * * Next
* * End With

* * 'Remove trailing comma. Add field name, IN operator, and brackets.
* * lngLen = Len(strWhere) - 1
* * If lngLen 0 Then
* * * * Debug.Print "strWhere " & strWhere
* * * * Debug.Print "lngLen " & lngLen
* * * * Debug.Print "[ID] IN (" & Left$(strWhere, lngLen) & ")"
* * * * strWhere = "[Questions By Category].[ID] IN (" & Left$
(strWhere, lngLen) & ")"
* * * * lngLen = Len(strDescrip) - 2
* * * * If lngLen 0 Then
* * * * * * strDescrip = "Categories: " & Left$(strDescrip, lngLen)
* * * * End If
* * End If

* * 'Report will not filter if open, so close it. For Access 97, see
note 3.
* * If CurrentProject.AllReports(strDoc).IsLoaded Then
* * * * DoCmd.Close acReport, strDoc
* * End If

* * 'Omit the last argument for Access 2000 and earlier. See note 4..
* * DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

Exit_Handler:
* * Exit Sub

Err_Handler:
* * If Err.Number <2501 Then *'Ignore "Report cancelled" error.
* * * * MsgBox "Error " & Err.Number & " - " & Err.Description,,
"cmdPreview_Click"
* * End If
* * Resume Exit_Handler

End Sub

Maybe you can help us by telling us what it is you are doing. *There's
too much stuff to keep up with all of this thread; syntax errors,
listbox errors, report errors, maybe other errors.

You have a listbox. *What are the fields you display in the listbox?
Category, question, answer? *You tag the category and you get
categories. *Sounds right. *But you have sub categories. *Are the
subcategories in the listbox as well? *Is the ID of the subcategory in
the listbox as well? *Don't you need to be selecting on the question or
subcategory id, not the category id?

If you spend some time detailing what you have now, what you need, and
what you are doing without being too verbose you might get a solution.

Yea I know this is somewhat hard to follow sorry about that. What I
have now is a query which includes the Category.ID, SQ(Standard
Questions).SubCategory1A, and SQ.Question.
The list box RowSource is as follows SELECT [Questions By
Category].ID, [Questions By Category].SubCategory1, [Questions By
Category].Question FROM [Questions By Category];
The subcategory ID is not in the listbox, and I yes I should be
setting it up to select the question ID very good thought. I am going
to try to mess with that now.

I have tried messing with the code so that it would select individual
questions but so far unsuccessful. I am somewhat in the dark in terms
of information needed sometimes that is why I am vague with some of my
reponses

[Salad]

tr******@gmail.com wrote:

On Jul 18, 1:48 pm, Salad <[e-mail]:

>>[e-mail] wrote:

>>>On Jul 18, 8:56 am, [e-mail] wrote:

>>>>On Jul 18, 8:48 am, [e-mail] wrote:

>>>>>On Jul 17, 5:58 pm, Jamey <[e-mail]:

>>>>>>On Jul 17, 4:55 pm, [e-mail] wrote:

>>>>>>>Perfect that fixed my syntax error but now it is telling me the ID
>>>field could be more than one things from my FROM clause. It is
>>>probably because there are two different ID's Category.ID and Standard
>>>Questions.ID, but only the Categories ID is actually being used in the
>>>RowSource. Is there a way for me to specify Categories.ID in the
>>>code? When I try to put it in the brackets it says invalid bracketing
>>>of name. This also may not be the correct solution it could also have
>>>to do with my RowSource as well.- Hide quoted text -

>>>>>>>- Show quoted text -

>>>>>>[Questions By Category].[ID]

>>>>>Thank you for all your help, but I am now running into an issue of
>where I get a prompt asking for a parameter value for the following
>fields:
>Questions By Category.ID
>Lookup_Category.Category
>Lookup_SubCategory1.SubCategory1
>Lookup_SubCategory1A.SubCategory1A

>>>>>This could be because the report was setup before I started doing all
>this so I am going to try and make a blank report to see if it fills
>it in or not. If not, how would I resolve this issues, I should be
>able to select items in the list box and then have it filter them that
>way instead of asking me for parameter values.

>>>>I created a new report and it fixed this previous problem, but now
when I select items from my list box instead of giving me individual
questions, it gives me all the questions per that category ID, I want
to be able to select individual questions and have only those show up.
I probably have to edit the RowSource stuff and BoundColumn again,
going to mess around with it. If you have any suggestions please post.

>>>So I have the functionality working, but I still cant get it to
seperate by individual question instead of category. Can anyone look
at this code and see if it can be done, it seems like it should be
able to.

>>>Option Compare Database

>>>Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

>> 'strDelim = """" 'Delimiter appropriate to field type.
See note 1.
strDoc = "Questions by Category"

>> 'Loop through the ItemsSelected in the list box.
With Me.lstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
'Build up the description from the text in the visible
column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "
End If
Next
End With

>> 'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen 0 Then
Debug.Print "strWhere " & strWhere
Debug.Print "lngLen " & lngLen
Debug.Print "[ID] IN (" & Left$(strWhere, lngLen) & ")"
strWhere = "[Questions By Category].[ID] IN (" & Left$
(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

>> 'Report will not filter if open, so close it. For Access 97, see
note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

>> 'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

>>>Exit_Handler:
Exit Sub

>>>Err_Handler:
If Err.Number <2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
End If
Resume Exit_Handler

>>>End Sub

Maybe you can help us by telling us what it is you are doing. There's
too much stuff to keep up with all of this thread; syntax errors,
listbox errors, report errors, maybe other errors.

You have a listbox. What are the fields you display in the listbox?
Category, question, answer? You tag the category and you get
categories. Sounds right. But you have sub categories. Are the
subcategories in the listbox as well? Is the ID of the subcategory in
the listbox as well? Don't you need to be selecting on the question or
subcategory id, not the category id?

If you spend some time detailing what you have now, what you need, and
what you are doing without being too verbose you might get a solution.

Yea I know this is somewhat hard to follow sorry about that. What I
have now is a query which includes the Category.ID, SQ(Standard
Questions).SubCategory1A, and SQ.Question.
The list box RowSource is as follows SELECT [Questions By
Category].ID, [Questions By Category].SubCategory1, [Questions By
Category].Question FROM [Questions By Category];
The subcategory ID is not in the listbox, and I yes I should be
setting it up to select the question ID very good thought. I am going
to try to mess with that now.

I have tried messing with the code so that it would select individual
questions but so far unsuccessful. I am somewhat in the dark in terms
of information needed sometimes that is why I am vague with some of my
reponses

Dim var As Variant
Dim strHold
For Each var In Me.ListBox.ItemsSelected
strHold = strHold & Me.ListBox.Column(3,var)
Next

Let's say I have a list box; 4 columns. The above code grabs the value
of the 4th column (starts at the 0 element so in this case it's 3)

You should be grabbing the ID of the Question (aka SubCategory), not
category.

The QuestionID should be on the report so you can filter it with the
links you most likely have. So you could set the column for subcategory
(which is a key to the QUestions table) and set the width to 0 to hide it.

[trose178]

On Jul 18, 3:38*pm, Salad <[e-mail]:

[e-mail] wrote:

On Jul 18, 1:48 pm, Salad <[e-mail]:

>[e-mail] wrote:

>>On Jul 18, 8:56 am, [e-mail] wrote:

>>>On Jul 18, 8:48 am, [e-mail] wrote:

>>>>On Jul 17, 5:58 pm, Jamey <[e-mail]:

>>>>>On Jul 17, 4:55 pm, [e-mail] wrote:

>>>>>>Perfect that fixed my syntax error but now it is telling me the ID
>>field could be more than one things from my FROM clause. It is
>>probably because there are two different ID's Category.ID and Standard
>>Questions.ID, but only the Categories ID is actually being used inthe
>>RowSource. *Is there a way for me to specify Categories.ID in the
>>code? When I try to put it in the brackets it says invalid bracketing
>>of name. *This also may not be the correct solution it could also have
>>to do with my RowSource as well.- Hide quoted text -

>>>>>>- Show quoted text -

>>>>>[Questions By Category].[ID]

>>>>Thank you for all your help, but I am now running into an issue of
where I get a prompt asking for a parameter value for the following
fields:
Questions By Category.ID
Lookup_Category.Category
Lookup_SubCategory1.SubCategory1
Lookup_SubCategory1A.SubCategory1A

>>>>This could be because the report was setup before I started doing all
this so I am going to try and make a blank report to see if it fills
it in or not. *If not, how would I resolve this issues, I should be
able to select items in the list box and then have it filter them that
way instead of asking me for parameter values.

>>>I created a new report and it fixed this previous problem, but now
when I select items from my list box instead of giving me individual
questions, it gives me all the questions per that category ID, I want
to be able to select individual questions and have only those show up..
I probably have to edit the RowSource stuff and BoundColumn again,
going to mess around with it. *If you have any suggestions please post.

>>So I have the functionality working, but I still cant get it to
seperate by individual question instead of category. *Can anyone look
at this code and see if it can be done, it seems like it should be
able to.

>>Option Compare Database

>>Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
* *'Purpose: *Open the report filtered to the items selected inthe
list box.
* *'Author: * Allen J Browne, 2004. *http://allenbrowne.com
* *Dim varItem As Variant * * *'Selected items
* *Dim strWhere As String * * *'String to use as WhereCondition
* *Dim strDescrip As String * *'Description of WhereCondition
* *Dim lngLen As Long * * * * *'Length of string
* *Dim strDelim As String * * *'Delimiter for this field type.
* *Dim strDoc As String * * * *'Name of report to open.

>* *'strDelim = """" * * * * * *'Delimiter appropriate to field type.
See note 1.
* *strDoc = "Questions by Category"

>* *'Loop through the ItemsSelected in the list box.
* *With Me.lstCategory
* * * *For Each varItem In .ItemsSelected
* * * * * *If Not IsNull(varItem) Then
* * * * * * * *'Build up the filter from the bound column (hidden).
* * * * * * * *strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
* * * * * * * *'Build up the description from the text in the visible
column. See note 2.
* * * * * * * *strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "
* * * * * *End If
* * * *Next
* *End With

>* *'Remove trailing comma. Add field name, IN operator, and brackets.
* *lngLen = Len(strWhere) - 1
* *If lngLen 0 Then
* * * *Debug.Print "strWhere " & strWhere
* * * *Debug.Print "lngLen " & lngLen
* * * *Debug.Print "[ID] IN (" & Left$(strWhere, lngLen) & ")"
* * * *strWhere = "[Questions By Category].[ID] IN (" & Left$
(strWhere, lngLen) & ")"
* * * *lngLen = Len(strDescrip) - 2
* * * *If lngLen 0 Then
* * * * * *strDescrip = "Categories: " & Left$(strDescrip, lngLen)
* * * *End If
* *End If

>* *'Report will not filter if open, so close it. For Access 97, see
note 3.
* *If CurrentProject.AllReports(strDoc).IsLoaded Then
* * * *DoCmd.Close acReport, strDoc
* *End If

>* *'Omit the last argument for Access 2000 and earlier. See note 4.
* *DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

>>Exit_Handler:
* *Exit Sub

>>Err_Handler:
* *If Err.Number <2501 Then *'Ignore "Report cancelled" error..
* * * *MsgBox "Error " & Err.Number & " - " & Err.Description, ,
"cmdPreview_Click"
* *End If
* *Resume Exit_Handler

>>End Sub

>Maybe you can help us by telling us what it is you are doing. *There's
too much stuff to keep up with all of this thread; syntax errors,
listbox errors, report errors, maybe other errors.

>You have a listbox. *What are the fields you display in the listbox?
Category, question, answer? *You tag the category and you get
categories. *Sounds right. *But you have sub categories. *Are the
subcategories in the listbox as well? *Is the ID of the subcategory in
the listbox as well? *Don't you need to be selecting on the question or
subcategory id, not the category id?

>If you spend some time detailing what you have now, what you need, and
what you are doing without being too verbose you might get a solution.

Yea I know this is somewhat hard to follow sorry about that. *What I
have now is a query which includes the Category.ID, SQ(Standard
Questions).SubCategory1A, and SQ.Question.
The list box RowSource is as follows SELECT [Questions By
Category].ID, [Questions By Category].SubCategory1, [Questions By
Category].Question FROM [Questions By Category];
The subcategory ID is not in the listbox, and I yes I should be
setting it up to select the question ID very good thought. I am going
to try to mess with that now.

I have tried messing with the code so that it would select individual
questions but so far unsuccessful. *I am somewhat in the dark in terms
of information needed sometimes that is why I am vague with some of my
reponses

* * * * Dim var As Variant
* * * * Dim strHold
* * * * *For Each var In Me.ListBox.ItemsSelected
* * * * * * *strHold = strHold & *Me.ListBox.Column(3,var)
* * * * Next

Let's say I have a list box; 4 columns. *The above code grabs the value
of the 4th column (starts at the 0 element so in this case it's 3)

You should be grabbing the ID of the Question (aka SubCategory), not
category.

The QuestionID should be on the report so you can filter it with the
links you most likely have. *So you could set the column for subcategory
(which is a key to the QUestions table) and set the width to 0 to hide it..

Yea Salad you were right, I had to set it up so the question ID was in
the query. It now works as planned without the code you posted. Now I
can finally start doing the layout for this since it works. The only
thing I changed was instead of Categories.ID in the query I mad it
SQ.ID so it would do it by each individual question #. Such an easy
solution that I couldn't see because I was overwhelmed by this
application. Thanks for the help, I may post back if I run into any
troubles, but as of right now it works and should be okay. I may have
to clean it up a little bit though.

[Salad]

tr******@gmail.com wrote:

On Jul 18, 3:38 pm, Salad <[e-mail]:

>>[e-mail] wrote:

>>>On Jul 18, 1:48 pm, Salad <[e-mail]:

>>>>[e-mail] wrote:

>>>>>On Jul 18, 8:56 am, [e-mail] wrote:

>>>>>>On Jul 18, 8:48 am, [e-mail] wrote:

>>>>>>>On Jul 17, 5:58 pm, Jamey <[e-mail]:

>>>>>>>>On Jul 17, 4:55 pm, [e-mail] wrote:

>>>>>>>>>Perfect that fixed my syntax error but now it is telling me the ID
>>>>>field could be more than one things from my FROM clause. It is
>>>>>probably because there are two different ID's Category.ID and Standard
>>>>>Questions.ID, but only the Categories ID is actually being used in the
>>>>>RowSource. Is there a way for me to specify Categories.ID in the
>>>>>code? When I try to put it in the brackets it says invalid bracketing
>>>>>of name. This also may not be the correct solution it could also have
>>>>>to do with my RowSource as well.- Hide quoted text -

>>>>>>>>>- Show quoted text -

>>>>>>>>[Questions By Category].[ID]

>>>>>>>Thank you for all your help, but I am now running into an issue of
>>>where I get a prompt asking for a parameter value for the following
>>>fields:
>>>Questions By Category.ID
>>>Lookup_Category.Category
>>>Lookup_SubCategory1.SubCategory1
>>>Lookup_SubCategory1A.SubCategory1A

>>>>>>>This could be because the report was setup before I started doing all
>>>this so I am going to try and make a blank report to see if it fills
>>>it in or not. If not, how would I resolve this issues, I should be
>>>able to select items in the list box and then have it filter them that
>>>way instead of asking me for parameter values.

>>>>>>I created a new report and it fixed this previous problem, but now
>>when I select items from my list box instead of giving me individual
>>questions, it gives me all the questions per that category ID, I want
>>to be able to select individual questions and have only those show up.
>>I probably have to edit the RowSource stuff and BoundColumn again,
>>going to mess around with it. If you have any suggestions please post.

>>>>>So I have the functionality working, but I still cant get it to
>seperate by individual question instead of category. Can anyone look
>at this code and see if it can be done, it seems like it should be
>able to.

>>>>>Option Compare Database

>>>>>Private Sub cmdPreview_Click()
>On Error GoTo Err_Handler
'Purpose: Open the report filtered to the items selected in the
>list box.
'Author: Allen J Browne, 2004. http://allenbrowne.com
Dim varItem As Variant 'Selected items
Dim strWhere As String 'String to use as WhereCondition
Dim strDescrip As String 'Description of WhereCondition
Dim lngLen As Long 'Length of string
Dim strDelim As String 'Delimiter for this field type.
Dim strDoc As String 'Name of report to open.

>>>> 'strDelim = """" 'Delimiter appropriate to field type.
>See note 1.
strDoc = "Questions by Category"

>>>> 'Loop through the ItemsSelected in the list box.
With Me.lstCategory
For Each varItem In .ItemsSelected
If Not IsNull(varItem) Then
'Build up the filter from the bound column (hidden).
strWhere = strWhere & strDelim & .ItemData(varItem) &
>strDelim & ","
'Build up the description from the text in the visible
>column. See note 2.
strDescrip = strDescrip & """" & .Column(1, varItem) &
>""", "
End If
Next
End With

>>>> 'Remove trailing comma. Add field name, IN operator, and brackets.
lngLen = Len(strWhere) - 1
If lngLen 0 Then
Debug.Print "strWhere " & strWhere
Debug.Print "lngLen " & lngLen
Debug.Print "[ID] IN (" & Left$(strWhere, lngLen) & ")"
strWhere = "[Questions By Category].[ID] IN (" & Left$
>(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If

>>>> 'Report will not filter if open, so close it. For Access 97, see
>note 3.
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If

>>>> 'Omit the last argument for Access 2000 and earlier. See note 4.
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
>OpenArgs:=strDescrip

>>>>>Exit_Handler:
Exit Sub

>>>>>Err_Handler:
If Err.Number <2501 Then 'Ignore "Report cancelled" error.
MsgBox "Error " & Err.Number & " - " & Err.Description, ,
>"cmdPreview_Click"
End If
Resume Exit_Handler

>>>>>End Sub

>>>>Maybe you can help us by telling us what it is you are doing. There's
too much stuff to keep up with all of this thread; syntax errors,
listbox errors, report errors, maybe other errors.

>>>>You have a listbox. What are the fields you display in the listbox?
Category, question, answer? You tag the category and you get
categories. Sounds right. But you have sub categories. Are the
subcategories in the listbox as well? Is the ID of the subcategory in
the listbox as well? Don't you need to be selecting on the question or
subcategory id, not the category id?

>>>>If you spend some time detailing what you have now, what you need, and
what you are doing without being too verbose you might get a solution.

>>>Yea I know this is somewhat hard to follow sorry about that. What I
have now is a query which includes the Category.ID, SQ(Standard
Questions).SubCategory1A, and SQ.Question.
The list box RowSource is as follows SELECT [Questions By
Category].ID, [Questions By Category].SubCategory1, [Questions By
Category].Question FROM [Questions By Category];
The subcategory ID is not in the listbox, and I yes I should be
setting it up to select the question ID very good thought. I am going
to try to mess with that now.

>>>I have tried messing with the code so that it would select individual
questions but so far unsuccessful. I am somewhat in the dark in terms
of information needed sometimes that is why I am vague with some of my
reponses

Dim var As Variant
Dim strHold
For Each var In Me.ListBox.ItemsSelected
strHold = strHold & Me.ListBox.Column(3,var)
Next

Let's say I have a list box; 4 columns. The above code grabs the value
of the 4th column (starts at the 0 element so in this case it's 3)

You should be grabbing the ID of the Question (aka SubCategory), not
category.

The QuestionID should be on the report so you can filter it with the
links you most likely have. So you could set the column for subcategory
(which is a key to the QUestions table) and set the width to 0 to hide it.

Yea Salad you were right, I had to set it up so the question ID was in
the query. It now works as planned without the code you posted. Now I
can finally start doing the layout for this since it works. The only
thing I changed was instead of Categories.ID in the query I mad it
SQ.ID so it would do it by each individual question #. Such an easy
solution that I couldn't see because I was overwhelmed by this
application. Thanks for the help, I may post back if I run into any
troubles, but as of right now it works and should be okay. I may have
to clean it up a little bit though.

Glad you are off onto the nuts and bolts of the project. Best of luck.

My suggestion is when you get overwhelmed...step back, smell some
flowers, munch on a snack...let the mind roam...then get back to the
task on hand. Sometimes the intensity of figuring something out becomes
self defeating.

[trose178]

On Jul 18, 5:25*pm, Salad <[e-mail]:

[e-mail] wrote:

On Jul 18, 3:38 pm, Salad <[e-mail]:

>[e-mail] wrote:

>>On Jul 18, 1:48 pm, Salad <[e-mail]:

>>>[e-mail] wrote:

>>>>On Jul 18, 8:56 am, [e-mail] wrote:

>>>>>On Jul 18, 8:48 am, [e-mail] wrote:

>>>>>>On Jul 17, 5:58 pm, Jamey <[e-mail]:

>>>>>>>On Jul 17, 4:55 pm, [e-mail] wrote:

>>>>>>>>Perfect that fixed my syntax error but now it is telling me theID
>>>>field could be more than one things from my FROM clause. It is
>>>>probably because there are two differentID'sCategory.IDand Standard
>>>>Questions.ID, but only the CategoriesIDis actually being used inthe
>>>>RowSource. *Is there a way for me to specify Categories.IDin the
>>>>code? When I try to put it in the brackets it says invalid bracketing
>>>>of name. *This also may not be the correct solution it could also have
>>>>to do with my RowSource as well.- Hide quotedtext-

>>>>>>>>- Show quotedtext-

>>>>>>>[Questions By Category].[ID]

>>>>>>Thank you for all your help, but I am now running into an issue of
>>where I get a prompt asking for a parameter value for the following
>>fields:
>>Questions By Category.ID
>>Lookup_Category.Category
>>Lookup_SubCategory1.SubCategory1
>>Lookup_SubCategory1A.SubCategory1A

>>>>>>This could be because the report was setup before I started doing all
>>this so I am going to try and make a blank report to see if it fills
>>it in or not. *If not, how would I resolve this issues, I shouldbe
>>able to select items in the listboxand then have it filter them that
>>wayinsteadof asking me for parameter values.

>>>>>I created a new report and it fixed this previous problem, but now
>when I select items from my listboxinsteadof giving me individual
>questions, it gives me all the questions per that categoryID, I want
>to be able to select individual questions and have only those show up.
>I probably have to edit the RowSource stuff and BoundColumn again,
>going to mess around with it. *If you have any suggestions pleasepost.

>>>>So I have the functionality working, but I still cant get it to
seperate by individual questioninsteadof category. *Can anyone look
at this code and see if it can be done, it seems like it should be
able to.

>>>>Option Compare Database

>>>>Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
* 'Purpose: *Open the report filtered to the items selected in the
listbox.
* 'Author: * Allen J Browne, 2004. *http://allenbrowne.com
* Dim varItem As Variant * * *'Selected items
* Dim strWhere As String * * *'String to use as WhereCondition
* Dim strDescrip As String * *'Description of WhereCondition
* Dim lngLen As Long * * * * *'Length of string
* Dim strDelim As String * * *'Delimiter for this field type.
* Dim strDoc As String * * * *'Name of report to open.

>>>* 'strDelim = """" * * * * * *'Delimiter appropriate to field type.
See note 1.
* strDoc = "Questions by Category"

>>>* 'Loop through the ItemsSelected in the listbox.
* With Me.lstCategory
* * * For Each varItem In .ItemsSelected
* * * * * If Not IsNull(varItem) Then
* * * * * * * 'Build up the filter from the bound column (hidden).
* * * * * * * strWhere = strWhere & strDelim & .ItemData(varItem) &
strDelim & ","
* * * * * * * 'Build up the description from thetextin the visible
column. See note 2.
* * * * * * * strDescrip = strDescrip & """" & .Column(1, varItem) &
""", "
* * * * * End If
* * * Next
* End With

>>>* 'Remove trailing comma. Add field name, IN operator, and brackets.
* lngLen = Len(strWhere) - 1
* If lngLen 0 Then
* * * Debug.Print "strWhere " & strWhere
* * * Debug.Print "lngLen " & lngLen
* * * Debug.Print "[ID] IN (" & Left$(strWhere, lngLen) & ")"
* * * strWhere = "[Questions By Category].[ID] IN (" & Left$
(strWhere, lngLen) & ")"
* * * lngLen = Len(strDescrip) - 2
* * * If lngLen 0 Then
* * * * * strDescrip = "Categories: " & Left$(strDescrip, lngLen)
* * * End If
* End If

>>>* 'Report will not filter if open, so close it. For Access 97, see
note 3.
* If CurrentProject.AllReports(strDoc).IsLoaded Then
* * * DoCmd.Close acReport, strDoc
* End If

>>>* 'Omit the last argument for Access 2000 and earlier. See note 4..
* DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
OpenArgs:=strDescrip

>>>>Exit_Handler:
* Exit Sub

>>>>Err_Handler:
* If Err.Number <2501 Then *'Ignore "Report cancelled" error.
* * * MsgBox "Error " & Err.Number & " - " & Err.Description,,
"cmdPreview_Click"
* End If
* Resume Exit_Handler

>>>>End Sub

>>>Maybe you can help us by telling us what it is you are doing. *There's
too much stuff to keep up with all of this thread; syntax errors,
listbox errors, report errors, maybe other errors.

>>>You have a listbox. *What are the fields you display in the listbox?
Category, question, answer? *You tag the category and you get
categories. *Sounds right. *But you have sub categories. *Are the
subcategories in the listbox as well? *Is theIDof the subcategory in
the listbox as well? *Don't you need to be selecting on the question or
subcategoryid, not the categoryid?

>>>If you spend some time detailing what you have now, what you need, and
what you are doing without being too verbose you might get a solution..

>>Yea I know this is somewhat hard to follow sorry about that. *What I
have now is a query which includes the Category.ID, SQ(Standard
Questions).SubCategory1A, and SQ.Question.
The listboxRowSource is as follows SELECT [Questions By
Category].ID, [Questions By Category].SubCategory1, [Questions By
Category].Question FROM [Questions By Category];
The subcategoryIDis not in the listbox, and I yes I should be
setting it up to select the questionIDvery good thought. I am going
to try to mess with that now.

>>I have tried messing with the code so that it would select individual
questions but so far unsuccessful. *I am somewhat in the dark in terms
of information needed sometimes that is why I am vague with some of my
reponses

* * * *Dim var As Variant
* * * *Dim strHold
* * * * For Each var In Me.ListBox.ItemsSelected
* * * * * * strHold = strHold & *Me.ListBox.Column(3,var)
* * * *Next

>Let's say I have a listbox; 4 columns. *The above code grabs the value
of the 4th column (starts at the 0 element so in this case it's 3)

>You should be grabbing theIDof the Question (aka SubCategory), not
category.

>The QuestionID should be on the report so you can filter it with the
links you most likely have. *So you could set the column for subcategory
(which is a key to the QUestions table) and set the width to 0 to hide it.

Yea Salad you were right, I had to set it up so the questionIDwas in
the query. It now works as planned without the code you posted. *Now I
can finally start doing the layout for this since it works. *The only
thing I changed wasinsteadof Categories.IDin the query I mad it
SQ.IDso it would do it by each individual question #. *Such an easy
solution that I couldn't see because I was overwhelmed by this
application. *Thanks for the help, I may post back if I run into any
troubles, but as of right now it works and should be okay. *I may have
to clean it up a little bit though.

Glad you are off onto the nuts and bolts of the project. *Best of luck.

My suggestion is when you get overwhelmed...step back, smell some
flowers, munch on a snack...let the mind roam...then get back to the
task on hand. *Sometimes the intensity of figuring something out becomes
self defeating.

Yea it really is, I was making silly mistakes because I was
overwhelmed and stressing. But I also have another question, this one
is probably much simpler but I can't seem to shake it I have checked
all the properties of a text box, but it may be related to the table.

I am now setting up report and form layouts for this checklist, but
when I send my selected questions from the form to the report instead
of showing me the text fields in my subcategories, it is showing their
individual autonumbered IDs. I am not sure how to resolve this
solution. Any thoughts?

[Salad]

tr******@gmail.com wrote:

On Jul 18, 5:25 pm, Salad <[e-mail]:

>>[e-mail] wrote:

>>>On Jul 18, 3:38 pm, Salad <[e-mail]:

>>>>[e-mail] wrote:

>>>>>On Jul 18, 1:48 pm, Salad <[e-mail]:

>>>>>>[e-mail] wrote:

>>>>>>>On Jul 18, 8:56 am, [e-mail] wrote:

>>>>>>>>On Jul 18, 8:48 am, [e-mail] wrote:

>>>>>>>>>On Jul 17, 5:58 pm, Jamey <[e-mail]:

>>>>>>>>>>On Jul 17, 4:55 pm, [e-mail] wrote:

>>>>>>>>>>>Perfect that fixed my syntax error but now it is telling me theID
>>>>>>>field could be more than one things from my FROM clause. It is
>>>>>>>probably because there are two differentID'sCategory.IDand Standard
>>>>>>>Questions.ID, but only the CategoriesIDis actually being used in the
>>>>>>>RowSource. Is there a way for me to specify Categories.IDin the
>>>>>>>code? When I try to put it in the brackets it says invalid bracketing
>>>>>>>of name. This also may not be the correct solution it could also have
>>>>>>>to do with my RowSource as well.- Hide quotedtext-

>>>>>>>>>>>- Show quotedtext-

>>>>>>>>>>[Questions By Category].[ID]

>>>>>>>>>Thank you for all your help, but I am now running into an issue of
>>>>>where I get a prompt asking for a parameter value for the following
>>>>>fields:
>>>>>Questions By Category.ID
>>>>>Lookup_Category.Category
>>>>>Lookup_SubCategory1.SubCategory1
>>>>>Lookup_SubCategory1A.SubCategory1A

>>>>>>>>>This could be because the report was setup before I started doing all
>>>>>this so I am going to try and make a blank report to see if it fills
>>>>>it in or not. If not, how would I resolve this issues, I should be
>>>>>able to select items in the listboxand then have it filter them that
>>>>>wayinsteadof asking me for parameter values.

>>>>>>>>I created a new report and it fixed this previous problem, but now
>>>>when I select items from my listboxinsteadof giving me individual
>>>>questions, it gives me all the questions per that categoryID, I want
>>>>to be able to select individual questions and have only those show up.
>>>>I probably have to edit the RowSource stuff and BoundColumn again,
>>>>going to mess around with it. If you have any suggestions please post.

>>>>>>>So I have the functionality working, but I still cant get it to
>>>seperate by individual questioninsteadof category. Can anyone look
>>>at this code and see if it can be done, it seems like it should be
>>>able to.

>>>>>>>Option Compare Database

>>>>>>>Private Sub cmdPreview_Click()
>>>On Error GoTo Err_Handler
>> 'Purpose: Open the report filtered to the items selected in the
>>>listbox.
>> 'Author: Allen J Browne, 2004. http://allenbrowne.com
>> Dim varItem As Variant 'Selected items
>> Dim strWhere As String 'String to use as WhereCondition
>> Dim strDescrip As String 'Description of WhereCondition
>> Dim lngLen As Long 'Length of string
>> Dim strDelim As String 'Delimiter for this field type.
>> Dim strDoc As String 'Name of report to open.

>>>>>> 'strDelim = """" 'Delimiter appropriate to field type.
>>>See note 1.
>> strDoc = "Questions by Category"

>>>>>> 'Loop through the ItemsSelected in the listbox.
>> With Me.lstCategory
>> For Each varItem In .ItemsSelected
>> If Not IsNull(varItem) Then
>> 'Build up the filter from the bound column (hidden).
>> strWhere = strWhere & strDelim & .ItemData(varItem) &
>>>strDelim & ","
>> 'Build up the description from thetextin the visible
>>>column. See note 2.
>> strDescrip = strDescrip & """" & .Column(1, varItem) &
>>>""", "
>> End If
>> Next
>> End With

>>>>>> 'Remove trailing comma. Add field name, IN operator, and brackets.
>> lngLen = Len(strWhere) - 1
>> If lngLen 0 Then
>> Debug.Print "strWhere " & strWhere
>> Debug.Print "lngLen " & lngLen
>> Debug.Print "[ID] IN (" & Left$(strWhere, lngLen) & ")"
>> strWhere = "[Questions By Category].[ID] IN (" & Left$
>>>(strWhere, lngLen) & ")"
>> lngLen = Len(strDescrip) - 2
>> If lngLen 0 Then
>> strDescrip = "Categories: " & Left$(strDescrip, lngLen)
>> End If
>> End If

>>>>>> 'Report will not filter if open, so close it. For Access 97, see
>>>note 3.
>> If CurrentProject.AllReports(strDoc).IsLoaded Then
>> DoCmd.Close acReport, strDoc
>> End If

>>>>>> 'Omit the last argument for Access 2000 and earlier. See note 4.
>> DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere,
>>>OpenArgs:=strDescrip

>>>>>>>Exit_Handler:
>> Exit Sub

>>>>>>>Err_Handler:
>> If Err.Number <2501 Then 'Ignore "Report cancelled" error.
>> MsgBox "Error " & Err.Number & " - " & Err.Description, ,
>>>"cmdPreview_Click"
>> End If
>> Resume Exit_Handler

>>>>>>>End Sub

>>>>>>Maybe you can help us by telling us what it is you are doing. There's
>>too much stuff to keep up with all of this thread; syntax errors,
>>listbox errors, report errors, maybe other errors.

>>>>>>You have a listbox. What are the fields you display in the listbox?
>>Category, question, answer? You tag the category and you get
>>categories. Sounds right. But you have sub categories. Are the
>>subcategories in the listbox as well? Is theIDof the subcategory in
>>the listbox as well? Don't you need to be selecting on the question or
>>subcategoryid, not the categoryid?

>>>>>>If you spend some time detailing what you have now, what you need, and
>>what you are doing without being too verbose you might get a solution.

>>>>>Yea I know this is somewhat hard to follow sorry about that. What I
>have now is a query which includes the Category.ID, SQ(Standard
>Questions).SubCategory1A, and SQ.Question.
>The listboxRowSource is as follows SELECT [Questions By
>Category].ID, [Questions By Category].SubCategory1, [Questions By
>Category].Question FROM [Questions By Category];
>The subcategoryIDis not in the listbox, and I yes I should be
>setting it up to select the questionIDvery good thought. I am going
>to try to mess with that now.

>>>>>I have tried messing with the code so that it would select individual
>questions but so far unsuccessful. I am somewhat in the dark in terms
>of information needed sometimes that is why I am vague with some of my
>reponses

>>> Dim var As Variant
Dim strHold
For Each var In Me.ListBox.ItemsSelected
strHold = strHold & Me.ListBox.Column(3,var)
Next

>>>>Let's say I have a listbox; 4 columns. The above code grabs the value
of the 4th column (starts at the 0 element so in this case it's 3)

>>>>You should be grabbing theIDof the Question (aka SubCategory), not
category.

>>>>The QuestionID should be on the report so you can filter it with the
links you most likely have. So you could set the column for subcategory
(which is a key to the QUestions table) and set the width to 0 to hide it.

>>>Yea Salad you were right, I had to set it up so the questionIDwas in
the query. It now works as planned without the code you posted. Now I
can finally start doing the layout for this since it works. The only
thing I changed wasinsteadof Categories.IDin the query I mad it
SQ.IDso it would do it by each individual question #. Such an easy
solution that I couldn't see because I was overwhelmed by this
application. Thanks for the help, I may post back if I run into any
troubles, but as of right now it works and should be okay. I may have
to clean it up a little bit though.

Glad you are off onto the nuts and bolts of the project. Best of luck.

My suggestion is when you get overwhelmed...step back, smell some
flowers, munch on a snack...let the mind roam...then get back to the
task on hand. Sometimes the intensity of figuring something out becomes
self defeating.

Yea it really is, I was making silly mistakes because I was
overwhelmed and stressing. But I also have another question, this one
is probably much simpler but I can't seem to shake it I have checked
all the properties of a text box, but it may be related to the table.

I am now setting up report and form layouts for this checklist, but
when I send my selected questions from the form to the report instead
of showing me the text fields in my subcategories, it is showing their
individual autonumbered IDs. I am not sure how to resolve this
solution. Any thoughts?

You need to check the recordsource for your report. Does it contain the
category, subcat/question? You can hide your autonumbers by making the
visible property false (under format tab). You can pass to the report
the filter string (in the form calling the report you can create a text
field, ex. FilterStr to store that string) and then enter
Docmd.OpenReport "RptName",,,Me.FilterStr
and this should open your report and only display the records you
selected. FilterStr would contain the "SubcatID In (...)"

If you need to group, sort the questions do it from the report...select
View/Sorting&Grouping from the menu.