VBA with ClickButton help

supportservice · #1 08-13-2015, 08:23 AM

I am having trouble with this,

I am trying to create another worksheet named "MemberList" just the "Pronunciation" worksheet but without the Address fields.

I am getting an error 400 and not sure what I am doing wrong.

Code:

With ActiveWorkbook.Connections("MemberList").OLEDBConnection
   Split1 = "SELECT et.EventDescription, person.LastName, person.FirstName, person.MembershipNumber 'Member #', person.Gender, " & _
       "( SELECT OrganizationName FROM entity.Organization WHERE Id = " & _
       "( SELECT TOP 1 OrganizationId FROM attribute.PersonMembership pm WHERE pm.PersonId = person.ID AND (MembershipTypeId IN (1, 2, 3, 4, 6, 7, 8)) AND EndDate > GETDATE() ) ) 'Home Club', " & _
   Split2 = "( SELECT TOP 1 OrganizationId FROM attribute.PersonMembership pm WHERE pm.PersonId = person.ID AND EndDate > GETDATE() ) ), " & _
       "( SELECT TOP 1 OrganizationId FROM attribute.PersonMembership pm WHERE pm.PersonId = person.ID AND EndDate > GETDATE() ) ), " & _
       "partner.MembershipNumber 'Partner Member #', partner.LastName + ‘ ‘ + RTRIM( partner.FirstName )  'Partner Name' "
   Split3 = "FROM entity.Competition comp " & _
       "JOIN attribute.CompetitionRegistration reg ON reg.CompetitionId = comp.Id AND reg.InvoiceNumber IS NOT NULL " & _
       "JOIN attribute.CompetitionSkatedEvents cse ON cse.RegistrationId = reg.Id  AND cse.Bye <> 1 AND cse.Withdrawn = 0 " & _
       "JOIN attribute.CompetitionEvents events on events.Id = cse.CompetitionEventId " & _
       "JOIN lookup.EventType et ON events.EventTypeId = et.Id "
   Split4 = "LEFT JOIN entity.Person person ON reg.PersonId = person.Id " & _
       "LEFT JOIN entity.Person partner ON cse.PartnerId = partner.Id " & _
       "WHERE comp.ID = " + CompId

   .CommandText = Split1 + Split3 + Split4 + " UNION ALL " + Split1

   Split3 = "FROM entity.Competition comp " & _
       "JOIN attribute.CompetitionRegistration reg ON reg.CompetitionId = comp.Id AND reg.InvoiceNumber IS NOT NULL " & _
       "JOIN attribute.CompetitionSkatedEvents cse ON cse.RegistrationId = reg.Id  AND cse.Bye <> 1 AND cse.Withdrawn = 0 AND cse.PartnerId IS NOT NULL " & _
       "JOIN attribute.CompetitionEvents events on events.Id = cse.CompetitionEventId " & _
       "JOIN lookup.EventType et ON events.EventTypeId = et.Id "
   Split4 = "LEFT JOIN entity.Person person ON cse.PartnerId = person.Id " & _
       "LEFT JOIN entity.Person partner ON reg.PersonId = partner.Id  " & _
       "WHERE comp.ID = " + CompId + " ORDER BY et.EventDescription, person.LastName, person.FirstName, person.Gender, partner.LastName, partner.FirstName "

   .CommandText = .CommandText + Split3 + Split4

Here's the "Pronunciation" code:

Code:

