2010: Display querry/table on a form using VBA

[Lingg]

Hey!
I'm trying to make a form that displays data from a query. The only problem is this query is a query of a query. And on top of that the first query needs to update and immediately take affect on the other Query.

Let me start over. I have two tables. one is a list of people. Another is a list of points the people from the first table have received, organized by month and year. I want to display on a form those people that are missing from the list from a specific year and month. There are combo boxes for the year and month I want to feed off of.

I've made one query that takes all the people in the point table for a specific month/year. This Query is called "Miss_Feed"

SELECT DISTINCT [Operator_Last_name] & ', ' & [Operator_First_name] & ' #' & [PerNumber] AS Expr1, tblScore_Card_Points.ScoreMonth, tblScore_Card_Points.Scoreyear, tblScore_Card_Points.PNumber
FROM [List of Operators] LEFT JOIN tblScore_Card_Points ON [List of Operators].[PerNumber] = tblScore_Card_Points.[PNumber]
WHERE (((tblScore_Card_Points.ScoreMonth)="September") AND ((tblScore_Card_Points.Scoreyear)=2011))
ORDER BY [Operator_Last_name] & ', ' & [Operator_First_name] & ' #' & [PerNumber];

The next query links off of the one above. It finds those people not in the query above

SELECT [List of Operators].Operator_First_name, [Operator_Last_name] & ", " & [Operator_First_name] & "# " & [PerNumber] AS [Operator Full Name], Miss_Feed.PNumber
FROM [List of Operators] LEFT JOIN Miss_Feed ON [List of Operators].PerNumber = Miss_Feed.PNumber
WHERE (((Miss_Feed.PNumber) Is Null));

This Query works however it doesn't update when I change the criteria of the first query. I've made this code to run when ever the Combo boxes are updated

Dim rst, rsD As DAO.Recordset
Dim strSQL
Dim striSQL

If Nz(Me.cbMonth, "") = "" Then
        Exit Sub
    Else
        strSQL = "SELECT DISTINCT [Operator_Last_name] & ', ' & [Operator_First_name] & ' #' & [PerNumber] AS Expr1, tblScore_Card_Points.ScoreMonth, tblScore_Card_Points.Scoreyear, tblScore_Card_Points.PNumber " & _
                 "FROM [List of Operators] LEFT JOIN tblScore_Card_Points ON [List of Operators].[PerNumber] = tblScore_Card_Points.[PNumber] " & _
                 "WHERE (((tblScore_Card_Points.ScoreMonth) = '" & Me.cbMonth & "') And ((tblScore_Card_Points.Scoreyear) = " & Me.cbYear & ")) " & _
                 "ORDER BY [Operator_Last_name] & ', ' & [Operator_First_name] & ' #' & [PerNumber];"
End If
Set rst = CurrentDb.OpenRecordset(rst, dbOpenDynaset)


striSQL = "SELECT [List of Operators].Operator_First_name, [Operator_Last_name] & ', ' & [Operator_First_name] & '# ' & [PerNumber] AS [Operator Full Name] " & _
          "FROM [List of Operators] LEFT JOIN rst ON [List of Operators].PerNumber = rst.PNumber " & _
          "WHERE (((rst.PNumber) Is Null));"

Set rsD = CurrentDb.OpenRecordset(striSQL, dbOpenDynaset)

but I'm stuck on how to display rsD in a Form.
Any help would be great!, thanks in advance.

 

[HiTechCoach]

I see a few issue with teh code in teh last code bloack.

This line:

Dim rst, rsD As DAO.Recordset

and this line

Set rst = CurrentDb.OpenRecordset(rst, dbOpenDynaset)

Try:

Dim rst As DAO.Recordset , rsD As DAO.Recordset  

Dim strSQL
Dim striSQL

If Nz(Me.cbMonth, "") = "" Then
        Exit Sub
    Else
        strSQL = "SELECT DISTINCT [Operator_Last_name] & ', ' & [Operator_First_name] & ' #' & [PerNumber] AS Expr1, tblScore_Card_Points.ScoreMonth, tblScore_Card_Points.Scoreyear, tblScore_Card_Points.PNumber " & _
                 "FROM[List of Operators] LEFT JOIN tblScore_Card_Points ON[List of Operators].[PerNumber] = tblScore_Card_Points.[PNumber] " & _
                 "WHERE (((tblScore_Card_Points.ScoreMonth) = '" & Me.cbMonth & "') And ((tblScore_Card_Points.Scoreyear) = " & Me.cbYear & ")) " & _
                 "ORDER BY [Operator_Last_name] & ', ' & [Operator_First_name] & ' #' & [PerNumber];"
End If

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

 

[HiTechCoach]

About displaying:

The sipliest was is to use a bound form or sub form. Either bound to a table or a query.

You may want to append the data into a temp table. Use a form that is boud to this temp table to display the results.