ok i have a list box with January through to december 2005
i have linked this to my issue date which is entered (long date)
the command button is trying to print a report of just the records that i issued in acertain month, but it comes up with a script error (see script below)
i think the list box info is not matching my issued date hence the error, but im unsure how to correct this. any ideas
Private Sub Monthly_MD_Report_Click()
'Run a report displaying only the records chosen
'by the user in the form's listbox.
Dim v As Variant
Dim Frm As Form
Dim ctl As Control
Dim theId As Long
Dim WhereCrit As String
'If nothing is selected, notify user...
If Me.List94.ItemsSelected.Count = 0 Then
MsgBox "Please select a month.", vbExclamation, "No Month Selected"
'and then scram.
Exit Sub
End If
'Assign form and control to object variables.
Set Frm = Forms!FrmForm
Set ctl = Frm!List94
'Begin building Where string.
WhereCrit = "txtIssuedate = "
'Add each selected item to the WHERE string.
For Each v In ctl.ItemsSelected
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'The first column in the list, holding SupplierID, is hidden.
'See the list's "Column Widths" and "Column Count" properties in
'its property dialog and look up the terms in Help for more info.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'Coordinates: 1st column (0); row v
'where v changes for each round of the loop.
theId = ctl.Column(0, v)
'Tag on to string.
WhereCrit = WhereCrit & theId & " OR txtIssuedate = "
Next v
'Loop ends; selected items are now accounted for...
'NOTE: To better understand what the code is doing, uncomment the
'next line to print the Where string to the Immediate window.
'Debug.Print WhereCrit
'Clean-up the Where string by removing the trailing text.
WhereCrit = Left(WhereCrit, Len(WhereCrit) - 17)
'Test (see note a few lines above).
'Debug.Print WhereCrit
'By default, the Suppliers Report returns all records from the Suppliers Table.
'Here the Suppliers Report is opened using the 'Where clause' to filter it down
'to only the items selected in the listbox.
DoCmd.OpenReport "MD Form", acViewPreview, , WhereCrit
End Sub
i have linked this to my issue date which is entered (long date)
the command button is trying to print a report of just the records that i issued in acertain month, but it comes up with a script error (see script below)
i think the list box info is not matching my issued date hence the error, but im unsure how to correct this. any ideas
Private Sub Monthly_MD_Report_Click()
'Run a report displaying only the records chosen
'by the user in the form's listbox.
Dim v As Variant
Dim Frm As Form
Dim ctl As Control
Dim theId As Long
Dim WhereCrit As String
'If nothing is selected, notify user...
If Me.List94.ItemsSelected.Count = 0 Then
MsgBox "Please select a month.", vbExclamation, "No Month Selected"
'and then scram.
Exit Sub
End If
'Assign form and control to object variables.
Set Frm = Forms!FrmForm
Set ctl = Frm!List94
'Begin building Where string.
WhereCrit = "txtIssuedate = "
'Add each selected item to the WHERE string.
For Each v In ctl.ItemsSelected
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'The first column in the list, holding SupplierID, is hidden.
'See the list's "Column Widths" and "Column Count" properties in
'its property dialog and look up the terms in Help for more info.
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'Coordinates: 1st column (0); row v
'where v changes for each round of the loop.
theId = ctl.Column(0, v)
'Tag on to string.
WhereCrit = WhereCrit & theId & " OR txtIssuedate = "
Next v
'Loop ends; selected items are now accounted for...
'NOTE: To better understand what the code is doing, uncomment the
'next line to print the Where string to the Immediate window.
'Debug.Print WhereCrit
'Clean-up the Where string by removing the trailing text.
WhereCrit = Left(WhereCrit, Len(WhereCrit) - 17)
'Test (see note a few lines above).
'Debug.Print WhereCrit
'By default, the Suppliers Report returns all records from the Suppliers Table.
'Here the Suppliers Report is opened using the 'Where clause' to filter it down
'to only the items selected in the listbox.
DoCmd.OpenReport "MD Form", acViewPreview, , WhereCrit
End Sub