SELECT et.EventDescription, person.LastName, person.FirstName, person.MembershipNumber 'Member #', person.Gender, ( SELECT OrganizationName FROM entity.Organization WHERE Id = ( SELECT TOP 1 OrganizationId FROM attribute.PersonMembership pm WHERE pm.PersonId = person.ID AND (MembershipTypeId IN (1, 2, 3, 4, 6, 7, 8)) AND EndDate > GETDATE() ) ) 'Home Club', ISNULL( ( SELECT rinkAddr.City FROM entity.Organization org JOIN entity.Rink ON org.PrincipalHQRinkId = rink.Id JOIN attribute.Address rinkAddr ON rink.AddressId = rinkAddr.Id WHERE org.Id = ( SELECT TOP 1 OrganizationId FROM attribute.PersonMembership pm WHERE pm.PersonId = person.ID AND EndDate > GETDATE() ) ), ( SELECT City FROM attribute.Address WHERE Id = person.PrimaryAddressId ) ) 'City', ISNULL( ( SELECT rinkState.Description FROM entity.Organization org JOIN entity.Rink ON org.PrincipalHQRinkId = rink.Id JOIN attribute.Address rinkAddr ON rink.AddressId = rinkAddr.Id JOIN lookup.State rinkState ON rinkState.Id = rinkAddr.StateId WHERE org.Id = ( SELECT TOP 1 OrganizationId FROM attribute.PersonMembership pm WHERE pm.PersonId = person.ID AND EndDate > GETDATE() ) ), ( SELECT Description FROM attribute.Address addr JOIN lookup.State addrState ON addr.StateId = addrState.Id WHERE addr.Id = person.PrimaryAddressId ) ) 'State', partner.MembershipNumber 'Partner Member #', RTRIM( partner.FirstName ) + ' ' + partner.LastName 'Partner Name' FROM entity.Competition comp JOIN attribute.CompetitionRegistration reg ON reg.CompetitionId = comp.Id AND reg.InvoiceNumber IS NOT NULL JOIN attribute.CompetitionSkatedEvents cse ON cse.RegistrationId = reg.Id  AND cse.Bye <> 1 AND cse.Withdrawn = 0 JOIN attribute.CompetitionEvents events on events.Id = cse.CompetitionEventId JOIN lookup.EventType et ON events.EventTypeId = et.Id LEFT JOIN entity.Person person ON reg.PersonId = person.Id LEFT JOIN entity.Person partner ON cse.PartnerId = partner.Id WHERE comp.ID = 18506 UNION ALL SELECT et.EventDescription, person.LastName, person.FirstName, person.MembershipNumber 'Member #', person.Gender, ( SELECT OrganizationName FROM entity.Organization WHERE Id = ( SELECT TOP 1 OrganizationId FROM attribute.PersonMembership pm WHERE pm.PersonId = person.ID AND (MembershipTypeId IN (1, 2, 3, 4, 6, 7, 8)) AND EndDate > GETDATE() ) ) 'Home Club', ISNULL( ( SELECT rinkAddr.City FROM entity.Organization org JOIN entity.Rink ON org.PrincipalHQRinkId = rink.Id JOIN attribute.Address rinkAddr ON rink.AddressId = rinkAddr.Id WHERE org.Id = ( SELECT TOP 1 OrganizationId FROM attribute.PersonMembership pm WHERE pm.PersonId = person.ID AND EndDate > GETDATE() ) ), ( SELECT City FROM attribute.Address WHERE Id = person.PrimaryAddressId ) ) 'City', ISNULL( ( SELECT rinkState.Description FROM entity.Organization org JOIN entity.Rink ON org.PrincipalHQRinkId = rink.Id JOIN attribute.Address rinkAddr ON rink.AddressId = rinkAddr.Id JOIN lookup.State rinkState ON rinkState.Id = rinkAddr.StateId WHERE org.Id = ( SELECT TOP 1 OrganizationId FROM attribute.PersonMembership pm WHERE pm.PersonId = person.ID AND EndDate > GETDATE() ) ), ( SELECT Description FROM attribute.Address addr JOIN lookup.State addrState ON addr.StateId = addrState.Id WHERE addr.Id = person.PrimaryAddressId ) ) 'State', partner.MembershipNumber 'Partner Member #', RTRIM( partner.FirstName ) + ' ' + partner.LastName 'Partner Name' FROM entity.Competition comp JOIN attribute.CompetitionRegistration reg ON reg.CompetitionId = comp.Id AND reg.InvoiceNumber IS NOT NULL JOIN attribute.CompetitionSkatedEvents cse ON cse.RegistrationId = reg.Id  AND cse.Bye <> 1 AND cse.Withdrawn = 0 AND cse.PartnerId IS NOT NULL JOIN attribute.CompetitionEvents events on events.Id = cse.CompetitionEventId JOIN lookup.EventType et ON events.EventTypeId = et.Id LEFT JOIN entity.Person person ON cse.PartnerId = person.Id LEFT JOIN entity.Person partner ON reg.PersonId = partner.Id  WHERE comp.ID = 18506 ORDER BY et.EventDescription, person.LastName

I removed the parts related to the address fields but doing something wrong.

Now in the "MemberList" worksheet under Connections I have this:

Code:

