Can't select

[Thompyt]

I have a form referencing a query to make a selection that activates VBA code to print out a report of the selection. I can't figure out why I can't select values for the unbound list.



Query:

SELECT DISTINCT Format(([CRNo]+([SubNo]*0.01)),"Fixed") AS CRNumber, tblChangeRequest.CRID, qrySwitching.Units, qrySwitching.HBVers, qrySwitching.MTOEParas, qrySwitching.People, qrySwitching.DaysOpen, qrySwitching.DateIDs, tblChangeRequest.ChangeRequested, tblChangeRequest.Rationale, tblChangeRequest.NIE, tblChangeRequest.ChangeType, qrySwitching.Levelz, qrySwitching.Status, tblChangeRequest.NOTES, tblChangeRequest.ActionItems
FROM tblChangeRequest INNER JOIN qrySwitching ON tblChangeRequest.CRID = qrySwitching.CRID
WHERE (((tblChangeRequest.CRNo)<>0))
GROUP BY Format(([CRNo]+([SubNo]*0.01)),"Fixed"), tblChangeRequest.CRID, qrySwitching.Units, qrySwitching.HBVers, qrySwitching.MTOEParas, qrySwitching.People, qrySwitching.DaysOpen, qrySwitching.DateIDs, tblChangeRequest.ChangeRequested, tblChangeRequest.Rationale, tblChangeRequest.NIE, tblChangeRequest.ChangeType, qrySwitching.Levelz, qrySwitching.Status, tblChangeRequest.NOTES, tblChangeRequest.ActionItems
ORDER BY tblChangeRequest.CRID;

VBA:

Option Explicit
Private Sub Cancel_Click()
    DoCmd.Close acForm, "frmSelectChanges"
    DoCmd.OpenForm "frmStart"
End Sub
Private Sub SelectChanges_Click()
    Dim strWhere As String
    Dim ctl As Control
    Dim varItem As Variant
    Set ctl = Me.MyChange
If Me.MyChange.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 "rptSelectChanges", acViewReport, , "CRNumber IN(" & strWhere & ")"
   DoCmd.Close acForm, "frmSelectChanges"
End Sub

Form: frmSelectChanges
Record Source - qrySelectChanges
Recordset type: Dynaset


Unbound = MyChange
MyChange Row Source = SELECT DISTINCT qrySelectChanges.CRNumber, qrySelectChanges.CRID FROM qrySelectChanges GROUP BY qrySelectChanges.CRNumber, qrySelectChanges.CRID ORDER BY qrySelectChanges.CRID;
MyChange Row Source Type: Table/Query
Bound Column: 1


Report: rptSelctChanges -
Record Source - qrySelectChanges

I can run the report separately and it will display all the records.

I have learned not to change naming conventions after the fact.

[Thompyt]

Found the issue.

Set the form record source to tblChnageRequest. Now it works fine.