getting rid of duplicates in labels (2000 SP3)

Topic

New Reply

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

Reply | Quote

Replies

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.

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)

Reply | Quote

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

Peter

Reply | Quote

You can set the record source of a report in code in the On Open event. The record source can be

• The name of an existing table.
• 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.
• 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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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! )

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

Reply | Quote

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! )

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

You can set the record source of a report in code in the On Open event. The record source can be

• The name of an existing table.
• 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.
• 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

Reply | Quote

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

Peter

Reply | Quote

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.

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)

Reply | Quote