Printing a Report from a loop in VB

[stallams]

Hi!

I am trying thru a while loop in a vb program behind the access report. I have a form where I enter txtSubject.
I want to search for all the subjects in the database that have in them the txtSubject and then print them in a report.

I am having a problem sending the string to the open report here is my code:

Dim dbs as Database
Dim rsSelect as Recordset
Dim Mypos as Interger
Dim Mypos1 as Interger
Dim nm as Variant
Dim I as Interger
Dim sqlstring(2000) as String

set dbs=CurrentDb
("Set rsSelect = dbs.OpenRecordset("Tbl_Bakashot")

With rsSelect
(nm = Array(2000))

Do while Not.Eof

(For I = LBound(nm to Ubound(nm))

If not IsNUll (!SubjectH) then
(MyPos = Instr(1,!SubjectH,Me.txtSubject)
If Mypos<>0 then
sqlstring(nm(I)) = !CodeBakasha

Endif
Endif

If not IsNUll (!SubjectH) then
(MyPos1 = Instr(1,!SubjectE,Me.txtSubject)
If Mypos1<>0 then
sqlstring(nm(I)) = !CodeBakasha

Endif
Endif

MoveNext

Next I

Loop

EndWith

Docmd.OpenReport "Rep_SubjectFromBakashot",acPreview,"CodeBakasha= " & (sqlstring(nm(I))

EndSub


Please let me know what I am doing wrong and maybe there is a better way to do it.

Help please.

Thanks,

ST

 

[geoB]

It would be good to know exactly what the problem is.

Aside from the unmatched parentheses, it looks like you're trying to set a filter where CodeBakasha is equal to a value in an array. I'm guessing you will get a subscript out of range error at

Docmd.OpenReport "Rep_SubjectFromBakashot",acPreview,"CodeBakas  ha= "  & (sqlstring(nm(I))

because I has been incremented beyond the upper bound of the nm array.

George

 

[stallams]

HI!

I am trying to set an array with certain database records and then print a report of those records.

Do you know a different way I could do it.


Thanks,

ST

 

[geoB]

I am trying to set an array with certain database records and then print a report of those records.

Do you mean one report for each value of the array? Or one report showing each value?

G

 

[stallams]

No I mean one report with all the records of the array

Thanks,

ST

 

[geoB]

Your best bet will be to load the array into a (temporary) table and reference that table in the report. For ideas on how to do this, search for related terms such as "access vba report array". Any code you find that way will be better than anything I could come up with.

George

 

[stallams]

Thanks, I try

 

[stallams]

Hi!

I think I got the VB code correct by building a temporary file, but how do I send this temporary file to my report. (I bolded out where my problem is)

Here's the code:
Private Sub Display_Click()
On Error GoTo Err_Display_Click
Dim rst1 As ADODB.Recordset
Dim rstTempMem As ADODB.Recordset
Dim cn1 As ADODB.Connection
Dim EachWord1() As String
Dim lenWord1 As Integer
Dim Mypos As Integer
Dim strSQL As String
'set connection
Set cn1 = CurrentProject.Connection
Set rst1 = New ADODB.Recordset
' Build a temporary recordset in memory. you need this to "put" the word you find.
Set rstTempMem = New ADODB.Recordset
With rstTempMem
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.Fields.Append "No", adVarBinary, 2000
.Fields.Append "Name", adVarChar, 255
.Fields.Append "Subjecth", adVarChar, 255
.Fields.Append "Subjecte", adVarChar, 255
.Fields.Refresh
.Open
End With

strSQL = "Select Name, Subjecth, Subjecte from tblName;"
'marry the above selection to the recordset opened
rst1.Open strSQL, cn1, openstatic, adLockOptimistic
'Go to the beginning of the selected recordset
If Not rst1.BOF And Not rst1.EOF Then
rst1.MoveFirst
' Cycle through the titles using the Split function
Do Until rst1.EOF
If Not IsNull(rst1!Subjecth) Then
Mypos = InStr(1, rst1!Subjecth, Me.txtSubtect)
End If
If Mypos <> 0 Then
EachWord1 = Split(rst1!Name, " ")
For intLoop = 0 To UBound(EachWord1)
' Add each keyword to the temporary recordset
rstTempMem.AddNew
rstTempMem!Name = EachWord1(intLoop)
rstTempMem!Subjecth = rst1!Subjecth
rstTempMem!Subjecte = rst1!Subjecte
'get length of each word
lenWord1 = Len(rstTempMem!Name)
' create message box to make sure looping is ok
MsgBox "The word is " & rstTempMem!Name & " " & rstTempMem!Subjecth & " " & rstTempMem!Subjecte & " the length is " & lenWord1
rstTempMem.Update
'clean up memory
EachWord1(intLoop) = ""
Next intLoop
End If
rst1.MoveNext
Loop
End If


This is my problem I want to send the records from the tempmem file to my report.
DoCmd.OpenReport , "RepSubject", acPreview, "Name=" & rstTempMem!Name

Exit_Display_Click:
Exit Sub
Err_Display_Click:
MsgBox Err.Description
Resume Exit_Display_Click

End Sub


thanks,
ST