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:
Code:
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:
Code:
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.