HI,
You are right, it takes the criteria from the form to build the query.
You can see how I build it in post nr 12.
this is the criteria in the query disign view:
Code:
Between [Forms]![frm_main_form]![from_date_txt] And [Forms]![frm_main_form]![to_date_txt]
Code:
[Forms]![frm_main_form]![branch_txt]
Code:
[Forms]![frm_main_form]![account_txt]
(the below code runs the loop and each time it passes the record to the variables that are used to run the query)
so this code gets the creates the criteria for the query and pass it to the form unbound fields:
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
i = 0
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
from_date_txt = rs![from_date]
to_date_txt = rs![to_date]
branch_txt = rs![branch]
account_txt = rs![account]
'Now do something with these variables
If rs.EOF = True Then
Set rs = Nothing 'Release machine's memory
Set db = Nothing
Exit Sub
End If
i = i + 1
Check_first_branch_nr_Click
rs.MoveNext
Loop
rs.Close 'Close recordset when you are done.
Set rs = Nothing
Set db = Nothing
End Sub
And this is the code that outputs the query to excel:
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
For me it would be already good if the above code could be adjusted to put it all in the same workbook and create for each time the query has a new recordset, to add one more sheet to the workbook to it and paste it there.
Greetings.
Greetings.