Subform Record Emails by Sub-Subform records? 1

[misscrf]

I have a form where a user can create an event. In the subform they can add invites. The invites are looked up from a contacts list. That contacts list contains email addresses.

This gets more interesting as for each invite, one or more people can be selected as inviting them. The invites subform is set up as continuous with a button to launch a pop-up form. That pop-up form is a continous form with a drop down to pick from a list of possible people who would invite someone to an event.

I am being asked to create a way for a person to go to an event and grab the emails for all the contacts being invited to that event.

Here is the catch - The user wants to show them by the person or people inviting them.

so lets say we have 5 people that might invite a contact to an event Person A, B, C, D, and E.
Contact 1,2,5, 8, 9 and 10 were invited by A and B.
Contact 3 and 4 were invited by C.
Contact 6,11,13 were invited by A.
Contact 7 and 12 were invited by A D and E.

I need either 4 results of email addresses or a datasheet view that will contatenate the combo of people inviting contacts.

(something like this)
Invited By Contactemail
A, B 1@address.com
A, B 2@address.com
A 3@address.com
etc.

I am concerned about trying to create that output because it seems like I will need code to do the looping.

The user wants to be able to grab (copy - for putting into an email) all the emails for all the invites being invited by one combo of people and send an email from those people to those invites they are inviting. Make sense?

If anyone has ideas of how to do this, I am all ears!!!!




misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Check out faq701-4233.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Ok, so I set this up in a module:

Function Concatenate(pstrSQL As String, Optional pstrDelim As String = ", ", Optional pstrLastDelim As String = "") As Variant
'   Created by Duane Hookom, 2003
'   this code may be included in any application/mdb providing
'   this statement is left intact
'   example
'   tblFamily with FamID as numeric primary key
'   tblFamMem with FamID, FirstName, DOB,...
'   return a comma separated list of FirstNames
'   for a FamID
'    John, Mary, Susan
'   ======= in a Query =========================
'   SELECT FamID,
'   Concatenate("SELECT FirstName FROM tblFamMem
'    WHERE FamID =" & [FamID]) as FirstNames
'   FROM tblFamily
'   ============================================
'   to get a return like Duane, Laura, Jake, and Chelsey
'   ======= in a Query =========================
'   SELECT FamID,
'   Concatenate("SELECT FirstName FROM tblFamMem
'    WHERE FamID =" & [FamID], ",",", and ") as FirstNames
'   FROM tblFamily
'   ============================================
'   If FamID is a string rather than numeric,
'    it will need to be delimited with quotes
'   ======= in a Query =========================
'   SELECT FamID,
'   Concatenate("SELECT FirstName FROM tblFamMem
'    WHERE FamID =""" & [FamID] & """", ",",", and ") as FirstNames
'   FROM tblFamily
'   ============================================
'======For DAO uncomment next 4 lines=======
'====== comment out ADO below =======
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)


'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
'Dim rs As New ADODB.Recordset
'length before last concatenation
Dim intLenB4Last As Integer
'rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

Dim strConcat As String 'build return string
    With rs
        If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                    intLenB4Last = Len(strConcat)
                    strConcat = strConcat & _
            .Fields(0) & pstrDelim
            .MoveNext
      Loop
    End If
  .Close
  End With
    Set rs = Nothing
'====== uncomment next line for DAO ========
    Set db = Nothing
    
    If Len(strConcat) > 0 Then
    strConcat = Left(strConcat, _
    Len(strConcat) - Len(pstrDelim))
        If Len(pstrLastDelim) > 0 Then
            strConcat = Left(strConcat, _
      intLenB4Last - Len(pstrDelim) - 1) _
        & pstrLastDelim & Mid(strConcat, intLenB4Last + 1)
    End If
    End If
    If Len(strConcat) > 0 Then
    Concatenate = strConcat
    Else
    Concatenate = Null
    End If
End Function

You will see that all the DAO stuff is uncommented.

I then have this in my query:

SELECT Concatenate("SELECT FKRep FROM tblEventInvOBO    WHERE FKEventID =" & [PKEventInviteID],",",", and ") AS FKRep, tblContact.txtEmail
FROM ((tblEventInvite LEFT JOIN tblContact ON tblEventInvite.FKContact = tblContact.PKContactdID) LEFT JOIN tblEventInvOBO ON tblEventInvite.PKEventInviteID = tblEventInvOBO.FKEventInvite) LEFT JOIN tblRep ON tblEventInvOBO.FKRep = tblRep.PKRepID
WHERE (((tblEventInvite.FKEvent)=[Forms]![frmEvent]![PKEventID]));

when I try to run the query, I get an error:

Microsoft Visual Basic
Run-time error '3601':

Too few parameters. Expected 1.

------------------

