I have (Access 07) 2 tables that link on the field: ForID
Tables
A: Contains AgyCode and ForID. There are multiple ForID for each AgyCode
B: has acct info and the ForID the acct belongs to
Queries:
qryDistinct: getting the distinct AgyCode from A
qryFile: getting acct info for an AgyCode which is being passed as a parameter
Basically, i used to write a query for each agency and OutPutTo or a TransferSpreadsheet for each query. I would like to write something that grabs all of the ForIDs for each AgyCode and produces a spreadsheet for each.
Here is the code I have so far. There is nothing for the export because the exports I am used to are not compatable with recordsets. So far I seem to have it working as far as (qryDistinct) passing each AgyCode as a parameter to qryFile. That's about it. I've been pretty much fiuring this out as I go. I've searched all over and found variations on what I'm looking for, but nothing I've been able to translate to my needs. Thanks!
Private Sub Command0_Click()
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim Agy As String
Dim db As DAO.Database
Dim Myqdf As DAO.QueryDef
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("qrydistinct")
If rs1.RecordCount = 0 Then Exit Sub
rs1.MoveFirst
' loop through
Do Until rs1.EOF
Agy = rs1![agycode]
' If matching record is found then update field in second recordset to value you determine
' MsgBox (Agy)
Set Myqdf = db.QueryDefs("qryFile")
Myqdf.Parameters("Agency") = rs1![agycode]
Set rs2 = Myqdf.OpenRecordset()
Do Until rs2.EOF
' MsgBox (rs2![dbacct])
rs2.MoveNext
Loop
rs1.MoveNext
Loop
End Sub
Tables
A: Contains AgyCode and ForID. There are multiple ForID for each AgyCode
B: has acct info and the ForID the acct belongs to
Queries:
qryDistinct: getting the distinct AgyCode from A
qryFile: getting acct info for an AgyCode which is being passed as a parameter
Basically, i used to write a query for each agency and OutPutTo or a TransferSpreadsheet for each query. I would like to write something that grabs all of the ForIDs for each AgyCode and produces a spreadsheet for each.
Here is the code I have so far. There is nothing for the export because the exports I am used to are not compatable with recordsets. So far I seem to have it working as far as (qryDistinct) passing each AgyCode as a parameter to qryFile. That's about it. I've been pretty much fiuring this out as I go. I've searched all over and found variations on what I'm looking for, but nothing I've been able to translate to my needs. Thanks!
Private Sub Command0_Click()
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim Agy As String
Dim db As DAO.Database
Dim Myqdf As DAO.QueryDef
Set db = CurrentDb()
Set rs1 = db.OpenRecordset("qrydistinct")
If rs1.RecordCount = 0 Then Exit Sub
rs1.MoveFirst
' loop through
Do Until rs1.EOF
Agy = rs1![agycode]
' If matching record is found then update field in second recordset to value you determine
' MsgBox (Agy)
Set Myqdf = db.QueryDefs("qryFile")
Myqdf.Parameters("Agency") = rs1![agycode]
Set rs2 = Myqdf.OpenRecordset()
Do Until rs2.EOF
' MsgBox (rs2![dbacct])
rs2.MoveNext
Loop
rs1.MoveNext
Loop
End Sub