Hello,
Thank you so much for helping me.
My lstSelected list box in my form is Row Source Type = "Value List",
Row
Source = nothing and Control Source ="nothing", only one column in the
list
box. My first step is move from lstAvailable list box to lstSelected
then
if
for example i have 5 names in a 'lstSelected' lst box then i click on
button
"Preview Report", i should have all info for 5 users. I am using your
code
and i did exactly what you said in your notes (1-4).For Record source
in a
report shoul be just table name "tblNames" or "Select * from tblNames"
??? I
did add text box to the report and entered for control source:
=[Report].[OpenArgs]. Also i did compile system, it's clean now.
But still my preview button it's not working. May be I changed the code
incorrectly. here is the code:
Private Sub cmdPreview_Click()
On Error GoTo Err_Handler
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 = "rptNames"
'Loop through the ItemsSelected in the list box.
With Me.lstSelected
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
strWhere = "[Names] IN (" & Left$(strWhere, lngLen) & ")"
lngLen = Len(strDescrip) - 2
If lngLen > 0 Then
strDescrip = "Categories: " & Left$(strDescrip, lngLen)
End If
End If
If CurrentProject.AllReports(strDoc).IsLoaded Then
DoCmd.Close acReport, strDoc
End If
DoCmd.OpenReport strDoc, acViewPreview, WhereCondition:=strWhere
' I did drop the 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
====
Please let me know what i am doing wrong.
Thank you.
:
The line starting with Const is the delaration: you do not need to Dim
it
as
well.
After entering the code, choose Compile on the Debug window. Fix any
errors,
and repeat until it compiles okay.
I assume that you have a form named frmNew, the form is open, and it
has
a
listbox named lstSelected. If this is a multi-select listbox, you need
different code to loop through its ItemsSelected collection like this:
http://allenbrowne.com/ser-50.html
If it is not a multi-select list box, and you select one record, only
the
matching record will be included. If you select no record, the list
box
is
null, and you need to NOT include this in your WHERE clause. Test
using
IsNull().
Error 2757 is quite a generic one. You will need to note which line
generates that error.
When i start running your code i've got error msg:
"Run-time error '2757': There was a problem accessing a property or
method
of the OLE object."
When i removed date parameter i only able to view data for one user
but
in
lstSelected list box i have 4 names, it should pull data for 4
users.
I am not sure what i need to do...
Do i need declare 'strcJetDate ', if yes then is 'Dim strcJetDate
as
???'
Please help with this code.
Thank you.
:
The literal date value in the SQL string needs to be formatted the
way
JET
expects, and delimited with #.
Const strcJetDate = "\#mm/dd/yyyy\#"
With Forms!frmNew
If IsNull(!lstSelected) Or IsNull(!txtFrom) Or IsNull(!txtTo)
Then
MsgBox "Somehing's missing."
Else
strRecordSource = "Select tblUserInfo.* from tblUserInfo "
& _
"where (PCreatedBy = """ & !lstSelected & _
""") And (PCreatedDate between " & _
Format(!txtFrom, strcJetDate) & " and " & _
Format(!txtTo, strcJetDate) & ");"
Debug.Print strRecordSource
Me.RecordSource = strRecordSource
End If
End With
From form frmUsers i have more then one selected names in
lstSelected
list
box and date from 'txtFrom', date to 'txtTo'. When i click on
button
it
will
open frmNew form, and this part in my code 'PCreatedDate =
between
"Forms!frmNew.txtFrom" and Forms!frmNew.txtTo", i am not sure how
use
parameter in my code for date.
Please help me.
Thank you
==============
Private Sub Form_Open(Cancel As Integer)
Dim PCreatedBy As Integer
Dim strRecordSource As String
Dim PCreatedDate As Integer
If CurrentProject.AllForms("frmNew").IsLoaded Then
strRecordSource = "Select * from tblUserInfo where PCreatedBy
=
'" &
Forms!frmNew.lstSelected & "'"
And PCreatedDate = between "Forms!frmNew.txtFrom" and
Forms!frmNew.txtTo"
Me.Form.RecordSource = strRecordSource
End If
End Sub
