Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2009
    Posts
    6

    Unanswered: Display Query Results In Textbox; Comma Delimited Records

    Hello,

    I am working on an Access 2007 database that stores contact information. There is one table with all the contact information (name, address, phone ect) and a second table that has one column called "Groups". There is a series of groups here and each member of the Contacts table can belong to a set of groups which is connected through the lookup wizard.

    I have designed a query that finds members that belong to specified groups when queried by the user. The query returns a column titled "E-mail Address" where the emails that belong to these groups are listed.

    I want to extract this information from the "E-mail Address" column and output it to a text box where they are comma delimited.
    After doing some research on this, I am pretty sure that I have to create a recordset for this query and then somehow combine the fields. The problem is, I am not exactly sure how to create a recordset for this query. Still a beginner with Access.

    Here is some code that I have found mostly on the web. It is not outputting the email addresses into textbox_test like I want it to:

    Code:
    Private Sub group_list_email_button_Click()
    
    Dim var As Variant
    Dim strCriteria As String
      
    If group_email_listbox.ItemsSelected.Count = 0 Then
        MsgBox "Please select an item.", vbOKOnly, "Error"
    Exit Sub
      
    'Build a SQL statement using
    'selected items
    Else
        For Each var In group_email_listbox.ItemsSelected
            strCriteria = strCriteria & "(Contacts.Membership.Value)" & " = '" & group_email_listbox.ItemData(var) & "' Or "
        Next var
    End If
      
    strCriteria = Left(strCriteria, Len(strCriteria) - 4)
    
    Debug.Print strCriteria
    
    On Error Resume Next
    
    Dim dbs As Database
    Dim strSQL As String
    Dim strQueryGroups As String
    Dim qryDef As QueryDef
    
    'set variable values
    Set dbs = CurrentDb
    strQueryGroups = "Query Groups"
    
    'Delete old query first
    dbs.QueryDefs.Delete strQueryGroups
    
    'Notice below how we inserted the variable as a parameter value
    strSQL = "SELECT Contacts.[E-mail Address] FROM Groups INNER JOIN Contacts ON Groups.Groups = Contacts.Membership.Value WHERE " & strCriteria & ";"
    
    'Create query definition
    Set qryDef = dbs.CreateQueryDef(strQueryGroups, strSQL)
    
    Dim rst As DAO.Recordset
    Set rst = CurrentDb.OpenRecordset("Query Groups", dbOpenDynaset)
    
    With rst
      Do While Not .EOF
       textbox_test = textbox_test & rst.Fields("E-mail Address").Value & ","
        .MoveNext
      Loop
    End With
    
    If Right$(textbox_test, 1) = "," Then
      textbox_test = Left$(textbox_test, Len(textbox_test) - 1)
    End If
    
    End Sub
    Any help would be appreciated...
    David P

  2. #2
    Join Date
    Feb 2009
    Posts
    6

    Problem Solved

    I have finally solved my problem, taking an alternative route to using recordsets.

    For anybody who is interested; here is my solution to this problem:
    I have created a new listbox that displays the reuslts of my Email Address Query. This is a list of email addresses. The listbox is updated when this button is pressed and then all of the items in the listbox are selected. Once they are selected, the fields in this listbox are combined with a comma delimiter. This is then displayed in a textbox.

    Code:
    Private Sub group_list_email_button_Click()
    
    Dim var As Variant
    Dim strCriteria As String
      
    If group_email_listbox.ItemsSelected.Count = 0 Then
        MsgBox "Please select an item.", vbOKOnly, "Error"
    Exit Sub
      
    'Build a SQL statement using selected items
    Else
        For Each var In group_email_listbox.ItemsSelected
            strCriteria = strCriteria & "(Contacts.Membership.Value)" & " = '" & group_email_listbox.ItemData(var) & "' Or "
        Next var
    End If
      
    strCriteria = Left(strCriteria, Len(strCriteria) - 4)
    
    Debug.Print strCriteria
    
    On Error Resume Next
    
    Dim dbs As Database
    Dim strSQL As String
    Dim strQueryGroups As String
    Dim qryDef As QueryDef
    
    'set variable values
    Set dbs = CurrentDb
    strQueryGroups = "Query Groups"
    
    'Delete old query first
    dbs.QueryDefs.Delete strQueryGroups
    
    'Notice below how we inserted the variable as a parameter value
    strSQL = "SELECT Contacts.[E-mail Address] FROM Groups INNER JOIN Contacts ON Groups.Groups = Contacts.Membership.Value WHERE " & strCriteria & ";"
    
    'Create query definition
    Set qryDef = dbs.CreateQueryDef(strQueryGroups, strSQL)
    
    'update email listbox
    Me!email_listbox.RowSource = "Query Groups"
    
    'all the items in email_listbox are selected so that these items are included in the mailing list
    Dim intCounter As Integer ' create a loop counter
        
    For intCounter = 0 To email_listbox.ListCount
    email_listbox.Selected(intCounter) = True
    Next intCounter
    
    'a mailing list is generated for all of the items in email_listbox
    'this string is displayed in mailing_list_textbox
    Dim varItem As Variant
    Dim emName As String
    
    For Each varItem In Me!email_listbox.ItemsSelected
    emName = emName & Me!email_listbox.Column(0, varItem) & ", "
    Next varItem
    
    'remove the extra comma at the end
    emName = Left$(emName, Len(emName) - 2)
    
    mailing_list_textbox = emName
    
    
    End Sub

Posting Permissions

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