Query by Recordset in a loop

[OSCSLeo]

I have 2 tables
a "Name" Table containing "LASTNAME" and "Events" Table containing "LASTNAME" and "MISSIONS"
I have a query called "Events"
I have a report that is based on "Events"
which lists all personnel assigned to a mission.
What I would like to do is print out a report for each "LASTNAME" and associated Missions.
I presume I would have to use a loop to filter the "Events" query by the "LASTNAME"
in the "NAMES" table. Then print the report, then continue the loop. I would like to do this in code but I am by no means an expert in VBA. If some VBA Guru could help or provide code I would greatly appreciate it.
Thanks

 

[OSCSLeo]

Here is more information

The problem seems to be either I'm not getting the sql statement filtered by strName
or if that is occurring. The events report does not see that as a recordset source..or the more likely conclusion Im all hosed up..grin... Help appreciated

Private Sub Command0_Click()
Dim db As Database
Dim rsName As DAO.Recordset
Dim strName As String
Dim strSQL As String
Set rsName = CurrentDb.OpenRecordset("Names")
Do While Not rsName.EOF
strName = rsName.Fields("LastName").Value
strSQL = ("Select * From Events1 where LastName = 'strName'")
Debug.Print strName
Debug.Print strSQL
DoCmd.OpenReport "events", acViewPreview, strSQL
DoCmd.OutputTo acOutputReport, "Events", acFormatRTF, , 0
rsName.MoveNext
Loop
Set rsName = Nothing
End Sub

 

[Jack Cowley]

If I understand this correctly a single person can be involved in many missions. If that is so then create a One to Many relationship between the 'Persons Table' and the 'Missions Table'. Then a Report/Subreport will allow you to print the persons name (main report) and their missions (subreport) in a fairly straightforward manner.

 

[OSCSLeo]

Thanks for the reply. What I want to do is print one record per report (separate report for each person, looping through the name list til each person has his report of missions he is assigned to.

 

[Jack Cowley]

If one person can have many missions then the scheme that I suggested is what you want. What you want to do then is filter the data (via a query) on the date, or some other unique field, in the Mission table. This will give you each person and that particular mission.

 

[OSCSLeo]

Jack,
Yes your right, but what I want to do with the report is email the individual's report to the person, so it would have to be a totally separate report, not a subreport. Sorry if I was unclear as to the intent.
Jeff

 

[simongallop]

Step 1: Make sure that your query shows all the missions for each person in the format of 1 record per mission per person:

ie
Smith Mission1
Smith Mission2
Smith Mission5
Jones Mission2 etc.

Once you have that then the code that you need will look at the surname table, loop through each record taking the surname and using it to limit the query, output the report, then do next surname till the end

S0:

Dim MyRS as recordset
Dim MyQD as QueryDef
Dim MyStr as string
Dim Surname as string
Dim EMailAdd as string

set MyRS = CurrentDB.OpenRecordset("TableNameWithSurname")
Set MyQD = CurrentDB.QueryDefs("QueryName")

MyRS.MoveFirst
Do
MySurname = MyRS("NameofField")
EMailAdd = MyRS("NameofEMailField")
MyString = "SQL CODE FROM QUERY { SEE NOTE } "
MyQD.SQL = MyString
DoCmd.SendObject acSendReport, "NameofReport", acFormatTXT, EMailAdd, , , "YourMissions", "Find attached you missions"
MyRS.MoveNext
Loop Until MyRS.EOF =True
MyRS.Close
MyQD.Close
END


NOTE:: For the SQL string, place one of the surnames in the criteria of the query to limit the results to one person and then change the view to SQL. Copy the code and paste it as the string for MyString. Where the surname turns up (Field = 'Surname') change to Field = '" & MySurname & "' etc.

HTH

 

[OSCSLeo]

Thanks, Will give it a shot and let you know.
Appreciate the assistance.
Jeff

 

[OSCSLeo]

Harry,
Here is the code from program, I feel Im almost there, I pulled out the email part, and did an open report instead while getting it to work. I believe Im having syntax errors in the Mystring.
Thanks for the assist

Private Sub Command4_Click()
Dim MyRS As Recordset
Dim MyQD As QueryDef
Dim MyStr As String
Dim MyString As String
Dim MySurname As String
Dim EMailAdd As String
Set MyRS = CurrentDb.OpenRecordset("Names")
Set MyQD = CurrentDb.QueryDefs("Events1")
MyRS.MoveFirst
Do
MySurname = MyRS("LastName")
MyString = ("SELECT * from Events1 where LastName= MySurname")
MyQD.SQL = MyString
DoCmd.OpenReport "Events1", acViewPreview, MyQD.SQL
MyRS.MoveNext
Loop Until MyRS.EOF = True
MyRS.Close
MyQD.Close
End
End Sub

 

[simongallop]

MyString = "SELECT * FROM Events1 WHERE Lastname = '" & MySurname & "';"

Note: As MySurname is a string, if you were to hardcode the surname SMITH into the string you would have ....Lastname = 'SMITH'... ie strings are held in single quotes. So to put a variable you open SINGLE quotes then do the double quote followed by the Ampersand to insert the variable. So ' " & VAR & " '

HTH

 

[OSCSLeo]

Thanks Harry
that worked great. Appreciate the help.