Help, I am stuck trying to modify vba

[ademitutu]

I have two different types of reports to print, Narratives (name format eg: rptabcdefgh) and Summary (name format eg: sumijklmnop). Right now, all the reports show up in lstNarrativeList.RowSource. My intension is to separate the reports into two groups – Narratives and Summaries. I am hoping to populate lstNarrativeList.RowSource with Narative Reports only and lstSummaryList.RowSource with Summary Reports only.

Below is a modified code (thanks to Inside Microsoft Access) that I have been using and my form name is frmPrint Options.


Private Sub Form_Open (Cancel As Integer)


Dim db As Database, rpt As Document, cnt As Container
Dim strRowSrc As String, RepName As String

Set db = CurrentDb
Set cnt = db.Containers!Reports

For Each rpt In cnt.Documents

If Left(rpt.Name, 3) = "rpt" Then
RepName = Right(rpt.Name, Len(rpt.Name) - 3)
Else
End If

strRowSrc = strRowSrc & RepName & ";"

Next rpt

lstNarrativeList.RowSource = strRowSrc

Set cnt = Nothing
Set db = Nothing

End Sub

I have tried everything I can think of with no success. Can anyone out there help this inexperienced access user?
Thanks in advance.

 

[simongallop]

You need the code to say If rpt then place it in lstNarrative otherwise place it in lstSummary. To do this you need to declare 2 string row source variables and add the report name to the relevant one. So within the For Next loop you want:


RepName = Right(rpt.Name, Len(rpt.Name) - 3)

If Left(rpt.Name, 3) = "rpt" Then
strRowSrcNar = strRowSrcNar & RepName & ";"
Else
strRowSrcSum = strRowSrcSum & RepName & ";"
End If


Then after the next you want:

lstNarrativeList.RowSource = strRowSrcNar
lstSummaryList.RowSource = strRowSrcSum

HTH