Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91

    using loop to get the content of a table (4 fields) to run a query


    Hi all,

    Some time ago I managed to make a macro that will use a word from at table field to do a google search and then loop one by one till end of the records.

    How would I go about to re-use the below code to get the data from 4 fields instead of 1 and use it to feed a query? (the query now gets the 4 (parameter) fields from text fields in the form)


    Code:
     Dim ie As Object
    Dim db As DAO.Database
    Set db = CurrentDb
    Me.Refresh
    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 "http://www.google.com" & "/" & "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 "http://www.google.com" & "/" & "search?q=" & rs!Google_Search & "&start=10"
    'Do While ie.Busy: DoEvents: Loop
    Do While ie.ReadyState <> 4: DoEvents: Loop
    ie.ExecWB 6, 2
    Do While ie.ReadyState <> 4: DoEvents: Loop
    MsgBox "You go now to the first page news search"
    ie.navigate "http://www.google.com" & "/" & "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 "http://www.google.com" & "/" & "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
    MsgBox "You go now to the next search"
    Loop
    rs.Close 'Close recordset when you are done.
    MsgBox "This is the end of your Google searches mi amigo!"
    

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    You have
    "search?q=" & rs!Google_Search & "&start=0"

    You need to add another column from your recordset object by concatenating it. So maybe something like ...
    "search?q=" & rs!Google_Search & " " & rs![AnotherField] & " " & rs![AnotherField2] & "&start=0"

  3. #3
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi,

    The above code works nice. There is only one catch. I actually wanted to re-use some of the code to get the current record's 4 fields and feed them to a query.
    I have now 4 unbound text fields in a form that are fed to the query. This way I can only do one search at a time and then export it to excel. The idea is to use a table with 4 fields and but there a bunch of records to be used in the query and get all those recordsets into excel sheet. The tricky part is that it needs to be in one excel sheet and not per row, but, per column. So, the first result will start in column A till F and depending how many records they will be per row. The second query result will look for second free column to the right from the first and then put there the next 5 columns. And so on till there are no more query results. Normaly there are about 5 results and could be max 15.


    this is the table with the 4 fields: tbl_batch_process.
    fields: [from_data], [to_date], [branch], [account_nr]

    I started to build this code so far, but it is horible what I did so far :-(
    Code:
     Private Sub btn_batch_process_Click()
    Dim db As DAO.Database
    Set db = CurrentDb
    Me.Refresh
    Dim rs1 As DAO.Recordset
    Dim rs2 As DAO.Recordset
    Set rs1 = db.OpenRecordset("SELECT from_date FROM tbl_batch_process", dbOpenDynaset)
    Set rs2 = db.OpenRecordset("SELECT to_date FROM tbl_batch_process", dbOpenDynaset)
     
    rs1.MoveFirst
    rs2.MoveFirst
    If rs1!from_date = "" Then
    Exit Sub
    End If
    If rs2!to_date = "" Then
    Exit Sub
    End If
    Do Until rs1.EOF
    'Do Until rs2.EOF
    'Debug.Print rs
    Loop
    rs1.Close 'Close recordset when you are done.
    rs2.Close 'Close recordset when you are done.
    End Sub
    Any idea if it can be done much better?

    Greetings.

  4. #4
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi all,

    Any help possible?

    Greetings.

  5. #5
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    The above code works nice. There is only one catch. I actually wanted to re-use some of the code to get the current record's 4 fields and feed them to a query.
    What code are you referring to? The example I provided is considering your objective to, "get the current record's 4 fields". Not sure what you mean by "feed them to a query."

    I have now 4 unbound text fields in a form that are fed to the query. This way I can only do one search at a time and then export it to excel. The idea is to use a table with 4 fields and but there a bunch of records to be used in the query and get all those recordsets into excel sheet.
    Are you using unbound textbox controls or a table? The code you provided in post #1 is using values from a table, tbl_batch_process.

    The tricky part is that it needs to be in one excel sheet and not per row, but, per column. So, the first result will start in column A till F and depending how many records they will be per row.
    If you are retrieving data from Excel then you will need to link to the worksheet or automate Excel via VBA.

    The second query result will look for second free column to the right from the first and then put there the next 5 columns. And so on till there are no more query results. Normaly there are about 5 results and could be max 15.
    This is not making any sense to me. How is a result going to look for something? Either you have a query or you have a result.

  6. #6
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi,
    I made a dummy database where you can see what I need.
    The database is just a simple form with a subform inside and a buton. the table "tbl_main_data" is empty and just for test there. The real database is linked to external table in Oracle I think. The query is based on the linked table.
    All I want to create is a tool that will take record by record (looping) and pass the values from the fields in the subform to the query and then export to excel. The next loop will export to the same excel in the same sheet, but leaving one column space between the previous result.

    It could be like this, just to visualize different:

    fields from the "tbl_batch_process" [date_from] , [date_to], [branch], [accoun_nr] ---> to the query: qry_batch_process
    the 2 fields: [date_from] , [date_to] are used to create a between 2 dates in the query. The date field from the linked table is in text format: 20151024. Currently I get the values into the query by unbounds text boxes on the form: Between [Forms]![frm_main_form]![from_date_txt] And [Forms]![frm_main_form]![to_date_txt]

    I hope this is more clear.

    Greetings.
    Attached Files Attached Files

  7. #7
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    I did not look at your DB, but if you want to transfer records from a subform to an Excel worksheet, you could use DAO to create a clone of the subform's Recordset.

    Something like
    dim rs as dao.recordset
    set rs = Me.FormName!SubformControlName.Form.RecordsetClone

    Then with the recordset object you could automate Excel and use Excel's CopyFromRecordset method. Here is an example.
    https://www.accessforums.net/program...tml#post281494

  8. #8
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi,

    This will not work as I need to query the external table and loop record by record to get it to the excel file.
    I have a query that takes the parameters from the form. There are 4 fields in the form that holds the info passed to the query.
    maybe it can be looped from the table where the records will be held for batch processing?

    It could look like this (just thinking loud):

    table: tbl_batch_process
    fields: [from_date_txt], [to_date], [branch], [account_nr] (there will be 5 fields with resulst, but need only 4 criteria to get it)

    the loop will run and go by the first record, pass those fields to the query and get results

    the results of the query need to go to an excel sheet and pasted on the first available column, the next query resulst should be pasted in the same excel sheet but, on the second empty column to the right

    loop through all the records till the end (I will include a message box at the end to show that it is finished)


    I hope this more clear :-)

    Greetings.

  9. #9
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi,

    I'm working with this code to get the contents of the current record so I can use it in the query, but I only get results for the first one "from_date"
    any clue what I miss to get the rest of the fields from the record?


    Code:
     Private Sub btn_batch_process_Click()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim rs As DAO.Recordset
    Dim from_date As String
    Dim to_date As String
    Dim branch As String
    Dim account As String
     
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT from_date, to_date, branch, account FROM tbl_batch_process", dbOpenDynaset)
    Debug.Print "Search For: " & rs!from_date; to_date; branch; account; ""
     
    rs.MoveFirst
    If rs!from_date = "" Then
    Exit Sub
    End If
     
     
    Do Until rs.EOF
    Loop
    rs.Close 'Close recordset when you are done.
     
    End Sub

  10. #10
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Ok, getting closer :-)

    if I add " rs! " in front of the other fields, then it works.
    Now I need to pass those to the query to get the data from the table.

    Greetings.

  11. #11
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Instead of
    rs.MoveFirst
    If rs!from_date = "" Then
    Exit Sub
    End If

    You should do another test. It seems you want to make sure there are records within the recordset you just opened. So something like the following is more appropriate for making sure your recordset has something in it.
    Code:
    If rs.EOF = True Then
    set rs = nothing    'Release machine's memory
    set db = nothing
    Exit Sub
    End If
    
    rs.MoveFirst
    If you want to iterate all of the records in your recordset you can do the following.

    Code:
    dim strSQL as string
    
    Do Until rs.EOF
    
    from_date = rs![from_date]
    to_date = rs![to_date]
    branch = rs![branch]
    account = rs![account]
    
    
    'Now do something with these variables
    strSQL = "INSERT INTO TableName (from_date, to_date, branch, account) " & _
             "VALUES ('" & from_date & "', '" & to_date & "', '" & branch & "');"
    db.Execute strSQL, dbFailOnError
    
    
    Loop
    rs.Close 'Close recordset when you are done.
    set rs = nothing
    set db = nothing
    However, I do not see any real reason to do any of this looping stuff. I do not understand what you are trying to accomplish here. What is it you are trying to do with the data in your tbl_batch_process? This thread started out with you trying to do something with the Internet Explorer browser window and then you mentioned Excel and now you are iterating rows in an Access table.

    What is it you are trying to do, exactly?

  12. #12
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi,

    I must confess that my initial post was to bad and I just asked it wrong as I wanted to see how I could re-use the previous code I made to go do the below, no intentions for IE automation.

    All I'm trying to do is to run a query in access to query a table and move those results to excel sheet. There is a table where I will put info on all the dates, branches and accounts I want to do a batch process to get multiple query resulst into to the excel file.
    This means, taking the first record from the table "tbl_batch_process" to get the first criteria, it runs the query and moves those result into excel. the move rs.MoveNext moves to the next record for the criteria and runs again the query to get the results to the excel sheet to the next empty column.

    I managed to do some looping and getting all the 4 criterias for the query, so next stop is to get it to excel :-)
    Code:
    Private Sub btn_batch_process_Click()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim rs As DAO.Recordset
    Dim from_date As String
    Dim to_date As String
    Dim branch As String
    Dim account As String
    Dim strSQL As String
    Set db = CurrentDb
        Set rs = db.OpenRecordset("SELECT from_date, to_date, branch, account FROM tbl_batch_process", dbOpenDynaset)
    Dim strSQL As String
        rs.MoveFirst
    Do Until rs.EOF
    from_date = rs![from_date]
    to_date = rs![to_date]
    branch = rs![branch]
    account = rs![account]
    
    'Now do something with these variables
    strSQL = "SELECT from_date, to_date, branch, account FROM tbl_batch_process"
    If rs.EOF = True Then
        Set rs = Nothing    'Release machine's memory
            Set db = Nothing
        Exit Sub
    End If
            Debug.Print from_date, to_date, branch, account
            Debug.Print rs
        rs.MoveNext
        
    Loop
    rs.Close 'Close recordset when you are done.
    Set rs = Nothing
    Set db = Nothing
    End Sub
    I was previous running this code for individual processing:

    Code:
     Private Sub query_results1()
    Dim sFile As String
    Dim stDocName As String
    sFile = "C:\Documents\Query_1.xls"
    stDocName = "Query_1"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Query1", "C:\Documents\Query_1.xls", True
    Dim xlApp As Object
    Dim xlSheet As Object
    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
    xlApp.Visible = True
    With xlSheet
    .Range("A1").Value = "QUERY DATE"
    .Range("B1").Value = "BRANCH NO"
    .Range("C1").Value = "ACCOUNT NO"
    .Range("D1").Value = "PRODUCTS"
    .Range("A1:D1").Font.Bold = True
    .Range("A1:D1").EntireColumn.AutoFit
    End With
    End Sub
    As you can see from above attempts, it is all about a batch processing for all the records in the table :-)

    Greetings.

  13. #13
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    If you want all of the results in a single worksheet, I suggest you use a temp table in Access. Create a table that looks similar to how you want your worksheet to look. You will need to nest an append query within a DAO loop to populate your temp table. Then, export the data in your temp table to Excel.

    The first thing you need to do is create a DAO recordset that loops through the records that contain the key for your nested append query. It is just a guess, but maybe you could get those key values from a recordset similar to the following. You should use the Query Designer to determine what the query will look like.
    Code:
    dim dtVariable as Date
    dtVariable = Date
    Set rs1 = db.OpenRecordset("SELECT PrimaryKey FROM tbl_batch_process WHERE from_date > #" & dtVariable & "#", dbOpenDynaset)

  14. #14
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi,

    I made it finaly work like this:

    Code:
    Private Sub btn_batch_process_Click()
    Dim db As DAO.Database
    Set db = CurrentDb
    Dim rs As DAO.Recordset
    
    Dim from_date As String
    Dim to_date As String
    Dim branch As String
    Dim account As String
    Dim strSQL As String
    Set db = CurrentDb
        Set rs = db.OpenRecordset("SELECT from_date, to_date, branch, account FROM tbl_batch_process", dbOpenDynaset)
    
        rs.MoveFirst
    Do Until rs.EOF
    [Forms]![frm_main_form]![from_date_txt] = rs![from_date]
    [Forms]![frm_main_form]![to_date_txt] = rs![to_date]
    [Forms]![frm_main_form]![branch_txt] = rs![branch]
    [Forms]![frm_main_form]![account_txt] = rs![account]
    
    Call Check_first_branch_nr_Click
    
    If rs.EOF = True Then
        Set rs = Nothing    'Release machine's memory
            Set db = Nothing
        Exit Sub
    End If
        rs.MoveNext
        
    Loop
    rs.Close 'Close recordset when you are done.
    Set rs = Nothing
    Set db = Nothing
    End Sub
    Sub Check_first_branch_nr_Click()
    If Me.branch_txt Like "980*" Then
        query_results2
            Else
        query_results1
    End If
    End Sub
    Here is the part that gets the results of the query to excel sheet. All I need now is to make it use only one excel sheet and paste the records inside, but then the second empty column from the right.
    A good staring point for me would be to know how to use the excel sheet that is refered in the below code and not create new, but open if it is there and get rid of the data first befor pasting.

    Code:
     Private Sub query_results1()
    Dim sFile As String
    Dim stDocName As String
    sFile = "C:\Documents\Query_1.xls"
    stDocName = "Query_1"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Query1", "C:\Documents\Query_1.xls", True
    Dim xlApp As Object
    Dim xlSheet As Object
    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
    xlApp.Visible = True
    With xlSheet
    .Range("A1").Value = "QUERY DATE"
    .Range("B1").Value = "BRANCH NO"
    .Range("C1").Value = "ACCOUNT NO"
    .Range("D1").Value = "PRODUCTS"
    .Range("A1:D1").Font.Bold = True
    .Range("A1:D1").EntireColumn.AutoFit
    End With
    End Sub

  15. #15
    megatronixs is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Posts
    91
    Hi,

    How can I close the excel sheet?

    I have this part to open it, but to close?
    Code:
    Dim xlApp As Object
    Dim xlSheet As Object
    Set xlApp = CreateObject("Excel.Application")
    Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
    xlApp.Visible = True
    Greetings.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-10-2013, 07:04 AM
  2. Replies: 1
    Last Post: 06-15-2012, 10:47 AM
  3. create table using something like loop query
    By learning_graccess in forum Queries
    Replies: 20
    Last Post: 04-18-2012, 09:52 AM
  4. Replies: 1
    Last Post: 12-01-2010, 11:01 AM
  5. Loop a table to use field in query
    By jdubp in forum Programming
    Replies: 0
    Last Post: 03-04-2008, 11:48 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums