get output of immediate window in report

[anil]

i write the module function to get the output of random numbers 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 bit in details as i am new to programming in vba .
if possible ,can i get output in excel or word.
thanks
anil

 

[Alex Dybenko]

Hi,
you can try to build a query and then build a report based on it, this is
natural why to do so in Access

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Baz]

i write the module function to get the output of random numbers 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 bit in details as i am new to programming in vba .
if possible ,can i get output in excel or word.
thanks
anil

It might be easier to help if you explained in functional terms what you are
trying to do.

 

[anil]

Hi
There are 3 queries and i have to put loop to go through whole
table/Fields, i do not know how to put loop.Second thing result of
Query 1 is condition of query2.ALso query1 and query2 are the
conditions in query 3.I havetried to work them as queries but not
successful.please help me if possible it would be nice without vba
code.
tahnks
anil

 

[anil]

Hi
there are 2 tables tblTown and tblSite.Each town has fixed no of sites
say t1 has 20 ,t2 has 13 and so on.problem is to generate no of sites
with respect to each town- e.g t1 needs 3 sites out of 20,t2 needs 4
out of 13 and so on every week.In first query it selects the town
code.In second query it selects the no of sites(sampleSites) with
respect to town codes from query 1. in third query it generates the no
of sites with respect to no of sites where condition is town code.
Hope this would help.
anil