Full Version: Reply To All
UtterAccess Forums > Microsoft® Access > Access Forms
LenaWood
Good Monday Morning!
For too many reasons to explain I am trying to incorporate a message center into one of my databases. Currently we have only email to rely on and it seems that the email monster is alive and well and eating messages and therefore people are not getting notified of pending changes.
I have incorporated a way for the users to send a Reminder Message to individuals that they select from a listbox. I have included a reminder date so that individuals don't get the reminder message until a day selected by the user.
I want to include a "Reply To All" button on my form - that way when one person takes care of what needs to be done he/she can let everyone else know that it has been taken care of as well.
I know I can use a recordset to choose all of the people that have gotten the message, but not sure how to go about selecting those names in a listbox.
My idea is that they can click on a "Reply To All" button and a form (frmMessage) opens up and the listbox (lstMessageTo) would be populated (rows selected) based on who got the message in the first place (MessageTo holds the name of the person getting the message). If the message was sent to 15 people, then there are 15 records of this message in my table (tblMessages).
Thanks!
Lena
LenaWood
I guess I could change the rowsource for my listbox when I open the form so that only those names that the message went to would appear in the listbox and then run a code to select all the names.
I have searched the archives for a way to do this, but either I am not searching for the right thing or it hasn't been done before (I am probably not search for the right thing).
Anyone else have any suggestions?
Thanks!
Lena
Jack Cowley
Lena -

You can set the Row Source of your List Box with code like this:

Me.ListBox.RowSource = "MyQuery"

This code will select all the items in the ListBox and put the bound column into a string:

Dim i as Integer
Dim Answer As String

For i = 0 To ListBoxName.ListCount - 1
Me.ListBoxName.Selected(i) = True
Answer = Answer & ListBoxName.ItemData(i) & ","
Next i

Is this what you are after?

Jack

