Multi Select

[Thompyt]

I have this code: Gleaned earlier from here by June7 assistance for an earlier request. I adapted it for another form/output and it will not select.

Private Sub Select_Changes_Click()
    Dim strWhere As String
    Dim ctl As Control
    Dim varItem As Variant
    Set ctl = Me.My_CR_Number
If Me.My_CR_Number.ItemsSelected.Count = 0 Then
     MsgBox "Nothing was selected"
        Exit Sub
    End If
For Each varItem In ctl.ItemsSelected
   strWhere = strWhere & "'" & ctl.ItemData(varItem) & "',"
Next varItem
   strWhere = Left(strWhere, Len(strWhere) - 1)
   DoCmd.OpenReport "Select Changes", acViewReport, , "CR_Numbers IN(" & strWhere & ")"
   DoCmd.Close acForm, "Select Changes"
End Sub

From [Select Changes] Query

SELECT Chng_ReqQry.CR_Numbers
FROM Chng_ReqQry
GROUP BY Chng_ReqQry.CR_Numbers, Chng_ReqQry.CR_ID, Chng_ReqQry.CR_No
HAVING (((Chng_ReqQry.CR_No)<>0))
ORDER BY Chng_ReqQry.CR_ID;

Into a report [Select Changes] report.

Gathering information from:
CR_Numbers: Format(([Change Request].[CR_No]+([Sub_No]*0.01)),"Fixed")




Where am I going wrong?

[ssanfu]

The code for Sub Select_Changes_Click() works, as far as I can tell.

Where am I going wrong?

Hard to tell. You didn't state the problem.
What is the error?
What is "wrong"?

[Thompyt]

The code for Sub Select_Changes_Click() works, as far as I can tell.


Hard to tell. You didn't state the problem.
What is the error?
What is "wrong"?

When the [Select Changes] Form opens, I cannot select anything. If I click on the select button, I get the desired POPup saying nothing was selected. I was thinking it may be from the Dim strWhere As String is using what was originally a number. But CR_Numbers formatting changes it to a text, and I tried it with a text field also by changing the
DoCmd.OpenReport "Select Changes", acViewReport, , "CR_Numbers IN(" & strWhere & ")" CR_Numbers into [Levels] A text based field

[ssanfu]

So the query returns all records and the code creates a filter string to use when opening the report.

If you open just the query "Select Changes", are any records returned?
(shouldn't use spaces in object names, BTW)

[Thompyt]

I know I shouldn't use spaces. Bad habit. I get all the records returned in the Select Changes Query, If I run the Select Changes Report It will report all the records.

[ssanfu]

OK, the query is returning records and the report displays them.

Now to determine if the criteria should be a string or a number.

In design view of the query, in the criteria row, enter a "CR_Numbers" in quotes and run the query. Is just that "CR_Numbers" returned?
If no records are returned, try the criteria without quotes.

[Thompyt]

ssanfu,
I fiqured out the issue. I had it in a type of loop where it was getting the CR_Numbers from the same Qry as I was intending to base the report on. Once I changed it to another query for the CR_Numbers it worked.

Thanks