Output of module/procedure in report

[anil]

i write the module function to get the output which
is as :
Sub MySecondConnection()
Dim con1 As ADODB.Connection
Dim recset1 As ADODB.Recordset
Dim recset2 As ADODB.Recordset
Dim recset3 As ADODB.Recordset
Dim strSQL As String
Dim strSQLforSampleSite As String
Dim strTownCode As String
Dim strsearch As String
Dim noOfSampleSites As Integer

Set con1 = CurrentProject.Connection


'strsearch = InputBox("Enter the Town to find", "Search Creteria")
strTownCode = "SELECT tblTown.TownCode FROM tblTown;"
Set recset3 = New ADODB.Recordset
recset3.Open strTownCode, con1


If recset3.EOF Then
MsgBox "No Sites"
Exit Sub
Else
Do Until recset3.EOF
strSQLforSampleSite = "SELECT tblTown.SampleSites FROM
tblTown where tblTown.towncode = '" & recset3(0) & "'"
Set recset2 = New ADODB.Recordset
recset2.Open strSQLforSampleSite, con1
If recset2.EOF = False Then
noOfSampleSites = recset2(0)
Else
MsgBox "Sorry !! The Sample sites are not specified"
Exit Sub
End If


strSQL = " SELECT TOP " & noOfSampleSites & "
tblSite.SiteCode, tblSite.SiteAddress, tblTown.Town,
tblTown.SampleSites " & _
" FROM tblTown INNER JOIN tblSite ON
tblTown.TownCode = tblSite.TownCode " & _
" WHERE ((Randomizer() = 0) And (tblTown.Towncode
= '" & recset3(0) & "')) " & _
" ORDER BY Rnd(IsNull(tblTown.Towncode)*0+1);"


Set recset1 = New ADODB.Recordset
recset1.Open strSQL, con1
Do Until recset1.EOF
Debug.Print recset1.Fields("SiteCode") & " - " &
recset1.Fields("SiteAddress")
'MsgBox ("SiteCode") & " - " &
recset1.Fields("SiteAddress")
recset1.MoveNext
Loop
Debug.Print "---------------------------------------------"

recset1.Close


recset3.MoveNext
Loop
End If
con1.Close
Set con1 = Nothing
Set recset1 = Nothing
End Sub
Now i get the result in immediate window.i need to get result in report

or form.can u please help to change the code to get the output in
report.if possible ,can i get output in excel or word.
thanks
anil

 

[[MVP] S.Clark]

Create a report with the desired layout, based on the fields from the
output. Base the report on the standard query.

Create a form that allows users to enter the desired criteria.

The trick to tie together the criteria from the form, to the output of the
report is the Where parameter of the DoCmd.OpenReport method.

You can create the criteria in a string in VBA, then pass it in this
parameter.

 

[anil]

thanks for ur help. can u help in bit more detail.
as there are 3 queries used in procedure where result of one query is
condition for other.Can i use them as simple queries and how?
anil

 

[[MVP] S.Clark]

Whichever is the final query used for the report, is the one that the
criteria will be applied.

So, if q1 is used by q2, and q2 by q3 and q3 is the one the report is based
upon, then passing a WHERE parameter in the Docmd.openreport is just like
doing the following:

Select * from q3 where [your passed criteria string here]

 

[anil]

thanks for ur help
anil