OpenReport

[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

 

[pbaldy]

First, check out OpenReport in VBA help. I think you have commas and arguments out of place. Second, see here for the syntax required if Name is text:

http://www.baldyweb.com/wherecondition.htm

 

[stallams]

Hi!

Can I send a complete select to openreport

DoCmd.OpenReport , "RepSubject", acPreview,, Select * from rstTempMem

Thanks,

Esti

 

[pbaldy]

Not using that method, no.

 

[stallams]

Hi!

So how can I send the selected information to the report.

Thanks,

Esti

 

[HiTechCoach]

Hi!

So how can I send the selected information to the report.

Thanks,

Esti

The simple way is to write the data to a table and set the report's record source to a query using the table.

AFAIK, you can not pass a select statement to a report.

You can use the report's on open even to set the report's record source to a ADO recordset. I think you must use acess 2002 or later for this to work.

I would suggest putting your code inside the report's On Open event,

See:
How to bind Microsoft Access forms to ADO recordsets

 

[stallams]

Hi!

This is my code and I am trying to write a report from the temporary file 'rsTempMem' I created.
Please help me with the code.

Option Compare Database
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
' open
' DoCmd.OpenReport "RptSubject", acPreview


Thanks,

Esti