Edited by: Jack Cowley on Mon Apr 10 13:56:45 EDT 2006.
LenaWood
Jack, Yes....kinda
If I send a message to Carla, Steve and Matt...when I hit Reply all, I want the listbox to have Carla, Steve and Matt's names selected. BUT there are other people in the list that should be showing but not selected (Lena, Jack, Dave, and many others).
If I change the rowsource to a query that shows just those people that got the message (Carla, Steve and Matt), no other names will show up so I can't send the same message to other people that may need the information.
Lena
Jack Cowley
This code will highlight the selected items in the list box, but I don't know how you know who has had emails sent (Carla, Steve and Matt)
im i As Integer
For i = 0 To List0.ListCount - 1
If List0.ItemData(i) = x Then
Me.List0.Selected(i) = True
End If
Next i
In the above code the x is the value that represents Carla or Steve or Matt, so for the code above to work it needs to be inside another loop....
for j = 1 to whatever
if x = one of the names that have had a message sent Then
For i = 0 To List0.ListCount - 1
If List0.ItemData(i) = x Then
Me.List0.Selected(i) = True
End If
End If
Next j
I hope that makes sense and that I have it right!!!
Jack
NoahP
PMFJI..
ight use a subform configured to look like a listbox, and then use Conditional Formatting to 'select' the records that have been sent, instead of a listbox. That way you'd only need to change the RecordSource of the subform.
May be 'out in left field' though...
Noah
Jack Cowley
Noah -
et real! You are never out in left field, but aways heading for home plate because of a long fly ball deep to center field...
A good idea, as always!
Jack
LenaWood
I am so confused :-( I don't think I am explaining what I want to do correctly.
will try your suggestion Jack and see if I can make heads or tails out of it. If not, I will try again to explain what I am trying to do.
Noah - I am not sure what you are trying to suggest either. Guess it is because it is Monday and my brain hasn't caught up with me from the weekend.
Thanks to you all,
Lena
Jack Cowley
Lena -
This is what I understand that you want - You have a list of people that you have sent messages too, Carla, Steve and Matt. You want to open a form, or have a form that is open, with a list box of people. In that list box you want Carla, Steve and Matts names to be highlighted so you know that messages have been sent to those people. Is that what you are asking or has MY Monday brain missed what you want?
Jack
LenaWood
OK - Let me try to explain it - maybe I am confused at what I want to do.
I have a form (frmReminderMessage) where a user generates a Reminder Message. This form is unbound and contains several controls. It has a command button that generates records in my table (tblMessages). Here is the code:
CODE
    Dim db As Database
    Dim rec As Recordset
    Dim varSelected As Variant
    
    Set db = CurrentDb
    Set rec = db.OpenRecordset("tblMessages")
    
    
    '***** CHECK TO MAKE SURE THAT THERE IS A SELECTION IN *****'
    '***** THE LIST BOX OF WHO NEEDS TO BE REMINDED OF     *****'
    '***** AN EVENT HAPPENING IN THE FUTURE                *****'
    If Me.lstRemind.ItemsSelected.Count <= 0 Then
        MsgBox "Select a name"
        '***** GIVE FOCUS TO THE FIELD THAT NEEDS INFO     *****'
        '***** ENTERED INTO IT                             *****'
        Me.lstRemind.SetFocus
        Exit Sub
    '***** THE REMINDER MESSAGE MUST HAVE A SUBJECT SO     *****'
    '***** WE NEED TO MAKE SURE THE USER HAS ENTERED ONE   *****'
    ElseIf IsNull(Me.txtSubject) Then
        MsgBox "You must enter a subject"
        '***** GIVE FOCUS TO THE FIELD THAT NEEDS INFO     *****'
        '***** ENTERED INTO IT                             *****'
        Me.txtSubject.SetFocus
        Exit Sub
    '***** THE REMINDER MESSAGE MUST HAVE A MESSAGE SO     *****'
    '***** WE NEED TO MAKE SURE THE USER HAS ENTERED ONE   *****'
    ElseIf IsNull(Me.txtMessage) Then
        MsgBox "You need to enter a message in the message field"
        '***** GIVE FOCUS TO THE FIELD THAT NEEDS INFO     *****'
        '***** ENTERED INTO IT                             *****'
        Me.txtMessage.SetFocus
        Exit Sub
    ElseIf IsNull(Me.datReminderDate) Then
        MsgBox "You need to enter a a date you want the reminder to become active"
        '***** GIVE FOCUS TO THE FIELD THAT NEEDS INFO     *****'
        '***** ENTERED INTO IT                             *****'
        Me.datReminderDate.SetFocus
        Exit Sub
    End If
    
    For Each varSelected In Me!lstRemind.ItemsSelected
        '***** ADD A NEW RECORD                            *****'
        rec.AddNew
        '***** MAKE MESSAGE STARTED FIELD EQUAL DATE AND   *****'
        '***** TIME NOW                                    *****'
        rec!MessageStarted = Now()
        '***** MAKE MESSAGE FROM PERSON SIGNED INTO        *****'
        '***** DATABASE                                    *****'
        rec!messagefrom = Me.txtFrom
        '***** MAKE MESSAGE TO PERSON SELECTED IN LIST BOX *****'
        rec!messageto = Me!lstRemind.ItemData(varSelected)
        '***** MAKE SUBJECT OF MESSAGE BE WHAT WAS ENTERED *****'
        '***** IN TXTSUBJECT CONTROL                       *****'
        rec!MessageSubject = Me.txtSubject
        '***** MAKE MESSAGE TEXT BE WHAT WAS ENTERED IN    *****'
        '***** TXTMESSAGE CONTROL                          *****'
        rec!MessageMessage = Me.txtMessage
        '***** SET THE MESSAGE STATUS TO REMINDER          *****'
        rec!Messagestatus = "Reminder"
        '***** MAKE THE DATE THE MESSAGE IS ACTIVATED BE   *****'
        '***** THE WHAT WAS ENTERED IN DATREMINDERDATE     *****'
        '***** CONTROL
        rec!MessageReminder = Me.datReminderDate
        '***** SAVE THE RECORD THAT YOU JUST CREATED       *****'
        rec.Update
    '***** MOVE TO THE NEXT PERSON SELECTED IN THE LIST    *****'
    '***** BOX                                             *****'
    Next varSelected
    
    '***** GIVE THE USER A MESSAGE LETTING THEM KNOW THAT  *****'
    '***** THE MESSAGE WAS LOGGED AND WILL BE ACTIVATED ON *****'
    '***** THE DATE THEY ENTERED INTO THE DATREMINDERDATA  *****'
    '***** CONTROL                                         *****'
    MsgBox "The Reminder Message has been logged and will be activated on " & Me.datReminderDate & "."
    '***** CLOSE REMINDER MESSAGE FORM                     *****'
    DoCmd.Close acForm, "frmReminderMessage"
    '***** OPEN MAIN MENU FORM                             *****'
    DoCmd.OpenForm "frmMainMenu"
End Sub

Each time the database is opened, a query runs to see if there are any messages that need to be activated:
UPDATE tblMessages SET tblMessages.MessageSent = Now(), tblMessages.MessageStatus = "Not Read"
WHERE (((tblMessages.MessageStatus)="Reminder") AND ((tblMessages.MessageReminder)<=Now()));
If Jerry sends a message to Carla, Matt, and Steve saying that Joe Blow is changing jobs and therefore A, B, and C needs to be done, I want Carla, Matt or Steve to be able to click a button that says "Reply To All" and bring up a form (frmMessage). On this form will be a listbox (lstMessageTo) that will contain many names (to include Jerry, Matt, Steve and Carla). I need to loop through the messages in tblMessages and find out who got sent this message (There is a field called MessageStarted that will contain the date and time that will be unique to these messages and I will also use the subject field (MessageSubject) to compare to ensure it is the same message) and make sure their names (along with the sender) is selected in the listbox.
Oneed to show the other names as well so that the replyer can add additional people to those that get the message.
I am just not sure how to loop through a recordset and say, Select the name if it appears in the MessageTo or MessageFrom fields in the table tblMessages for any record that meets the criteria.
I did I make that clear as mud? haha
Thanks!
Lena
NoahP
I think you need two tables (at least) here. One for the messages, and one for the persons involved, along with a field to identify the recipient type. The messages table would be the parent of the child records. This more normalizes your structure and immediately removes the need to check two fields (from and to).
Jack Cowley
Lena -
You said, "I am just not sure how to loop through a recordset and say, Select the name if it appears in the MessageTo or MessageFrom fields in the table tblMessages for any record that meets the criteria." If you create a query based on this table can you get the data you want by putting Not Is Null in the appropriate field(s) along with the other 'filtering' that you need to do? In other words, with criteria and you filter the data in the table to show the information you want?
My thought is that if you can do that then you can build an SQL statement on the fly to get your data... Being a man I cannot multi-task so I have to start at square 1 then go to square 2... You get the picture...
Jack
LenaWood
Noah, I do have a table that holds the people and a table that holds the messages. I am not sure how I am going to get past the needing to check the two fields though. Although that won't solve my problem of populating a listbox, it will as you say, keep me from having to check two fields.
Back - I know how to get the records (Limiting or filtering them), but what I do with the information after that to make it select the required names in my listbox is where I am confused. I don't know how to make that work. Maybe I do know how I just don't remember I know - and if I remember right, I am too young not to remember. (I am so glad it is nearly time to go home!)
Thanks for you guys for listening and trying to get me down the right road without many detours. I have enough being the mother of a teenage girl haha.
Lena
Jack Cowley
Lena -
h you young people....
The code I suggested where you have a loop inside a loop is what you will need to highlight records in the list box. The first loop finds the first name then the second loop looks through the listbox and highlights the name. Then the outer loop moves to the next and and the inner loop goes through the list again and highlights the next name.
Now I have made it sound very easy, and it may not be too bad expecially if you have a filtered list of names to begin with and do not need to go through the entire table searching for each one.
Am I making a lick of sense? And lets keep an eye on Noah as he has forgotten more ways to skin the Access cat than I ever will!!!
Jack
Jack Cowley
Mr. Pruitt -
Is you are lurking I would like to ask you if you think my loop inside a loop is the way to go to highlight the selected names in the listbox?
Jack
NoahP
Yessir, if you go the listbox route that'd be how I'd do it.
on't mean to 'lurk' on ya Jack. Just busy and dropping in and out a lot with little time to dedicate at the moment...
LenaWood
This is what I am so far in my code.
Dim MySql As String

MySql = "SELECT DISTINCT UserName FROM qryJoinToAndFrom WHERE [MessageStarted] = '" & Forms!frmMessage!MessageStarted & "'"
Odon't get any errors from my SQL line...so that is a good start. This will give me a list of people I want to have selected in the listbox when the form is opened.
Now I am lost as to what I do with this information.
Thanks for your help!
Lena
LenaWood
I did this much:
!--c1-->
CODE
    Dim MySql As String
    Dim db As Database
    Dim rs As Recordset
    
    Set db = CurrentDb
    
    MySql = "SELECT DISTINCT UserName FROM qryJoinToAndFrom WHERE [MessageStarted] = #" & Forms!frmMessage!MessageStarted & "#"
    
    Debug.Print MySql
    
    Set rs = db.OpenRecordset(MySql)
    
    If Not rs.EOF Then
        rs.MoveFirst
    
        Do Until rs.EOF
            If rs!UserName = Me.lstMessageTo.Column(0) Then
                Me.lstMessageTo.Selected(0) = True
            End If
            rs.MoveNext
        Loop
    End If

It loops through the names from the recordset, but doesn't do anything with them. I know I am missing something but am really proud I got this far haha.
Lena
Jack Cowley
Noah -
guess I have to spell it out for you... I WANT YOU TO LURK! If you are lurking I know that when I stumble you will be there to save me from total embarrassment blush.gif !!! I always feel more secure when you are lurking, Noah, so lurk!!!
Thank you, Noah....
Jack
NoahP
Yessir. Mama told me to respect my elders....
Jack Cowley
Lena -
I am doing this VERY quickly as I have to leave in a few minutes for my job at the library, but this may help get you started.. I am sure it is wrong, but I believe the basic structure is correct... Hopefully our pal Noah is around and can save my bacon again...
Jack
CODE
rs.MoveFirst  
Do Until rs.EOF

        For i = 1 to lstMessageTo.ListCount -1
            If rs!UserName = Me.lstMessageTo.Column(0) Then
                Me.lstMessageTo.Selected(0) = True
            End If
         Next i
     rs.MoveNext
  Loop
End If
Jack Cowley
Noah -
You are a funny man and a true Southern Gentleman, which is mighty rare these days...
Now I am doubly glad you are lurking as I must dash off to my job at the library and I have posted some air code for Lena. It would not make me cross if you set it right as I just tossed it together and I know it is not really correct...
Thanks Mr. Pruitt and your pay increase, as a moderator here at UA, will be reflected in your next check...
Jack
NoahP
I can expect another doubling of my current salary then?
NoahP
CODE
Dim MySql As String    
Dim db As Database
Dim rs As Recordset
Dim I as Integer
et db = CurrentDb
MySql = "SELECT DISTINCT UserName FROM qryJoinToAndFrom WHERE [MessageStarted] = #" & Forms!frmMessage!MessageStarted & "#"
Set rs = db.OpenRecordset(MySql)
If Not rs.EOF and Not rs.BOF Then
  rs.MoveFirst
  Do While Not rs.EOF
    For i = 1 to lstMessageTo.ListCount -1
      If rs!UserName = Me.lstMessageTo.Column(0) Then
          Me.lstMessageTo.Selected(0) = True
          Exit For
      End If
    Next I
  rs.MoveNext
  Loop
End If
LenaWood
OK...I used your code Noah (which is really close to what I had gotten from Jack's code) but I changed the line:
e.lstMessageTo.Selected(0) = True
to
Me.lstMessageTo.Selected(I) = True
HAs the code stands now, if the first name is selected in the listbox and then the command button is pressed, the name below the name selected is selected and the first name was deselected.
Before I changed it, if I selected the second name in the list then pressed the command button, it would deselect the second name and highlight the first name in the listbox.
It is so close to working.
Lena
Aquadevel
Noah,
ack's gonna <share> his 4 1/2 cents per post with you.
Aqua
NoahP
Got in too big a hurry Lena, sorry.
ry:
If rs!UserName = Me.lstMessageTo.Column(0,I) Then
Me.lstMessageTo.Selected(I) = True
Exit For
End If
LenaWood
Now it is selecting a different name but still don't understand why it is selecting what it does.
I am attaching the database. It is the form called frmMessages that I am working on. If you have a minute and can look at it I would greatly appreciate it.
Lena
NoahP
You need to work on your structure Lena. You need at minimum, 4 tables here.
blPeople
PeopleID Autonumber PK
FirstName Text
MI Text
LastName Text
tblMessages
MsgID Autonumber PK
MsgDate Date/Time
MsgSubj Text
MsgText Memo
MsgActive Date/Time
tblRecipTypes
RecipTypeID Autonumber PK
RecipType Text (From, To, etc.)
tblMessageRecipients
MsgRecipID Autonumber PK
MsgID FK to tblMessages
PeopleID FK to tblPeople
RecipTypeID FK to tblRecipTypes
DateReceived Date/Time
You're storing the same data multiple times and are having to use a Union query to get From and To together.
I'd go along the lines of the attached.
LenaWood
Thank you so much Noah. I will have a look at what you have done and see if I can make something similar work for what I am doing.
ena
Jack Cowley
Lena -
pen your form frmMessages in design mode. Open the property sheet for the list box. Under the Other tab set the Multi Select property to Simple. Now when the code runs Dave, Jerry and Lena will be highlighted...
Is that what you are after?
Jack
LenaWood
Jack - yes. Noah gave me a sample of what I need to do....kinda. It will get me down the right road anyway.
oah - Thanks for all of your time and assistance!
Until the next question :-)
Lena
Jack Cowley
Lena -
He are glad to help and we are all fortunate that Noah is a member at UA. He has given all of us a lot of his time and expertise.
Until your next question....
Jack
NoahP
You are welcome Lena!
If you need clarification on anything I did, please let me know.
LenaWood
Actually Noah I have learned just enough that I can fiddle with what you did and try to make things work my way. I am making progress but will be back with any questions that I may have.
Thanks again!
Lena
LenaWood
Just wanted you to know that I got my working model of my "Almost Instant Messenger" working and working very well thanks to the help I got here.
How I have to incorporate it into my other database and see if I can keep it working.
Thanks again!
Lena
Jack Cowley
Lena -
And they said it couldn't be done... Glad to hear you have got your 'AIM' (Almost Instant Messenger) working. It is amazing what you can do with hours of backbreaking labor and help from Noah!!!
Well done!!!
Jack
LenaWood
Jack - it wasn't just Noah...you offer me support and words of kindness along with your words of wisdom. You really do keep me on track when things get tough. If it wasn't for YOU, I would still be very frustrated many questions back :-)
ena
Jack Cowley
Lena -
You are too kind! The important thing is that you have solved your problem and are a heroine... All you need as a cape...
Jack
LenaWood
Can I borrow yours? :-)
ena
Jack Cowley
Lena -
OL laugh.gif Don't I wish!!!
Jack
NoahP
Haven't you seen "The Incredibles"? NO CAPES! wink.gif
You gotta see the movie to get the reference)
LenaWood
The Incredibles have been on when I was in the room, but I didn't pay attention. My daughter tells me however it is because they could get caught in fans and etc.
think the Incredibles will be added to my list of movies I need to pay attention to :-)
Lena
LenaWood
I posted the database to the code archive HERE
ena
Jack Cowley
Thanks Lena!!! Your support of UA is sincerely appreciated!!
ack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.