A
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
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