When I hit Debug, it hightlights this from the code:

Set rs = db.OpenRecordset(pstrSQL)

until I comment it out, It will just keep bugging out. I can't stop the code from running.

Once I comment it out, it hightlights this:

With rs

from this section of the code

Dim strConcat As String 'build return string
    With rs
        If Not .EOF Then
            .MoveFirst
            Do While Not .EOF
                    intLenB4Last = Len(strConcat)
                    strConcat = strConcat & _
            .Fields(0) & pstrDelim
            .MoveNext
      Loop
    End If

Again, until I comment all that out, the code won't stop and just keeps bugging out.

any idea what I messed up here?

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[PHV]

I'd replace this:
[tt]WHERE FKEventID =" & [PKEventInviteID],[/tt]
with this:
[tt]WHERE FKEventID='" & [PKEventInviteID] & "'",[/tt]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[misscrf]

Thank you for your help. Now I get...
Microsoft Visual Basic

Run-time error '3601':

Too few parameters. Expected 2.

-----

(before it was 1.)

Debug still goes to here:
Set rs = db.OpenRecordset(pstrSQL)

and then the loop.

anything else I might have wrong? It seems to not like the set rs = db.OpenRecordset(pstrSQL)

When or where did we set what pstrSQL is? I am shooting in the dark, because I understand some of this but not all of it.



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

Open a blank query and view its SQL. Paste in:

SELECT FKRep FROM tblEventInvOBO    WHERE FKEventID =[Enter and Event Inv OBO];

You can substitute a real FKEventID number and test the query.

You can also attempt to open the debug window and type in:

? Concatenate("SELECT FKRep FROM tblEventInvOBO    WHERE FKEventID = <type a value here>" ,",",", and ")

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

I'm sorry I am not understanding what you want me to do. I put the sql into the debug.print I don't see anything wrong.

I can run the query that I have with or without giving the id directly (I keep the form open in the background). I get results for all fields like I should. It is just the concatenate field/formula. It is blank. There is nothing in that column. The fields it is calling has stuff. I see that because while in testing, I return those to see them.

I don't see how I can get this formula to give me a result. :-(

I appreciate you working with me on this.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

I realized that I have a newer version of the query that I am working with, than the last one posted on here. This is the one that I am testing right now:

SELECT 
Concatenate("SELECT PKRepID  
FROM tblRep 
JOIN tblEventInvOBO ON tblEventInvOBO.FKRep = tblRep.PKRepID 
JOIN tblEventInvite on tblEventInvite.PKEventInviteID = FKEventInvite WHERE FKEventInvite =" & [PKEventInviteID]) AS Reps, 
tblRep.PKRepID, 
[txtRepAbbrev] & IIf(IsNull([txtRepFull]),"",": " & [txtRepFull]) AS Rep, 
tblContact.txtEmail
FROM ((tblEventInvite 
LEFT JOIN tblContact ON tblEventInvite.FKContact=tblContact.PKContactdID) 
LEFT JOIN tblEventInvOBO ON tblEventInvite.PKEventInviteID=tblEventInvOBO.FKEventInvite) 
LEFT JOIN tblRep ON tblEventInvOBO.FKRep=tblRep.PKRepID
GROUP BY 
Concatenate("SELECT PKRepID  FROM tblRep JOIN tblEventInvOBO ON tblEventInvOBO.FKRep = tblRep.PKRepID join tblEventInvite on tblEventInvite.PKEventInviteID = FKEventInvite WHERE FKEventInvite =" & [PKEventInviteID]), 
tblRep.PKRepID, [txtRepAbbrev] & IIf(IsNull([txtRepFull]),"",": " & [txtRepFull]), 
tblContact.txtEmail, 
tblEventInvite.FKEvent
HAVING (((tblEventInvite.FKEvent)=Forms!frmEvent!PKEventID));

I get results, but the concatenate column has nothing. Any idea what I am doing wrong? I really love this solution I just can't seem to figure it out.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I would avoid using the joins inside the Concatenate(). It should work but is a little more difficult to maintain. Try create a new saved query with the SQL of:

SELECT PKRepID, FKEventInvite
FROM tblRep 
JOIN tblEventInvOBO ON tblEventInvOBO.FKRep = tblRep.PKRepID 
JOIN tblEventInvite on tblEventInvite.PKEventInviteID = FKEventInvite

Make sure this query works and then try:

Concatenate("SELECT PKRepID  
FROM [YourNewQueryName] WHERE FKEventInvite =" & [PKEventInviteID]) AS Reps

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

ohh!!! Thats what I am missing. I will try that and get back to you on the results!

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

By dhookom, I think we've got it!!! You rock. Thank you so much for your help!

misscrf

It is never too late to become what you could have been ~ George Eliot