Apostrophe Challenges

[jonefer]

What should I do to compensate for this error?

A related anomaly to combo box coding (or any coding where a name could be
part of something examined) is that if you have an apostrophe in the name, It
will cause an error in the code.

For example: "Frank's Hot Dog Shop"

completely skips all the code in a NotInList event
and still returns the error "this item is Not in the list"

whereas Franks Hot Dog Shop gets added

 

[Dirk Goldgar]

What should I do to compensate for this error?

A related anomaly to combo box coding (or any coding where a name
could be part of something examined) is that if you have an
apostrophe in the name, It will cause an error in the code.

For example: "Frank's Hot Dog Shop"

completely skips all the code in a NotInList event
and still returns the error "this item is Not in the list"

whereas Franks Hot Dog Shop gets added

Actually, you can code around these problems. If you post the code in
question, we can show you how to keep the apostrophe from making
trouble.

 

[jonefer]

Thank you. I didn't want to unleash a bunch of code unless absolutely
necessary. But perhaps it may help you deterimine something underlying.
Here's the code with situations:


I have an AddNewGroup form that doubles as a Modify form
If the user clicks a particular button beside the combo box, it opens up the
AddNewGroup form in Edit mode and the procedure usually in the "Accept"
button changes to the following code:

....case "Modify"

If Me.Dirty Then 'if the user changes anything
'open the 'will modify form'
'first validate
If IsNull(Me.Group_Name) Then
MsgBox "Group Name cannot be left blank"
Me.Group_Name.SetFocus
ElseIf IsNull(Me.Group__) Then
MsgBox "Group # cannot be blank, please enter a group
number"
Me.Group__.SetFocus
Else
'if validation is passed then show them what will be
modified
DoCmd.OpenForm "frmWillModify"
End If


Else 'if no modifications have been made, just close the form

DoCmd.Close
End If
end sub

It opens a secondary form that previews all the modifications that will be
made to a certain group name (This is clean up mode for a badly designed
database
that includes a bunch of repeated various name entries)
In other words, while migrating to the new database, my interface will allow
them to look at group names, pick the one they want to be the unique one, and
it will show them all the records about to be affected and give them the
choice of whether or not to change it.

This secondary form is the "frmWillModify" which is based on a query that
shows all the records that match the GroupNum that is attached to the
GroupName that they just changed.

The code works almost flawlessly except in the following condition:

If the user picks a group name like "John's Aerobics"
the frmWill Modify, which is only based on the results of a query, the
result set comes up empty.

I managed to successfully use the "Replace() function, when entering a new
record.
But for some reason, the query (which also uses the 'Replace()' function)
comes up with mixed results.

If there is a standard way to deal with names that have apostrophe's in them
whenever you have code that uses the "apostrophied" name, I'd certainly like
to adopt it.

Thanks again.

 

[Dirk Goldgar]

Thank you. I didn't want to unleash a bunch of code unless absolutely
necessary. But perhaps it may help you deterimine something
underlying. Here's the code with situations:


I have an AddNewGroup form that doubles as a Modify form
If the user clicks a particular button beside the combo box, it opens
up the AddNewGroup form in Edit mode and the procedure usually in the
"Accept" button changes to the following code:

...case "Modify"

If Me.Dirty Then 'if the user changes anything
'open the 'will modify form'
'first validate
If IsNull(Me.Group_Name) Then
MsgBox "Group Name cannot be left blank"
Me.Group_Name.SetFocus
ElseIf IsNull(Me.Group__) Then
MsgBox "Group # cannot be blank, please enter a
group number"
Me.Group__.SetFocus
Else
'if validation is passed then show them what will
be modified
DoCmd.OpenForm "frmWillModify"
End If


Else 'if no modifications have been made, just close the form

DoCmd.Close
End If
end sub

It opens a secondary form that previews all the modifications that
will be made to a certain group name (This is clean up mode for a
badly designed database
that includes a bunch of repeated various name entries)
In other words, while migrating to the new database, my interface
will allow them to look at group names, pick the one they want to be
the unique one, and it will show them all the records about to be
affected and give them the choice of whether or not to change it.

This secondary form is the "frmWillModify" which is based on a query
that shows all the records that match the GroupNum that is attached
to the GroupName that they just changed.

The code works almost flawlessly except in the following condition:

If the user picks a group name like "John's Aerobics"
the frmWill Modify, which is only based on the results of a query, the
result set comes up empty.

I managed to successfully use the "Replace() function, when entering
a new record.
But for some reason, the query (which also uses the 'Replace()'
function) comes up with mixed results.

If there is a standard way to deal with names that have apostrophe's
in them whenever you have code that uses the "apostrophied" name, I'd
certainly like to adopt it.

Nothing you've posted above should be affected by apostrophes in
Group_Name, so I guess it's either the query on which frmWillModify is
based or code behind frmWillModify. You'll need to post that query
and/or code if I'm to make a useful suggestion.

 

[jonefer]

You were right about the query, so I made sure I used the 'replace' function
everywhere the name was used in my query.

..... the only glitch now is that anything with an apostrophe appears like
this in query results:

John''s Hamburgers

Can anything be done about that?

 

[Dirk Goldgar]

You were right about the query, so I made sure I used the 'replace'
function everywhere the name was used in my query.

.... the only glitch now is that anything with an apostrophe appears
like this in query results:

John''s Hamburgers

Can anything be done about that?

I'm forced to repeat: I can't make any worthwhile recommendations
without seeing the query and/or code. I'm certain something can be done
about it, but I'm not psychic.