Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    getting rid of duplicates in labels (2000 SP3)

    I am working on generating mailing labels from the same MultiSelect form I was working on for reports earlier in the week. The form allows you to select any set of groups of people and then print the listing of the members of that group.

    For the mailing labels, I want to remove duplicates which can be created if the same person is a member of more than one group. It seems to me that I need to generate the recordset of all the people in the selected groups then run a new SELECT DISTINCTROW query which will filter out the duplicates, then print the labels. I'm just not sure of the recipe I need to follow to accomplish this.

    I am working on the following assumptions: After selecting the list of groups to print labels for, the user clicks a command button. The code in the on click event loops through the list of groups using the same select case statements as for the lists of members that I was working on previously. After constructing the Where clause, a query is created in code to just have that group of people. From that query, a second one uses DISTINCTROW to pull out unique names and addresses. The labels are then generated.

    My questions are: Should these two queries that are created in code be created with the queryDef object (I haven't actually had to use this before) or some other way? What is the recordsource for the labels report if these queries are created in code? And what syntax should I use if the recordsource isn't a saved query? How do I send the SELECT DISTINCTROW info to the labels report?

    The underlying set of records that the command button calls on is the same saved query that the other report is generated from. Hopefully this makes sense.

    Peter

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: getting rid of duplicates in labels (2000 SP3)

    If you can construct a single Where-condition in code, you don't have to change the recordset of the report, you can pass the Where-condition as an argument to DoCmd.OpenReport the way you did in <post#=299854>post 299854</post#>.

    How you should create a single Where-condition to select the members of multiple groups depends on the structure of your tables.

    If you do need to change the record source of the report, you can create a complete SQL string in code and set the record source in the On Open to this string. Creating stored queries is also possible, but may cause problems in a multi-user environment. (And yes, that means creating a QueryDef object)

  3. #3
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getting rid of duplicates in labels (2000 SP3)

    I was originally hoping to just pass the strWhere to the report using DoCmd.OpenReport. Unfortunately, I need to filter the dataset by using the existing where clause on the field "Criteria" (e.g. WHERE Criteria = "selected groups") and it is from *this* set that I then need to generate the new SQL.

    I'm pretty sure I can do the SQL part in the on click event of the form. Can you give me the basic syntax to set the recordsource to the SQL string? Do I need to write the SQL to an unbound textbox on the form or can I just grab it out of the procedure?

    Finally, just to clarify in my mind, when you talk about the possibility of creating a stored query, you are talking about a fixed query created in the queries module, correct? It's one of those little terminology things that I never really paid attention to but would help to be clear now that I need it <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

    Peter

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: getting rid of duplicates in labels (2000 SP3)

    You can set the record source of a report in code in the On Open event. The record source can be
    <UL><LI>The name of an existing table.
    <LI>The name of an existing query. By existing query, or stored query, or saved query I mean a query listed in the Queries tab of the database window.
    <LI>An SQL string.[/list]You can construct the SQL string in the code behind a command button on a form. To make it available for use in the report, you can store the SQL string in a text box on the form, or in a global string variable.

    Example using a global variable. You have a form frmSelect with a command button cmdReport, and you want to open a report rptMailing.

    1. In a general module, declare a global variable:

    Public strSQL As String

    2. On Click procedure for cmdReport on the form:

    Private Sub cmdReport_Click()
    On Error GoTo ErrHandler

    strSQL = ... ' construct SQL string here
    DoCmd.OpenReport "rptMailing", acViewPreview
    Exit Sub

    ErrHandler:
    Select Case Err
    Case 2501 ' Report canceled
    ' No need to react
    Case Else
    MsgBox Err.Description, vbExclamation
    End Select
    End Sub

    3. On Open event handler for the report:

    Private Sub Report_Open(Cancel As Integer)
    On Error GoTo ErrHandler

    Me.RecordSource = strSQL
    Exit Sub

    ErrHandler:
    MsgBox Err.Description, vbExclamation
    End Sub

  5. #5
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getting rid of duplicates in labels (2000 SP3)

    Thanks for posting this. I actually got it to work with querydefs last night. The only thing I don't like about the way it is currently working is that I had to create stored queries and then delete them at the end. If the code crashes, I am left with stored queries. I suppose, though, I could just add in code to see if the queries exist and delete them before I recreate them again.

    I couldn't for the life of me make it work with temporary querydefs. It kept failing when the report opened as everything I tried to set Me.RecordSource with the temp querydefs kept not being able to find the recordset for qryTemp1. This way the report has its recordsource set to qryTemp2.

    Here is the code as it stands. Is this easily modified to use temporary query defs? Or would it be easier to modify it to use the global variable as you describe above?

    Private Sub Command22_Click()
    Dim aSelected() As Variant
    Dim varItem As Variant
    Dim strSlct As Variant
    Dim strWhere As Variant
    Dim intOpt As Integer
    Dim db As DAO.Database
    Dim recCmttee As DAO.Recordset
    Dim strLeft As String
    Dim recUnion As DAO.Recordset
    Dim strFinal As String
    Dim qdfTemp As DAO.QueryDef
    Dim recFinal As DAO.Recordset
    Dim qdfUnion As DAO.QueryDef
    Dim strNew As String


    Set db = CurrentDb()
    'Loop through the selected set of committee definitions and create a WHERE clause
    aSelected = mmp.SelectedItems
    Set recCmttee = db.OpenRecordset("tblCommittees")
    strWhere = ""
    For Each varItem In aSelected
    'Open a recordset of the items in the select box
    strSlct = "SELECT * FROM tblCommittees WHERE CommitteeName = '" & varItem & _
    "'"
    Set recCmttee = db.OpenRecordset(strSlct)
    intOpt = Nz(recCmttee("AgeCriteriaOptionGroup"))

    'Select Case statement to select right option group
    Select Case intOpt
    Case 1
    strWhere = strWhere & " OR Criteria = '" & recCmttee("CommitteeName") & "'"
    Case 2
    strWhere = strWhere & " OR Criteria = '" & recCmttee("SingleDelimiter") & "'"
    Case 3
    strWhere = strWhere & " OR Criteria BETWEEN '" & recCmttee( _
    "BetweenDelimiter") & "' AND '" & recCmttee("AndDelimiter") & "'"
    Case 4
    strWhere = strWhere & " OR Criteria > '" & recCmttee("GreaterThanDelimiter") & "'"
    Case 5
    strWhere = strWhere & " OR Criteria = '" & recCmttee("LessThanDelimiter") & "'"
    Case Else
    MsgBox "Help!"
    Exit Sub
    End Select
    Next varItem

    'Strip out the leading OR operator
    strLeft = Left(strWhere, 4)
    If strLeft = " OR " Then strWhere = Mid(strWhere, 5) Else strWhere = strWhere

    'Create a SELECT statement to get the complete set of records for the labels
    strFinal = "SELECT * FROM UnionMailingLabels WHERE " & strWhere
    With db
    Set qdfUnion = db.CreateQueryDef("qryTemp1", strFinal)
    Set recUnion = qdfUnion.OpenRecordset
    strNew = "SELECT DISTINCTROW Last(qryTemp1.IndName) AS Name, qryTemp1.Address1, qryTemp1.Address2 "
    strNew = strNew & "FROM qryTemp1 "
    strNew = strNew & "GROUP BY qryTemp1.Address1, qryTemp1.Address2;"

    'Filter out the duplicates and print the labels
    With db
    Set qdfTemp = db.CreateQueryDef("qryTemp2", strNew)
    Set recFinal = qdfTemp.OpenRecordset
    DoCmd.OpenReport "TestLabels", acViewPreview
    End With

    'Delete the queries you created
    .QueryDefs.Delete qdfTemp.Name
    .QueryDefs.Delete qdfUnion.Name
    .Close
    End With
    End Sub

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: getting rid of duplicates in labels (2000 SP3)

    You're opening a recordset, but what are you trying to do? In Access 2000, report's don't have an exposed recordset property, so is qryTemp2 the recordsource for your report? If not, it isn't going to work, or at least not as you might expect. I don't understand what you're doing with the recordset, but I haven't been following this thread or its predecessor closely and I may have missed an important detail.
    Charlotte

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: getting rid of duplicates in labels (2000 SP3)

    As Charlotte remarks, you create a query and open a recordset, then open a report. If the Record Source of the report has been set to qryTemp2, it will display the correct records, but the recordset has nothing to do with that.

    You can't use temporary querydefs for your purpose. I wouldn't delete the query immediately after opening the report - you'll probably get an error message that it is in use. Instead, change its SQL string immediately before opening the report. Alternatively, use the method described in my previous reply.

  8. #8
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: getting rid of duplicates in labels (2000 SP3)

    Thanks for the comments. My opening of the recordset recFinal had to do with an incomplete understanding of how queryDefs work. Your comment makes perfect sense in retrospect because the recordsource for the report is in fact qryTemp2. I will delete that line from the code. I will also spend some time with Han's solution tomorrow (Canadian Thanksgiving dinner today! <img src=/S/munch.gif border=0 alt=munch width=19 height=17> )

    One of the reasons I posted the code was to get this sort of feedback as this is new territory for me and all of my main resources for VBA don't give a lot of in depth discussion about how and when to use querydefs.

    With respect to Hans' most recent comment, I am not having any trouble with the deletion of the querydefs after the report's opening, but I will keep an eye out for that as well. I will look at his suggestions for just changing the SQL for qryTemp2.

    Peter

Posting Permissions

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