SELECT et.EventDescription, person.LastName, person.FirstName, person.MembershipNumber 'Member #', 
( SELECT OrganizationName FROM entity.Organization WHERE Id = ( SELECT TOP 1 OrganizationId FROM attribute.PersonMembership pm WHERE pm.PersonId = person.ID AND (MembershipTypeId IN (1, 2, 3, 4, 6, 7, 8)) AND EndDate > GETDATE() ) ) 'Home Club',
person.Gender, partner.MembershipNumber 'Partner Member #', RTRIM( partner.FirstName ) + ' ' + partner.LastName 'Partner Name'
 FROM entity.Competition comp JOIN attribute.CompetitionRegistration reg ON reg.CompetitionId = comp.Id AND reg.InvoiceNumber IS NOT NULL JOIN attribute.CompetitionSkatedEvents cse ON cse.RegistrationId = reg.Id  
AND cse.Bye <> 1 AND cse.Withdrawn = 0 JOIN attribute.CompetitionEvents events on events.Id = cse.CompetitionEventId
 JOIN lookup.EventType et ON events.EventTypeId = et.Id LEFT JOIN entity.Person person ON reg.PersonId = person.Id LEFT JOIN entity.Person partner ON cse.PartnerId = partner.Id 
WHERE comp.ID = 18220 UNION ALL SELECT et.EventDescription, person.LastName, person.FirstName, person.MembershipNumber 'Member #', 
 ( SELECT OrganizationName FROM entity.Organization WHERE Id = ( SELECT TOP 1 OrganizationId FROM attribute.PersonMembership pm 
WHERE pm.PersonId = person.ID AND (MembershipTypeId IN (1, 2, 3, 4, 6, 7, 8)) AND EndDate > GETDATE() ) ) 'Home Club', 
person.Gender, partner.MembershipNumber 'Partner Member #', RTRIM( partner.FirstName ) + ' ' + partner.LastName 'Partner Name' FROM entity.Competition comp 
JOIN attribute.CompetitionRegistration reg ON reg.CompetitionId = comp.Id AND reg.InvoiceNumber IS NOT NULL JOIN attribute.CompetitionSkatedEvents cse ON cse.RegistrationId = reg.Id  
AND cse.Bye <> 1 AND cse.Withdrawn = 0 AND cse.PartnerId IS NOT NULL JOIN attribute.CompetitionEvents events on events.Id = cse.CompetitionEventId JOIN lookup.EventType et ON events.EventTypeId = et.Id
 LEFT JOIN entity.Person person ON cse.PartnerId = person.Id LEFT JOIN entity.Person partner ON reg.PersonId = partner.Id  WHERE comp.ID = 18220 ORDER BY et.EventDescription, person.LastName

which works EXCEPT it is not change the WHERE comp.ID = ##### portion. It is NOT changing it based on the entered data from the 'CompSelection' worksheet.

Code:

Sub RefreshCompClick()
 Dim CompId As String, Split1 As String, Split2 As String, Split3 As String, Split4 As String
 CompId = Range("B3").Value
 
 With ActiveWorkbook.Connections("CompSelection").OLEDBConnection
    .CommandText = "SELECT Description FROM entity.Competition WHERE Id = " + CompId
 End With
 
 With ActiveWorkbook.Connections("EventSummary").OLEDBConnection
    Split1 = "SELECT et.EventDescription, COUNT(*) 'CompetitorCount' " & _
        "FROM attribute.CompetitionSkatedEvents cse " & _
        "JOIN attribute.CompetitionRegistration reg ON cse.RegistrationId = reg.Id AND InvoiceNumber IS NOT NULL " & _
        "JOIN attribute.CompetitionEvents ceRef ON ceRef.Id = cse.CompetitionEventId "
    Split2 = "JOIN lookup.EventType et ON ceRef.EventTypeId = et.Id " & _
        "WHERE cse.CompetitionEventId IN ( SELECT ce.Id 'CompetitionEventId' " & _
        "  FROM entity.Competition comp " & _
        "  JOIN attribute.CompetitionEvents ce ON ce.CompetitionId = comp.Id " & _
        "  WHERE comp.ID = " + CompId + " ) AND " & _
        "  cse.Bye <> 1 AND cse.Placement IS NULL AND cse.Withdrawn = 0 " & _
        "GROUP BY et.EventDescription"
        
    .CommandText = Split1 + Split2
 End With

There are 5 worksheets it updates and I am adding a 6th one.
Just want:
1. LastName
2. FirstName
3. Member#
4. Gender (by female=1 then by male=2)
5. partner LastName

6. partner FirstName

Being very limited in VBA, I would appreciate anyone's assistance on correcting what's wrong.
Thank you for your help.

supportservice · #2 08-13-2015, 08:31 AM

Nevermind! I figured it out!