apostrophe in record

[KAW]

I inherited a database and figured out that the following code will not work
if there is an apostrophe in the record. Any suggestions?

Private Sub Combo55_AfterUpdate()
On Error GoTo Err_Combo55_AfterUpdate
Dim rs As Object

If IsNull(Combo55) = False Then
DoCmd.GoToRecord , , acFirst
Do
If Site = True Then Call AddContact
DoCmd.GoToRecord , , acNext
Loop
End If
Me.Combo55 = ""

Exit_Combo55_AfterUpdate:
Exit Sub

Err_Combo55_AfterUpdate:
Me.Combo55 = ""
Resume Exit_Combo55_AfterUpdate

End Sub


Thanks!

 

[BruceM]

What does it do if there is no apostrophe? What is AddContact? An error
because of an apostrophe in the record can be due to an apostrophe in a
name. I suspect AddContact has something like DLookup in a format something
like:
DLookup("[LastName]","[SomeTable]","[LastName] = ' " & Me.[LastName] & " '
")

I have added spaces between the apostrophes and quotes for clarity. In the
code there would be no spaces. One solution is to replace the apostrophes
in the DLookup with a pair of quote marks:
DLookup("[LastName]","[SomeTable]","[LastName] = " " " & Me.[LastName] & " "
" ")

This will not work if there are quote marks in the field, but that is less
likely than apostrophes in a name field, which is my guess based on the
user-created sub or function named AddContact.

I'm just guessing with little to go on. It could be another function than
DLookup, or a SQL string, but I expect the problem is in AddContact.

 

[Steve Schapel]

KAW,

I am not sure what won't work if there is an apostrophe. I can't see
anything in this code that relates to that.

I notice you have declared a Object named rs which is not actually used
in the code, so not sure what that is about.

And it is irregular to use Me.Combo55 = "". Are you sure you want it to
be a zero-length string ("")? Why? Normally we would expect to see:
Me.Combo55 = Null

Anyway, can you explain a bit more about what you are concerned about?
Or more details of what the code is actually trying to do?

 

[KAW]

Oops, I tried to simplify and I think I simplified the problem out of the
code. I think the problem is with AddCompany in the following code:

Private Sub Combo55_AfterUpdate()
On Error GoTo Err_Combo55_AfterUpdate
Dim rs As Object

If IsNull(Combo55) = False Then
If Combo55 <> "Town Committee" Then
Set rs = Me.Recordset.Clone
rs.FindFirst "[Company Sort] = '" & Me![Combo55] & "'"
Me.Bookmark = rs.Bookmark
Call AddCompany
Else
DoCmd.GoToRecord , , acFirst
Do
If Site = True Then Call AddContact
DoCmd.GoToRecord , , acNext
Loop
End If
End If
Me.Combo55 = ""

Exit_Combo55_AfterUpdate:
Exit Sub

Err_Combo55_AfterUpdate:
Me.Combo55 = ""
Resume Exit_Combo55_AfterUpdate

End Sub


Here's the AddCompany code:

Private Sub AddCompany()
On Error GoTo Err_AddCompany

Dim stDocName As String
stDocName = "rptGLResultTable"

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("tblAddressMatch", dbOpenDynaset)

For i = 1 To Me!NumOfLabels
rs.AddNew
rs!MatchComID = Me![MatchComID]
rs![COMPANIES TABLE_Company Name] = Me![COMPANIES TABLE_Company Name]
rs![Company Sort] = Me![Company Sort]
rs!Address1 = Me!Address1
rs!Address2 = Me!Address2
rs!City = Me!City
rs!State = Me!State
rs!Zip = Me!Zip
rs.Update
Next i

Me!NumOfLabels = 1
LABELS_Subform.Requery

Exit_AddCompany:
Exit Sub

Err_AddCompany:
MsgBox Err.Description
Resume Exit_AddCompany

End Sub

Combo55 is on a form that merges with Word to create mailing labels. A
company name is selected in Combo55 and the contact info is added to a
subform. Mailing labels are created for the companies listed in the subform
using the OnClick procedure of a command button. If Town Committee is
selected, all town members are listed with their contact info and a label is
made for each member. This is the only selection when multiple contacts are
listed with one selection on Combo55 (that is why it has special code).
Otherwise, the company that is selected in Combo55 will be listed in a
subform and a mailing label will be created (after clicking the command
button). The problem is, when there is an apostrophe in the company's name
that is selected in Combo55, the subform does not populate with that company.
If there is no apostrophe in Combo55, everything works fine. But, I'm new
to Access and have inherited this database, so any help is appreciated.

Thanks.

 

[BruceM]

There's no DLookup, but there is a Where condition that could be causing
problems along the lines I suggested. Try:
rs.FindFirst "[Company Sort] = " " " & Me![Combo55] & " " " "
The spaces between the quotes are for clarity.

To test code you can add a breakpoint. To do that, open the VBA editor and
click just to the left of the code window, in the vertical bar, next to the
line If IsNull(Combo55) = False Then. This will add a red dot on the bar,
and highlight the line of code. Go back to the form and try to update
Combo55. The code will break (stop) at that line. Press F8 to move to the
next line of code. Repeat until the problem line of code causes an error.
When you no longer need the breakpoint you can clear it by clicking the red
dot.

Note that Null and an empty string are not the same thing. You are checking
to see if Combo55 is null (BTW, that can be expressed as If Not
IsNull(Combo55) Then), but in some cases it may be empty but not null. Be
clear on what you are checking. For more information see:
http://allenbrowne.com/casu-11.html
Be sure also to check the Common Errors With Null link at the bottom of the
web page.

Oops, I tried to simplify and I think I simplified the problem out of the
code. I think the problem is with AddCompany in the following code:

Private Sub Combo55_AfterUpdate()
On Error GoTo Err_Combo55_AfterUpdate
Dim rs As Object

If IsNull(Combo55) = False Then
If Combo55 <> "Town Committee" Then
Set rs = Me.Recordset.Clone
rs.FindFirst "[Company Sort] = '" & Me![Combo55] & "'"
Me.Bookmark = rs.Bookmark
Call AddCompany
Else
DoCmd.GoToRecord , , acFirst
Do
If Site = True Then Call AddContact
DoCmd.GoToRecord , , acNext
Loop
End If
End If
Me.Combo55 = ""

Exit_Combo55_AfterUpdate:
Exit Sub

Err_Combo55_AfterUpdate:
Me.Combo55 = ""
Resume Exit_Combo55_AfterUpdate

End Sub


Here's the AddCompany code:

Private Sub AddCompany()
On Error GoTo Err_AddCompany

Dim stDocName As String
stDocName = "rptGLResultTable"

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("tblAddressMatch", dbOpenDynaset)

For i = 1 To Me!NumOfLabels
rs.AddNew
rs!MatchComID = Me![MatchComID]
rs![COMPANIES TABLE_Company Name] = Me![COMPANIES TABLE_Company
Name]
rs![Company Sort] = Me![Company Sort]
rs!Address1 = Me!Address1
rs!Address2 = Me!Address2
rs!City = Me!City
rs!State = Me!State
rs!Zip = Me!Zip
rs.Update
Next i

Me!NumOfLabels = 1
LABELS_Subform.Requery

Exit_AddCompany:
Exit Sub

Err_AddCompany:
MsgBox Err.Description
Resume Exit_AddCompany

End Sub

Combo55 is on a form that merges with Word to create mailing labels. A
company name is selected in Combo55 and the contact info is added to a
subform. Mailing labels are created for the companies listed in the
subform
using the OnClick procedure of a command button. If Town Committee is
selected, all town members are listed with their contact info and a label
is
made for each member. This is the only selection when multiple contacts
are
listed with one selection on Combo55 (that is why it has special code).
Otherwise, the company that is selected in Combo55 will be listed in a
subform and a mailing label will be created (after clicking the command
button). The problem is, when there is an apostrophe in the company's
name
that is selected in Combo55, the subform does not populate with that
company.
If there is no apostrophe in Combo55, everything works fine. But, I'm new
to Access and have inherited this database, so any help is appreciated.

Thanks.

I inherited a database and figured out that the following code will not
work
if there is an apostrophe in the record. Any suggestions?

Private Sub Combo55_AfterUpdate()
On Error GoTo Err_Combo55_AfterUpdate
Dim rs As Object

If IsNull(Combo55) = False Then
DoCmd.GoToRecord , , acFirst
Do
If Site = True Then Call AddContact
DoCmd.GoToRecord , , acNext
Loop
End If
Me.Combo55 = ""

Exit_Combo55_AfterUpdate:
Exit Sub

Err_Combo55_AfterUpdate:
Me.Combo55 = ""
Resume Exit_Combo55_AfterUpdate

End Sub


Thanks!

 

[KAW]

Thank you! It worked. I'm not sure why, but I'm still learning. Thanks
again.

There's no DLookup, but there is a Where condition that could be causing
problems along the lines I suggested. Try:
rs.FindFirst "[Company Sort] = " " " & Me![Combo55] & " " " "
The spaces between the quotes are for clarity.

To test code you can add a breakpoint. To do that, open the VBA editor and
click just to the left of the code window, in the vertical bar, next to the
line If IsNull(Combo55) = False Then. This will add a red dot on the bar,
and highlight the line of code. Go back to the form and try to update
Combo55. The code will break (stop) at that line. Press F8 to move to the
next line of code. Repeat until the problem line of code causes an error.
When you no longer need the breakpoint you can clear it by clicking the red
dot.

Note that Null and an empty string are not the same thing. You are checking
to see if Combo55 is null (BTW, that can be expressed as If Not
IsNull(Combo55) Then), but in some cases it may be empty but not null. Be
clear on what you are checking. For more information see:
http://allenbrowne.com/casu-11.html
Be sure also to check the Common Errors With Null link at the bottom of the
web page.

Oops, I tried to simplify and I think I simplified the problem out of the
code. I think the problem is with AddCompany in the following code:

Private Sub Combo55_AfterUpdate()
On Error GoTo Err_Combo55_AfterUpdate
Dim rs As Object

If IsNull(Combo55) = False Then
If Combo55 <> "Town Committee" Then
Set rs = Me.Recordset.Clone
rs.FindFirst "[Company Sort] = '" & Me![Combo55] & "'"
Me.Bookmark = rs.Bookmark
Call AddCompany
Else
DoCmd.GoToRecord , , acFirst
Do
If Site = True Then Call AddContact
DoCmd.GoToRecord , , acNext
Loop
End If
End If
Me.Combo55 = ""

Exit_Combo55_AfterUpdate:
Exit Sub

Err_Combo55_AfterUpdate:
Me.Combo55 = ""
Resume Exit_Combo55_AfterUpdate

End Sub


Here's the AddCompany code:

Private Sub AddCompany()
On Error GoTo Err_AddCompany

Dim stDocName As String
stDocName = "rptGLResultTable"

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("tblAddressMatch", dbOpenDynaset)

For i = 1 To Me!NumOfLabels
rs.AddNew
rs!MatchComID = Me![MatchComID]
rs![COMPANIES TABLE_Company Name] = Me![COMPANIES TABLE_Company
Name]
rs![Company Sort] = Me![Company Sort]
rs!Address1 = Me!Address1
rs!Address2 = Me!Address2
rs!City = Me!City
rs!State = Me!State
rs!Zip = Me!Zip
rs.Update
Next i

Me!NumOfLabels = 1
LABELS_Subform.Requery

Exit_AddCompany:
Exit Sub

Err_AddCompany:
MsgBox Err.Description
Resume Exit_AddCompany

End Sub

Combo55 is on a form that merges with Word to create mailing labels. A
company name is selected in Combo55 and the contact info is added to a
subform. Mailing labels are created for the companies listed in the
subform
using the OnClick procedure of a command button. If Town Committee is
selected, all town members are listed with their contact info and a label
is
made for each member. This is the only selection when multiple contacts
are
listed with one selection on Combo55 (that is why it has special code).
Otherwise, the company that is selected in Combo55 will be listed in a
subform and a mailing label will be created (after clicking the command
button). The problem is, when there is an apostrophe in the company's
name
that is selected in Combo55, the subform does not populate with that
company.
If there is no apostrophe in Combo55, everything works fine. But, I'm new
to Access and have inherited this database, so any help is appreciated.

Thanks.

I inherited a database and figured out that the following code will not
work
if there is an apostrophe in the record. Any suggestions?

Private Sub Combo55_AfterUpdate()
On Error GoTo Err_Combo55_AfterUpdate
Dim rs As Object

If IsNull(Combo55) = False Then
DoCmd.GoToRecord , , acFirst
Do
If Site = True Then Call AddContact
DoCmd.GoToRecord , , acNext
Loop
End If
Me.Combo55 = ""

Exit_Combo55_AfterUpdate:
Exit Sub

Err_Combo55_AfterUpdate:
Me.Combo55 = ""
Resume Exit_Combo55_AfterUpdate

End Sub


Thanks!

 

[BruceM]

Add two lines of code:
Debug.Print "[Company Sort] = " " " & Me![Combo55] & " " " "
Debug.Print "[Company Sort] = ' " & Me![Combo55] & " ' "

After running the code (by updating Combo55) open the immediate window by
pressing Ctrl + G. Debug.Print will cause the result of the rest of the
line of code to be written to the immediate window. You should see it
displayed there. Note the difference. If the company is Bob's Pizza, in
the first instance you will see something like:
CompanySort = "Bob's Pizza"
In the second you will see:
CompanySort = 'Bob's Pizza'

In the both instances the quote before [Company Sort] starts the string. In
order for a literal quote to appear in the string, a quote mark needs to be
surrounded by quote marks. That results in the quote after the = sign in
the first instance. The first three quotes of the four at the end in the
first instance do the same thing as the three quotes after the = sign: they
produce a quote mark in the text string. The last quote mark in both
instances closes the text string. Without trying to get into a lot of
details about the apostrophe system, just think of the apostrophe as
replacing two quote marks.

Access can work with either way of handling quotes as long as the field
contains neither quotes nor apostrophes. However, if you use the apostrophe
system and there is an apostrophe, Access will see:
CompanySort = 'Bob'
As far as Access knows, the apostrophe in Bob's is the end of the string, so
the rest of the line makes no sense and either you get an error or Access
ignores the rest of the string. Unless there is a company named Bob,
FindFirst will not come up with a match.
If there is a quote mark in the field's value you will run into a similar
problem. There are ways around the problem when working with a text field,
but another approach is to use a number (autonumber, for instance) as the
primary key for the company, and as the bound (but invisible) column in
Combo55. Then you would have something like:
"CompanyID = " & Me.CompanyID
which would produce something like:
CompanyID = 123
The use of a numeric primary key has other advantages such as accomodating a
company's name change without your having to update a lot of records. I
realize the current database is an inherited project, but it's something to
think about when you do your own projects.

Thank you! It worked. I'm not sure why, but I'm still learning. Thanks
again.

There's no DLookup, but there is a Where condition that could be causing
problems along the lines I suggested. Try:
rs.FindFirst "[Company Sort] = " " " & Me![Combo55] & " " " "
The spaces between the quotes are for clarity.

To test code you can add a breakpoint. To do that, open the VBA editor
and
click just to the left of the code window, in the vertical bar, next to
the
line If IsNull(Combo55) = False Then. This will add a red dot on the
bar,
and highlight the line of code. Go back to the form and try to update
Combo55. The code will break (stop) at that line. Press F8 to move to
the
next line of code. Repeat until the problem line of code causes an
error.
When you no longer need the breakpoint you can clear it by clicking the
red
dot.

Note that Null and an empty string are not the same thing. You are
checking
to see if Combo55 is null (BTW, that can be expressed as If Not
IsNull(Combo55) Then), but in some cases it may be empty but not null.
Be
clear on what you are checking. For more information see:
http://allenbrowne.com/casu-11.html
Be sure also to check the Common Errors With Null link at the bottom of
the
web page.

Oops, I tried to simplify and I think I simplified the problem out of
the
code. I think the problem is with AddCompany in the following code:

Private Sub Combo55_AfterUpdate()
On Error GoTo Err_Combo55_AfterUpdate
Dim rs As Object

If IsNull(Combo55) = False Then
If Combo55 <> "Town Committee" Then
Set rs = Me.Recordset.Clone
rs.FindFirst "[Company Sort] = '" & Me![Combo55] & "'"
Me.Bookmark = rs.Bookmark
Call AddCompany
Else
DoCmd.GoToRecord , , acFirst
Do
If Site = True Then Call AddContact
DoCmd.GoToRecord , , acNext
Loop
End If
End If
Me.Combo55 = ""

Exit_Combo55_AfterUpdate:
Exit Sub

Err_Combo55_AfterUpdate:
Me.Combo55 = ""
Resume Exit_Combo55_AfterUpdate

End Sub


Here's the AddCompany code:

Private Sub AddCompany()
On Error GoTo Err_AddCompany

Dim stDocName As String
stDocName = "rptGLResultTable"

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim i As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("tblAddressMatch", dbOpenDynaset)

For i = 1 To Me!NumOfLabels
rs.AddNew
rs!MatchComID = Me![MatchComID]
rs![COMPANIES TABLE_Company Name] = Me![COMPANIES TABLE_Company
Name]
rs![Company Sort] = Me![Company Sort]
rs!Address1 = Me!Address1
rs!Address2 = Me!Address2
rs!City = Me!City
rs!State = Me!State
rs!Zip = Me!Zip
rs.Update
Next i

Me!NumOfLabels = 1
LABELS_Subform.Requery

Exit_AddCompany:
Exit Sub

Err_AddCompany:
MsgBox Err.Description
Resume Exit_AddCompany

End Sub

Combo55 is on a form that merges with Word to create mailing labels. A
company name is selected in Combo55 and the contact info is added to a
subform. Mailing labels are created for the companies listed in the
subform
using the OnClick procedure of a command button. If Town Committee is
selected, all town members are listed with their contact info and a
label
is
made for each member. This is the only selection when multiple
contacts
are
listed with one selection on Combo55 (that is why it has special code).
Otherwise, the company that is selected in Combo55 will be listed in a
subform and a mailing label will be created (after clicking the command
button). The problem is, when there is an apostrophe in the company's
name
that is selected in Combo55, the subform does not populate with that
company.
If there is no apostrophe in Combo55, everything works fine. But, I'm
new
to Access and have inherited this database, so any help is appreciated.

Thanks.

:

I inherited a database and figured out that the following code will
not
work
if there is an apostrophe in the record. Any suggestions?

Private Sub Combo55_AfterUpdate()
On Error GoTo Err_Combo55_AfterUpdate
Dim rs As Object

If IsNull(Combo55) = False Then
DoCmd.GoToRecord , , acFirst
Do
If Site = True Then Call AddContact
DoCmd.GoToRecord , , acNext
Loop
End If
Me.Combo55 = ""

Exit_Combo55_AfterUpdate:
Exit Sub

Err_Combo55_AfterUpdate:
Me.Combo55 = ""
Resume Exit_Combo55_AfterUpdate

End Sub


Thanks!