Mailing Labels

[Malcolm Webb]

I need to produce mailing labels every week for staff, but not always for
the same people. I have found a procedure using a filter form which
allows individual names to be selected from a list box which are then
applied to a report for printing.

I have an Employees Table filtered via a query to only show current staff.

My FilterForm is tied to this query and the list box on the filter form
shows surname and forename. I can choose any number of names I require
then click an ApplyFilter command button and my chosen names appear on the
Label Report. Just waht I want.

BUT -- next time I open the filter form there is a name missing. The top
name is missing and when I check the employees table the surname has been
deleted, although all the other information is there. I can easily put it
back. But next time it is deleted again. And if I don't put the first
surname back the next one is deleted.

Can anyone see anything amiss with the following code which would cause
the top surname in the list to be deleted?

Many thanks.

Malcolm Webb
Private Sub btnApplyFilter_Click()
Dim varItem As Variant
Dim strSurname As String
Dim strFilter As String

' Build criteria string from lstSurname listbox
For Each varItem In Me.lstSurname.ItemsSelected
strSurname = strSurname & ",'" & Me.lstSurname.ItemData(varItem) _
& "'"
Next varItem
If Len(strSurname) = 0 Then
strSurname = "Like '*'"
Else
strSurname = Right(strSurname, Len(strSurname) - 1)
strSurname = "IN(" & strSurname & ")"
End If

' Build filter string
strFilter = "[Surname:] " & strSurname

' Open the mailing label report and apply the filter.
DoCmd.OpenReport "LabelsEmployeeAddress", acViewPreview
With Reports![LabelsEmployeeAddress]
.Filter = strFilter
.FilterOn = True
End With
End Sub
Malcolm Webb

[Wayne Morgan]

I see nothing in the code that would delete the first surname in the
listing. However, there are a couple of things you may want to look at.

1) Surnames may have apostrophes in the (i.e. O'Hare). Your code will fail
if that is the case because you are using apostrophes as the delimiter. Try
the following adjustment:

strSurname = strSurname & ",""" & Me.lstSurname.ItemData(varItem) _
& """"

This changed each apostrophe to 2 double quotes. Since double quotes are
string delimiters also, you have to double them up to tell VBA that you
really mean for the double quote to be part of the string instead of a
delimiter. You may actually have to concatenate them in as double quotes if
the IN statement fails with the above. This would be for the same reason as
mentioned above for the apostrophes. The doubled up double quotes will put a
single double quote in the string as part of the string, this may cause a
problem with the IN statement. If so, they will need to be placed into the
string as doubled up double quotes. This would require replacing each
apostrophe with 4 double quotes instead of 2.

2) What if two people have the same surname? It would be better to use a
PersonID field or some other field that uniquely identifies the person.
--
Wayne Morgan
MS Access MVP
"Malcolm Webb" <mf********@yahoo.co.uk> wrote in message
news:me************************@mfwebb.compulink.c o.uk...

I need to produce mailing labels every week for staff, but not always for
the same people. I have found a procedure using a filter form which
allows individual names to be selected from a list box which are then
applied to a report for printing.

I have an Employees Table filtered via a query to only show current staff.

My FilterForm is tied to this query and the list box on the filter form
shows surname and forename. I can choose any number of names I require
then click an ApplyFilter command button and my chosen names appear on the
Label Report. Just waht I want.

BUT -- next time I open the filter form there is a name missing. The top
name is missing and when I check the employees table the surname has been
deleted, although all the other information is there. I can easily put it
back. But next time it is deleted again. And if I don't put the first
surname back the next one is deleted.

Can anyone see anything amiss with the following code which would cause
the top surname in the list to be deleted?

Many thanks.

Malcolm Webb
Private Sub btnApplyFilter_Click()
Dim varItem As Variant
Dim strSurname As String
Dim strFilter As String

' Build criteria string from lstSurname listbox
For Each varItem In Me.lstSurname.ItemsSelected
strSurname = strSurname & ",'" & Me.lstSurname.ItemData(varItem) _
& "'"
Next varItem
If Len(strSurname) = 0 Then
strSurname = "Like '*'"
Else
strSurname = Right(strSurname, Len(strSurname) - 1)
strSurname = "IN(" & strSurname & ")"
End If

' Build filter string
strFilter = "[Surname:] " & strSurname

' Open the mailing label report and apply the filter.
DoCmd.OpenReport "LabelsEmployeeAddress", acViewPreview
With Reports![LabelsEmployeeAddress]
.Filter = strFilter
.FilterOn = True
End With
End Sub
Malcolm Webb