Loop through records one by one

[megatronixs]

Hi all,

I made in excel a automation to do google searches for a range in a sheet.
the code goes row by row and uses the names in the range to insert them into google.
I would like to do the same in access, but I can't find any info on it.

I created a sample database where I inserted a subform in the main form. The subform has several records in it and is shown as a datasheet.

let's say I wanted to search for:
Pino
John
Samuel

For each name I need to search the first page and second one, and this is done for news search as well, page one and 2.

Here is the code I used in excel:

Private Sub btn_google_searches_Click()
Dim ie As Object
'Dim rng As Range
'Dim LastRow As Long
'Dim rCell As Range
'Dim rRng As Range
'ExecWB 6, 2

    Set ie = CreateObject("InternetExplorer.Application")
    'With ActiveSheet
    'LastRow = Cells(.Rows.Count, "B").End(xlUp).Row
    'Set rRng = Range("B3:B" & LastRow)
    
     
    ie.Visible = True
        'With ActiveSheet
    
            'For Each rCell In rRng.Cells
        'Debug.Print rCell.Address, rCell.Value
    ie.Navigate "http://www.google.com" & "/" & "search?q=" & rCell & "&start=0"
    
        Do While ie.Busy: DoEvents: Loop
        Do While ie.ReadyState <> 4: DoEvents: Loop
        
'        ie.ExecWB 6, 2
'    MsgBox "You go now to the second page web search"
'    ie.Navigate "http://www.google.com" & "/" & "search?q=" & rCell & "&start=10"
'        Do While ie.Busy: DoEvents: Loop
'        Do While ie.ReadyState <> 4: DoEvents: Loop
'    ie.ExecWB 6, 2
'
'    MsgBox "You go now to the first page news search"
'
'    ie.Navigate "http://www.google.com" & "/" & "search?q=" & rCell & "&tbm=nws&start=0"
'
'        Do While ie.Busy: DoEvents: Loop
'        Do While ie.ReadyState <> 4: DoEvents: Loop
'    ie.ExecWB 6, 2
'    MsgBox "You go now to the second page news search"
'    ie.Navigate "http://www.google.com" & "/" & "search?q=" & rCell & "&tbm=nws&start=10"
'
'        Do While ie.Busy: DoEvents: Loop
'        Do While ie.ReadyState <> 4: DoEvents: Loop
'    ie.ExecWB 6, 2
'
'    MsgBox "You go now to the next search"
'    Next rCell
'End With
'End With
MsgBox "This is the end of your Google searches mi amigo!"

End Sub

I also attach the sample database

Any help to get this moving would be really nice.

Greetings

 

[stopher]

You can use recordsets. Take a look here how to loop through a recordset
https://msdn.microsoft.com/en-us/library/bb243789(v=office.12).aspx

hth
Chris

 

[megatronixs]

Hi stohper,

I made the below with help of your tip and link.
The only thing that is now going on is that it will continue the loop once at the end.
Can you let me know where I went wrong?

Option Compare Database
Option Explicit
Sub btn_google_searches_Click()
Dim ie As Object
Dim db As DAO.Database
Set db = CurrentDb
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("SELECT * FROM tbl_google_searches", dbOpenDynaset)
rs.MoveFirst
Do Until rs.EOF
'Debug.Print "Search For: " & rs!Search_For 'Shows value of active record from ID field in Immediate Window
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = True
        MsgBox "You go now to the first page web search"
    ie.Navigate "[URL]http://www.google.com[/URL]" & "/" & "search?q=" & rs!Search_For & "&start=0"
        Do While ie.Busy: DoEvents: Loop
        Do While ie.ReadyState <> 4: DoEvents: Loop
ie.ExecWB 6, 2
        MsgBox "You go now to the second page web search"
    ie.Navigate "[URL]http://www.google.com[/URL]" & "/" & "search?q=" & rs!Search_For & "&start=10"
        Do While ie.Busy: DoEvents: Loop
        Do While ie.ReadyState <> 4: DoEvents: Loop
ie.ExecWB 6, 2
        MsgBox "You go now to the first page news search"
    ie.Navigate "[URL]http://www.google.com[/URL]" & "/" & "search?q=" & rs!Search_For & "&tbm=nws&start=0"
        Do While ie.Busy: DoEvents: Loop
        Do While ie.ReadyState <> 4: DoEvents: Loop
ie.ExecWB 6, 2
        MsgBox "You go now to the second page news search"
    ie.Navigate "[URL]http://www.google.com[/URL]" & "/" & "search?q=" & rs!Search_For & "&tbm=nws&start=10"
        Do While ie.Busy: DoEvents: Loop
        Do While ie.ReadyState <> 4: DoEvents: Loop
ie.ExecWB 6, 2
MsgBox "This is the end of your Google searches mi amigo!"
rs.MoveNext 'Move to the next record
Loop
rs.Close 'Close recordset when you are done.
End Sub

Greetings

 

[megatronixs]

ok, I solved

Private Sub Command550_Click()
Dim ie As Object
Dim db As DAO.Database
Set db = CurrentDb
Dim rs As DAO.Recordset
Set rs = db.OpenRecordset("SELECT * FROM tbl_google_search", dbOpenDynaset)
rs.MoveFirst
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = True
If rs!Google_Search = "" Then
Exit Sub
End If
Do Until rs.EOF
'Debug.Print "Search For: " & rs!Search_For 'Shows value of active record from Search_For field in Immediate Window
 
        'MsgBox "You go now to the first page web search"
    ie.navigate "[URL]http://www.google.com[/URL]" & "/" & "search?q=" & rs!Google_Search & "&start=0"
        Do While ie.Busy: DoEvents: Loop
        Do While ie.ReadyState <> 4: DoEvents: Loop
ie.ExecWB 6, 2
        MsgBox "You go now to the second page web search"
    ie.navigate "[URL]http://www.google.com[/URL]" & "/" & "search?q=" & rs!Google_Search & "&start=10"
        Do While ie.Busy: DoEvents: Loop
        Do While ie.ReadyState <> 4: DoEvents: Loop
ie.ExecWB 6, 2
        MsgBox "You go now to the first page news search"
    ie.navigate "[URL]http://www.google.com[/URL]" & "/" & "search?q=" & rs!Google_Search & "&tbm=nws&start=0"
        Do While ie.Busy: DoEvents: Loop
        Do While ie.ReadyState <> 4: DoEvents: Loop
ie.ExecWB 6, 2
        MsgBox "You go now to the second page news search"
    ie.navigate "[URL]http://www.google.com[/URL]" & "/" & "search?q=" & rs!Google_Search & "&tbm=nws&start=10"
        Do While ie.Busy: DoEvents: Loop
        Do While ie.ReadyState <> 4: DoEvents: Loop
ie.ExecWB 6, 2
 
rs.MoveNext 'Move to the next record
Loop
rs.Close 'Close recordset when you are done.
MsgBox "This is the end of your Google searches mi amigo!"
Exit Sub
End Sub