I have no idea you could do that with sql in vb code!
I put in CStr(Me.FindGroup) + ")
FindGroup is the combo box on GroupParam form. But I'm
getting an error message- Compile error: method or data
member not found. I'm not even sure what CStr is being
used for. Help says to use CStr to convert a number to a
string. Do I need to define it in the "Dim" section?
Here's what I have so far. I think I did the db section as
you suggested:
Private Sub EmailGroup_Click()
Dim rst As DAO.Recordset
Dim strTo As String
Dim strGroup As String
Dim strWhere As String
Dim sql As String
Dim db As Database
'Dim CStr As ?
'Open GroupParamEmail
DoCmd.OpenForm "GroupParamEmail", acNormal, , , ,
acDialog
strWhere = "[ContactID] = " & Nz([ContactID], 0)
strGroup = Nz(DLookup("qryGroups", "GroupName",
strWhere), 0)
sql = "SELECT Contacts.EmailName FROM GroupMember
INNER JOIN " + "Contacts ON GroupMember.ContactID =
Contacts.ContactID " + "WHERE (((GroupMember.GroupID) =" +
CStr(Me.FindGroup) + ") AND ((GroupMember.GMemberEnd) Is
Null));"
Set db = CurrentDb
Set rst = db.OpenRecordset(sql)
With rst
Do Until .EOF
strTo = strTo & ![EmailName] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo) - 1)
Set rst = Nothing
DoCmd.SendObject acSendNoObject, , , , , strTo, "Email
Group"
End Sub
Thanks, Sal
-----Original Message-----
In which case you just need to change your original code
to...
Dim rst As DAO.Recordset
Dim strTo As String
Dim sql As String
sql = "SELECT Contacts.EMailName FROM GroupMember INNER
JOIN " + _
"Contacts ON GroupMember.ContactID = Contacts.ContactID "
+ _
"WHERE (((GroupMember.GroupID)=" + CStr (Me.YourComboName)
+ _
") AND ((GroupMember.GMemberEnd) Is Null));"
Set rst = CurrentDb.OpenRecordset(sql)
Note... rather than using CurrentDB you're generally
better off doing... dim
db as database... set db=currentdb and then set
rst=db.openrecordset(sql)
With rst
Do Until .EOF
strTo = strTo & ![EmailName] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo) - 1)
Set rst = Nothing
DoCmd.SendObject acSendNoObject, , , , , strTo, "Email
Group "
....and that's providing that GroupID is the bound column
on your combo
(which it should be).
It's really just a case of building an SQL string that
gives you *any* list
of addresses.... and then replacing the key value in that
with a reference
to the control that you're using. You can drop a
breakpoint into the code
and copy the SQL variable out into a query window to see
what it is doing.
message
Hi. I set up GMemberStart and GMemberEnd (both date
fields) to specify the start and end dates in the Group
for the Contact. For example, a Group could have
started
01/05/05 but this particular Contact didn't start being
associated with the Group until 02/13/05. A real
example:
Board of Directors is an on-going group. Contacts
serve 2
years and then stop being on the Board. Rather than
setting up a new Group, we can just set the Contact's
GMemberEnd date and leave Group going. That way when I
want to send email to the Board, I tell the email code
(someday!) not to send email to the Contacts with a
value
in GMemberEnd.
GroupMemberID is the key of GroupMember table.
GroupMember is the "intermediary" table between Groups
and
Contacts.
Sample data: GroupMemberID, ContactID, GroupID,
GMemberStart and GMemberEnd
9, 95, 5, 01/01/02
10, 95, 6, 05/01/02
This means that ContactID = 95 is a memeber of 2 Groups
(Board of Directors, starting 01/01/02) and (Reading
piolot, starting 05/01/02). That also means that
ContactID = 95 has two GroupMemberIDs.
Did I put this together? Perhaps. The database has
evolved
(sort of) over a couple of years and is stuck together
with logic and spit. I do have a database designing
friend that helped in the beginning, but I give away
almost all credit for the good, and take ALL "credit"
for
the bad. Thanks, Sal
-----Original Message-----
OK. That looks good. It's certainly correct to have a
many-to-many
relationship from Contacts to Groups (although...
what's
going into the
GroupMember, GMemberStart and GMemberEnd fields in
GroupMember? Could you
just give me some sample data from that table. Did you
set the tables up by
the way?)
in
message
Demanding I can be ;-)
So, I suppose what you're saying is "code doesn't
just
happen". Rats!
I do appreciate your efforts to help me get this
working.
It's the "biggie" that will get my non-profit off
their
old system (Address Book- yikes!) and on to Access.
They
love their "groups" to segment the population.
Here's the process: I have a form (LabelsAndLists)
that
has a command button (EmailGroup) that kicks off the
emailing code. The code calls form GroupParamEmail
that
has an unbound combo box called FindGroup. Here's the
sql
for FindGroup: SELECT Groups.GroupID,
Groups.GroupName
FROM Groups
WHERE (((Groups.GroupEnd) Is Null));
Then magic happens! Somehow the code knows which
Group
the
user wants to email and which Contacts are in that
Group
and sends the email to them. I do see the problem.
It's
where "magic happens".
Here's the table structure
Contacts (my "main table", contains ContactID,
EmailName)
Groups (contains GroupID, GroupName, GroupStart,
GroupEnd)
GroupMember (contains GroupMember, ContactID,
GroupID,
GMemberStart, GMemberEnd)
I created qryGroups:
SELECT Contacts.ContactID, Contacts.EmailName,
Groups.GroupName
FROM Groups INNER JOIN (Contacts INNER JOIN
GroupMembers
ON Contacts.ContactID = GroupMembers.ContactID) ON
Groups.GroupID = GroupMembers.GroupID
WHERE (((GroupMembers.GMemberEnd) Is Null) AND
((Groups.GroupEnd) Is Null) AND
((Contacts.EmailName) Is
Not Null));
This gives me all Contacts in a Group (where both the
group itself and the Contact's involement in the
Group
are "active") that have an Email address.
Now here's the pitiful attempt. I thought maybe I
could
use (see below) strWhere and strGroup. But I still
don't
know what Set rst = CurrentDb.OpenRecordset (X) would
be.
Or how the user input from FindGroup interacts with
qryGroups- perhaps I would need FindGroup to equal
GroupName in strGroup. Here's the rest of the code:
Private Sub EmailGroup_Click()
Dim rst As DAO.Recordset
Dim strTo As String
Dim strGroup As String
strWhere = "[ContactID] = " & Nz ([ContactID], 0)
strGroup = Nz(DLookup ("qryGroups", "GroupName",
strWhere), 0)
Set rst = CurrentDb.OpenRecordset("SELECT
[EmailName]
FROM Contacts WHERE [EmailName] Is Not Null AND
something
about group members...")
With rst
Do Until .EOF
strTo = strTo & ![EmailName] & ";"
.MoveNext
Loop
.Close
End With
strTo = Left(strTo, Len(strTo) - 1)
Set rst = Nothing
DoCmd.SendObject acSendNoObject, , , , ,
strTo, "Group
Email Link"
End Sub
-----Original Message-----
I still don't get exactly what process you're
trying to
use. Is this right?
You click a button on a form which pops up the
GroupParamEmail form... on
that you choose the group you want to mail (in a
combo
that is also called
GroupParamEmail?)... and then, when you hit OK, you
want
the mail to be
sent?
If I have that wrong, then please let me know what
it
is
that you want to
happen in terms of picking options from forms etc
(feel
free to be
demanding, it can all be done)
Last thing... I don't see where you are using the
choice
you make from the
combo to specify the people you want to mail...
is 'People' a query that
includes a criteria reference to that control? If
not...
then how does that
work? How does 'Board of Directors' produce a list
of
addresses? (i.e.
what's the table structure that includes those
addresses?)
"SillySally" <
[email protected]>
wrote
in
message
Thanks for taking an interest in my question- it's
driving
me crazy.
I see that "parameter" is a bad description.
GroupParamEmail is simply an unbound combo box
field,
FindGroup, that allows an end-user to choose a
group. I
use a similar form to run mailing labels. For
example,
the end-user selects "Board of Directors" and the
report
creates mail labels for all People who are in
the "Board
of Directors" group.
So what I'm hoping to do with the email list is
loop
through all People with email addresses that also
belong
to the user specified group. Hope that
explanation
helps. I had found an article from ATTAC
Consulting
Group
titled "How to create your won input box forms and
pause
code for user input" which seems exactly what I
need.
It's long but I'll paste it in (see after ***) -
skip
it
if
too annoying ;-)
Thanks, Sal
***
In your application you may need to get
information
or a
selection from a user that an a standard Input Box
isn't
designed to handle.
As an example, you may want to offer the user
only a
selection of one of two choices. Since there's no
way to
restrict what the user can enter into an input
box,
an
input box won't work well.
To get around this limitation you can design a
form
that
acts as psudo input box, opening the form to get
input,
and then when closed, resuming your code.
Here's how:
In the declarations section of the module which
the
running function is in, and for which you need to
get
the
input from the user, dimension new variables which
will
to
hold the return values which the user selects or
enters
into your input form.The value of these variables
will
be "visible" to your running function after the
user
enters their selections. There's no need to make
these
variables global variables.
Add a new sub procedure to the same module as your
running
function. Your custom input box form will call
this
sub
procedure. The purpose of this sub procedure is
solely
to
accept the values from your input form as
parameters
and
to set the new module level variables equal to the
value
of the parameters.
Design your own "input box" form as a dialog box
form.
When a dialog box is opened, it will cause your
running
code to "pause" until the dialog is closed. (You
have to
specifically specify opening it in your running
function
as "acDialog".)
In the OnClose event of your input box form, now
add
a
call to the sub procedure you just wrote.
Specifying
as
parameter values, the values of the text boxes or
option
groups etc. from your form. This will send the
data
back
to your module when the dialog is closed.
You can also make your main code pause and wait
for
the
user's input and for non-dialog style forms by
using
code
like the following in you main procedure ( an "
_ "
means
the line continues on the next line show here):
DoCmd OpenForm "MyForm", acNormal
While SysCmd(acSysCmdGetObjectState, acForm, _
"My Form Name") = acObjStateOpen
DoEvents 'Do Nothing Wait for Closing
Wend
[Resume Code here]
You can also include a parameter in your sub
procedure
to
notify your running function if the user clicked a
cancel
button rather than an OK button to end your
process.
-----Original Message-----
You're close. What do you mean by the parameter
field
on
GroupParamEmail...
a parameter you have to enter when the form
opens?
I
don't quite get how
you're choosing to specify the people you want to
mail...
could you go into
a bit more detail please.
"SillySally"
