Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2012
    Posts
    7

    Unanswered: Need to save only VISIBLE records to a string

    Ok, so here's my scenario. Our hospital needs to send emails out to our physicians personal emails addresses on a semi regular basis. IT won't let us add the physicians personal email addresses as an alternate email address to their outlook contact card.

    I'm tasked with coming up with an easy way for us to keep a database of those personal emails addresses, and also make it easy for people to update the list, and then generate a recipient list based off queries they made against the contact table. For instance, we may just want to send an email to all Anesthesiologists, or Just Active Medicine Staff.

    I've setup a form that allows the end user to select the types of physicians they want an email to go to based off their specialty or service. A subform displaying the filtered contact table then shows the users that are selected.

    I have code that will generate a list of email addresses for recipients, but right now, its adding ALL the personal email addresses for our docs. I need it to add JUST the visible email addresses in the filtered table on the subform, but I'm now quite sure how to do that.

    Here is the code I'm using to generate the recipient list and output it to a text box. Does anyone know how I can get it to build that list based off of JUST the rows that are visible within the subform?

    Dim MyDB As DAO.Database
    Dim rstEAddr As DAO.Recordset
    Dim strBuild As String
    Set MyDB = CurrentDb
    Set rstEAddr = MyDB.OpenRecordset("Table.Contacts", dbOpenForwardOnly)

    With rstEAddr
    Do While Not .EOF
    If ![EmailAddress] <> "" Then
    strBuild = strBuild & ![EmailAddress] & ";"
    End If
    .MoveNext
    Loop
    End With

    rstEAddr.Close
    Set rstEAddr = Nothing

    Concatenate = Left$(strBuild, Len(strBuild) - 1)

    txtProgress.Value = strBuild

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what I'd do is stuff the selected emails into a control, say a list or combo box
    have a button on the form that sent the email
    then extract the rows from the combo/list box

    Mind you I'd also want to ask yoru IT depeartment why they cannot support something that the business actually needs. becuase all they are doing is forcing a business unit to do a workaround that by passes what ever reason they have in the first place not to store the email address in outlook..
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2012
    Posts
    7
    But how would I go about getting JUST the visible, filtered emails in the subform put into a control?

    Thats pretty much the problem that I have now...I'm putting the email addresses into a textbox control, but its dumping in ALL the email addresses in my contact table, not just the visible email addresses in the subform contact table.

    As for our IT department, if I relentlessly bombard them with logic and reason, they may decide to change their mind in 6 months to a year, but unfortunately I don't have the time or the patience. I think the main reason is that these emails are confidential, and we don't want just anyone with outlook access to be able to see the personal email addresses.

    Just a thought though, in my code, I'm setting the database with this line:

    Set rstEAddr = MyDB.OpenRecordset("Table.Contacts", dbOpenForwardOnly)

    Which now that I read it, seems like its using the overall Contacts table, but I need it to use the filtered table displayed in the Subform, but trying to use Subform.Table.Contacts doesn't seem to work.

  4. #4
    Join Date
    Mar 2012
    Posts
    7
    Ok, I figured it out, I modifed this line of code:

    If ![EmailAddress] <> "" Then

    To this:

    If ![EmailAddress] <> "" And ![Specialty] = strCriteriaSP Then

    and made my StrCriteriaSP variable global and now the contact list is generated using BOTH of those criteria.

    Now the only problem I have is how to get Access to recognize 2 different Specialty Filters. For Example, I have a multiselect combo box, and a user could select the Anesthesiology Section, and the Cardiology Section, but right now that makes strCriteriaSP = "Anesthesiology, Cardiology" which means that a provider would have to have BOTH of those specialties to match the filter.

    I want it to use or Logic, so that users with either the specialty of Anesthesiology, OR Cardiology will show up on the filtered list...

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try:
    Code:
    strCriteriaSP IN ( "Anesthesiology", "Cardiology")
    Have a nice day!

  6. #6
    Join Date
    Mar 2012
    Posts
    7
    Thanks for the reply! I just have one question. This is the code I'm using to loop through each VarItem selected in a combobox and add it to a string:

    For Each varItem In List55.ItemsSelected
    strCriteriaSP = strCriteriaSP & " AND " & List55.ItemData(varItem) & ""
    Next varItem

    How would I get that to work with the code you provided? Or would I leave that code the same but use the IN command where it compares strCriteriaSP to a field?

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It should be:
    Code:
    For Each varItem In List55.ItemsSelected
        IF Len(strCriteriaSP) > 0 Then strCriteriaSP =strCriteriaSP & ", "
        strCriteriaSP = strCriteriaSP & "'" & List55.ItemData(varItem) & "'"
    Next varItem
    strCriteriaSP = "[Specialty] IN ( " & strCriteriaSP & " )"
    Have a nice day!

  8. #8
    Join Date
    Mar 2012
    Posts
    7
    ***EDIT*** I got it working!, see the bottom of the message for the code I was missing:

    Thanks so much for the tip! I tried it out and its not quite working yet, but its got to be something small.

    Here's all the code I have in the command button to apply the filter:
    -----------------------------------------------------------
    Dim varItem As Variant
    Dim strSQL As String

    For Each varItem In List55.ItemsSelected
    If Len(strCriteriaSP) > 0 Then strCriteriaSP = strCriteriaSP & ", "
    strCriteriaSP = strCriteriaSP & "'" & List55.ItemData(varItem) & "'"
    Next varItem

    txtProgress.Value = strCriteriaSP
    SubFormContacts.Form.Filter = strCriteriaSP = "[Specialty] IN ( " & strCriteriaSP & " )"
    Me.SubFormContacts.Form.FilterOn = True

    Me.SubFormContacts.Form.Refresh

    End Sub
    -------------------------------------------


    I made a mistake and modified the one line so that it read:
    SubFormContacts.Form.Filter = strCriteriaSP = "[Specialty] IN ( " & strCriteriaSP & " )"

    What I needed to do was keep the original line you posted:
    strCriteriaSP = "[Specialty] IN ( " & strCriteriaSP & " )"

    and then add the line
    SubFormContacts.Form.Filter = strCriteriaSP
    Last edited by DrFistington; 10-21-13 at 14:35.

  9. #9
    Join Date
    Mar 2012
    Posts
    7
    Lol, I'm close enough to taste it, but now I've got an issue that has to do with the original reason I made this post.

    The contact list in the subform is filtering correctly now even with multiple items selected from a multiselect combo list, BUT I was never able to find a way to basically select only the filtered items displayed in the subform and output them to a text box.

    As a workaround, in the code I added to a command button that causes the recipient list to output to a text box I used the following code:

    ---------------------------------------------------------
    Private Sub Command59_Click()
    Dim MyDB As DAO.Database
    Dim rstEAddr As DAO.Recordset
    Dim strBuild As String
    Set MyDB = CurrentDb
    Set rstEAddr = MyDB.OpenRecordset("Table.Contacts", dbOpenForwardOnly)
    With rstEAddr
    Do While Not .EOF
    If ![EmailAddress] <> "" And ![Specialty] = strCriteriaSP Then
    strBuild = strBuild & ![EmailAddress] & ";"
    End If


    .MoveNext
    Loop
    End With

    rstEAddr.Close
    Set rstEAddr = Nothing

    Concatenate = Left$(strBuild, Len(strBuild) - 1)

    txtProgress.Value = strBuild
    End Sub
    -------------------------------------------------

    I've underlined the code that says to output the emails to a string, separated with a semi-colon, if the email address is not empty, and the specialty matches the variable that was created in the Apply filter button. But I think this has the same problem, where its not looking for rows where the specialty field matches 1 item OR another.

    Is there an easy way to just dump the [Email Address] fields of only the visible records in the subform into a text box?

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What do you get when, after setting the filter for the subform, you execute:
    Code:
    Debug.print Me.SubFormContacts.Form.Filter
    Have a nice day!

  11. #11
    Join Date
    Mar 2012
    Posts
    7
    I put that line right after:
    strCriteriaSP = "[Specialty] IN ( " & strCriteriaSP & " )"
    SubFormContacts.Form.Filter = strCriteriaSP

    And when I had Anesthesiology and Cardiology selected, the following was output to the debug window:

    [Specialty] IN ( 'Anesthesiology Section', 'Cardiology Section' )

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •