Access Newbie: Need to execute second SQL query in VBA for a report 1

[MaxHeadroom]

Hi,

I'm putting together an access DB for a confernece I'll be involved in running. I've got a main table containing a list of people, their groups, and their roles. I'm using access XP / 2002 and its an MDB file.

I have a query which selects people who have role ID 1, formats their names properly, and has returns FullName and GroupID. I also have tables defining the Groups and the Roles.

I run a report from this query which produces a very basic list :
[tt]
Committee 1 Name
List of Members
Committee 2 Name
List of Members
etc...[/tt]

I'd like it to have Committee Name - Chairpeople in the header for each committee. The probelm is that chairpeople can have different titles (so different Role_IDs) The roles table looks like this:
[tt]
ID | RoleName | Delegate | Chair | Organiser
autonum | String | yes/no | y/n | y/n[/tt]

I want to be able to execute an SQL query similar to this:

SELECT Participants.FirstName, Participants.Surname, Roles.Chair, Roles.RoleName FROM Roles INNER JOIN Participants ON Roles.ID = Participants.Role WHERE (((Roles.Chair)=True) AND ((Participants.Committee)= '" & Me![Committee] & "'))

And then process the results so that for each record it returns, create / append a string FirstName Lastname, FirstName Lastname..

Then create a string CommitteeName - Chairsnames and assign it to an unbound control on the report
Me![CommTitle] = namestring.

But I can't figure it out. I've tried using recordsets but can't get it to work properly. I thought I'd got it with a DAO recordset, but you can't use an SQL query as a source for a DAO recordset in a Jet database. I'd really appreciate any help/tips on this.

 

[randysmid]

Hi,
You can simply use a DLookup in the group header for Committee. I wrote an FAQ on DSum, which uses the same criteria and parameters as a DLookup. faq703-3066]

HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association

 

[MaxHeadroom]

The problem with Dlookup is that it only returns one value - Each committee will have at leats 2 chairpeople.

 

[randysmid]

Ok,
Another technique is to build the query in the Query object, and get it to work correctly so that it returns the two people you are expecting. Then, switch to SQL view and copy the code, then inserting it into the OnFormat event of the Group header.

HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association

 

[MaxHeadroom]

That's pretty much the approach I'm trying to take - the SQL is from the query builder, and I know it works. The problem is getting access to display those two names (or one name if the committee only has one chairperson) in the header.

I was trying to:

Set a recordset with that sql as the source
Do a For / Next loop to go through each record, pick out the names, and add them to a string.
But I couldn't get it to work. The problem seemed to be that all the examples I'd found were using DAO.recordset which can only have a table as the data source in a Jet DB, whereas I need the INNER JOIN in order to get everyone who's role has Chair=true.

 

[randysmid]

Hmmm, I think you should be using two strings, rather than just one. With each name you fill in, then use an incrementer (intRecordCount) to keep track of whether you are writing to the first chair field, or the second. Of course, you are correct in looping through the selected records. After running the SQL, your recordset should have two records in it. Then, you can use "MoveNext" to loop. I did notice that you need to define a recordset variable, such as:

rstChairmen as Recordset
Set rstChairmen = CurrentDB.OpenRecordset("Select Participants.FirstName, etc,..........
'should return two records
' can now loop using MoveFirst, MoveNext


HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association

 

[MaxHeadroom]

I've got the following so far, just to see if I can even open the recordset:

Option Compare Database

Private Sub grpHeaderCommitteeID_Format(Cancel As Integer, FormatCount As Integer)
Dim rstChairmen As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT Participants.FirstName, Participants.Surname, Roles.Chair, Roles.RoleName FROM Roles INNER JOIN Participants ON Roles.ID = Participants.Role WHERE (((Roles.Chair)=True) AND ((Participants.Committee)= '" & Me![Committee] & "'))"
Set rstChairmen = CurrentDb.OpenRecordset(strSQL)
End Sub

I get the following run-time error
[tt] Run-time error '3464'
Data type mismatch in criteria expression.[/tt]

I think its because the openrecordset function only accepts a table name as the source for a JET database.

 

[randysmid]

Hi,
Is CommitteeId a numeric field? If so, don't encase the Me![Committee] in quote marks.

HTH,
Randy Smith, MCP
rsmith@cta.org
California Teachers Association

 

[MaxHeadroom]

Thats fantastic - did the trick nicely. Here's the final code I used:

Option Compare Database
Dim FirstPass As Integer

Private Sub grpHeaderCommitteeID_Format(Cancel As Integer, FormatCount As Integer)
FirstPass = False
Dim rs As DAO.Recordset
Dim strSQL As String
Dim strCHAIRS As String

strSQL = "SELECT Participants.FirstName, Participants.Surname, Roles.Chair, Roles.RoleName FROM Roles INNER JOIN Participants ON Roles.ID = Participants.Role WHERE (((Roles.Chair)=True) AND ((Participants.Committee)= " & Me![Committee] & "))"
Set rs = CurrentDb.OpenRecordset(strSQL)
With rs
    Do While Not .EOF
        If Not FirstPass Then
            strCHAIRS = ![FirstName] & " " & ![Surname] & " (" & ![RoleName] & ")"
            FirstPass = True
        Else
            strCHAIRS = strCHAIRS & ", " & ![FirstName] & " " & ![Surname] & " (" & ![RoleName] & ")"
        End If
    .MoveNext
    Loop
    
    .Close
End With

Me![CommTitle] = Me![ShortName] & " - " & strCHAIRS

End Sub

Thanks a million for the help