Need Help Handling Apostrophes with New Data

[rebecky via AccessMonster.com]

Hello. I am trying to handle the apostrophes during this NotInList event:
I cannot get it right and am at my wits end.
Any ideas?

Thanks!


Private Sub ContactID_NotInList(NewData As String, Response As Integer)
Dim Result
Dim Msg As String
Dim CR As String ' Carriage Return

'Title = "TED version 2.1"

CR = vbCrLf

' Return Control object that points to combo box.
Dim ctl As Control
Set ctl = Me![ContactID] 'An alias for the name of this combo-box,
whichreduces typing.


' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

NewData = StrConv(NewData, vbProperCase) ' Converts new entry to ProperCase.


' Ask the user if he or she wishes to add the new contact.

Msg = ""
Msg = Msg & "'" & NewData & "' is not in TED Site Contacts" & CR & CR
Msg = Msg & "Do you want to add this Site Contact?"
DoCmd.SetWarnings False

If MsgBox(Msg, vbQuestion + vbYesNo, "TED version 2.1") = vbYes Then
' Set Response argument to indicate that data is being added.
strSql = "INSERT INTO [tblsitecontacts] (contactname) " & _
"VALUES ('" & NewData & " & Chr$(34)"" & Me![ContactName] &
Chr$(34)"");"

DoCmd.RunSQL strSql
intAnswer = Nz(DMax("[contactID]", "[tblsitecontacts]"), 0)
MsgBox "The new Contact" & CR & NewData & " was Added. Please select
" & NewData & " from the List and continue Entering " & NewData & "
Information or Use the Undo Button" _
, vbInformation, "TED version 2.1"

Response = acDataErrAdded

'** MUST ** undo entry first, or you get "Can't go to specified record"

'DoCmd.GoToRecord , , acNewRec

ctl.Undo


DoCmd.RunCommand acCmdSaveRecord


'Supress error message
Response = acDataErrContinue


Else
'User clicked the No button here.
'You could display a message to "try again", but that just annoys mostof
us.
'Supress error message

Response = acDataErrContinue
ctl.Undo
SendKeys "{ESC}"
Exit Sub
End If

Set ctl = Nothing ' ANY time you use "Set", you should "unset" it
beforeleaving.
'ErrContinue
Me.ContactID.Requery

 

[rebecky via AccessMonster.com]

Well, I found the answer finally here on this board!!
Thank you

Hello. I am trying to handle the apostrophes during this NotInList event:
I cannot get it right and am at my wits end.
Any ideas?

Thanks!

Private Sub ContactID_NotInList(NewData As String, Response As Integer)
Dim Result
Dim Msg As String
Dim CR As String ' Carriage Return

'Title = "TED version 2.1"

CR = vbCrLf

' Return Control object that points to combo box.
Dim ctl As Control
Set ctl = Me![ContactID] 'An alias for the name of this combo-box,
whichreduces typing.

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

NewData = StrConv(NewData, vbProperCase) ' Converts new entry to ProperCase.

' Ask the user if he or she wishes to add the new contact.

Msg = ""
Msg = Msg & "'" & NewData & "' is not in TED Site Contacts" & CR & CR
Msg = Msg & "Do you want to add this Site Contact?"
DoCmd.SetWarnings False

If MsgBox(Msg, vbQuestion + vbYesNo, "TED version 2.1") = vbYes Then
' Set Response argument to indicate that data is being added.
strSql = "INSERT INTO [tblsitecontacts] (contactname) " & _
"VALUES ('" & NewData & " & Chr$(34)"" & Me![ContactName] &
Chr$(34)"");"

DoCmd.RunSQL strSql
intAnswer = Nz(DMax("[contactID]", "[tblsitecontacts]"), 0)
MsgBox "The new Contact" & CR & NewData & " was Added. Please select
" & NewData & " from the List and continue Entering " & NewData & "
Information or Use the Undo Button" _
, vbInformation, "TED version 2.1"

Response = acDataErrAdded

'** MUST ** undo entry first, or you get "Can't go to specified record"

'DoCmd.GoToRecord , , acNewRec

ctl.Undo


DoCmd.RunCommand acCmdSaveRecord

'Supress error message
Response = acDataErrContinue


Else
'User clicked the No button here.
'You could display a message to "try again", but that just annoys mostof
us.
'Supress error message

Response = acDataErrContinue
ctl.Undo
SendKeys "{ESC}"
Exit Sub
End If

Set ctl = Nothing ' ANY time you use "Set", you should "unset" it
beforeleaving.
'ErrContinue
Me.ContactID.Requery

 

[Klatuu]

I'll share a little trick with you rebecky.

You have stumbled into the problem where a single qoute (apostrophe) creates
problems when search for or filtering data.
As you probably have discovered by now, the solution is to always use double
qoutes to delimit text values; but you have also found that trying to get
the right number of double quotes to create a string with a double quote
would make Chuck Norris break down in tears. This used to drive me crazy
until I came up with a trick that eliminated the problem. This trick is
like training wheels on a bicycle. Use it for a while, then you will find
you understand how it works, and you wont need it any more.

When you need to put qoutes in a string, first write the code using the
single qoutes....easy enough.

v v
strSql = "INSERT INTO [tblsitecontacts] (contactname) VALUES ('" &
NewData & "' );"

Now, go back and replace each single qoute with two double qoutes:


v v
strSql = "INSERT INTO [tblsitecontacts] (contactname) VALUES (""" &
NewData & """ );"

Well, I found the answer finally here on this board!!
Thank you

Hello. I am trying to handle the apostrophes during this NotInList event:
I cannot get it right and am at my wits end.
Any ideas?

Thanks!

Private Sub ContactID_NotInList(NewData As String, Response As Integer)
Dim Result
Dim Msg As String
Dim CR As String ' Carriage Return

'Title = "TED version 2.1"

CR = vbCrLf

' Return Control object that points to combo box.
Dim ctl As Control
Set ctl = Me![ContactID] 'An alias for the name of this combo-box,
whichreduces typing.

' Exit this subroutine if the combo box was cleared.
If NewData = "" Then Exit Sub

NewData = StrConv(NewData, vbProperCase) ' Converts new entry to
ProperCase.

' Ask the user if he or she wishes to add the new contact.

Msg = ""
Msg = Msg & "'" & NewData & "' is not in TED Site Contacts" & CR & CR
Msg = Msg & "Do you want to add this Site Contact?"
DoCmd.SetWarnings False

If MsgBox(Msg, vbQuestion + vbYesNo, "TED version 2.1") = vbYes Then
' Set Response argument to indicate that data is being added.
strSql = "INSERT INTO [tblsitecontacts] (contactname) " & _
"VALUES ('" & NewData & " & Chr$(34)"" & Me![ContactName]
&
Chr$(34)"");"

DoCmd.RunSQL strSql
intAnswer = Nz(DMax("[contactID]", "[tblsitecontacts]"), 0)
MsgBox "The new Contact" & CR & NewData & " was Added. Please
select
" & NewData & " from the List and continue Entering " & NewData & "
Information or Use the Undo Button" _
, vbInformation, "TED version 2.1"

Response = acDataErrAdded

'** MUST ** undo entry first, or you get "Can't go to specified record"

'DoCmd.GoToRecord , , acNewRec

ctl.Undo


DoCmd.RunCommand acCmdSaveRecord

'Supress error message
Response = acDataErrContinue


Else
'User clicked the No button here.
'You could display a message to "try again", but that just annoys
mostof
us.
'Supress error message

Response = acDataErrContinue
ctl.Undo
SendKeys "{ESC}"
Exit Sub
End If

Set ctl = Nothing ' ANY time you use "Set", you should "unset" it
beforeleaving.
'ErrContinue
Me.ContactID.Requery

 

[rebecky via AccessMonster.com]

Thanks for the tip! Certainly SOUNDS easy enough! I will use it next time
the occasion calls for it.
I am having a dilly of a time with this:
I want the user to be able to go to a new record in the subform after
choosing a value from the combo box, then choose another value from the combo
box for that record....is that doable? Some "jobs" have more than one contact
so that is why I need it.....

Thanks again!

I'll share a little trick with you rebecky.

You have stumbled into the problem where a single qoute (apostrophe) creates
problems when search for or filtering data.
As you probably have discovered by now, the solution is to always use double
qoutes to delimit text values; but you have also found that trying to get
the right number of double quotes to create a string with a double quote
would make Chuck Norris break down in tears. This used to drive me crazy
until I came up with a trick that eliminated the problem. This trick is
like training wheels on a bicycle. Use it for a while, then you will find
you understand how it works, and you wont need it any more.

When you need to put qoutes in a string, first write the code using the
single qoutes....easy enough.

v v
strSql = "INSERT INTO [tblsitecontacts] (contactname) VALUES ('" &
NewData & "' );"

Now, go back and replace each single qoute with two double qoutes:


v v
strSql = "INSERT INTO [tblsitecontacts] (contactname) VALUES (""" &
NewData & """ );"

Well, I found the answer finally here on this board!!
Thank you

[quoted text clipped - 77 lines]

 

[Klatuu]

You don't need to go to a new record. You are adding the new record in the
Combo if the contact name doesn't already exist.

The issue you have is that the new record is added to the table, but it is
not yet in your subform's recordset. To get it there, you have to requery
the subform. After you do that, the subform will go back to the first
record, but since you actually want to edit the new record, you can make it
the current record. Here is some additional code you can add to your
NotInList event to do that:

strSql = "INSERT INTO [tblsitecontacts] (contactname) VALUES (""" &
NewData & """);"
Currentdb.Execute strSQL, dbFailOnError

Me.MySubForm.Form.Requery

With Me.MySubForm.Form.RecordsetClone
.FindFirst "[contactname] = """ & NewData & """"
If Not .NoMatch Then
Me.MySubForm.Form.Bookmark = .Bookmark
End If
End With

Thanks for the tip! Certainly SOUNDS easy enough! I will use it next
time
the occasion calls for it.
I am having a dilly of a time with this:
I want the user to be able to go to a new record in the subform after
choosing a value from the combo box, then choose another value from the
combo
box for that record....is that doable? Some "jobs" have more than one
contact
so that is why I need it.....

Thanks again!

I'll share a little trick with you rebecky.

You have stumbled into the problem where a single qoute (apostrophe)
creates
problems when search for or filtering data.
As you probably have discovered by now, the solution is to always use
double
qoutes to delimit text values; but you have also found that trying to get
the right number of double quotes to create a string with a double quote
would make Chuck Norris break down in tears. This used to drive me crazy
until I came up with a trick that eliminated the problem. This trick is
like training wheels on a bicycle. Use it for a while, then you will find
you understand how it works, and you wont need it any more.

When you need to put qoutes in a string, first write the code using the
single qoutes....easy enough.

v v
strSql = "INSERT INTO [tblsitecontacts] (contactname) VALUES ('" &
NewData & "' );"

Now, go back and replace each single qoute with two double qoutes:


v v
strSql = "INSERT INTO [tblsitecontacts] (contactname) VALUES (""" &
NewData & """ );"

Well, I found the answer finally here on this board!!
Thank you

[quoted text clipped - 77 lines]

'ErrContinue
Me.ContactID.Requery

 

[rebecky via AccessMonster.com]

Thank you. You have been MOST helpful. I cannot seem to accomplish what I
want, and though I almost Always refuse to believe "it can't be done", I
don't think it can be done.

This was my intention:

I have a MainForm for Employers(table1) and a Subform for Job Order
Information(table2) - related by EmployerID. Within the Job Order
Information Subform, there is a Subform for Job Order Contacts
(tblSiteContacts) related by PositionID. It is a subform because some Job
Orders have more than one contact. What I wanted to do was have a Combo box
on the Job Order Information Subform, so the user could choose the contact
if it was already there and it would pop up in the subform, and if not there,
add it(NotInList Event), and then go to the next record in the subform and
choose a second contact for THAT job order from the combo box or "enter" a
second contact for that job order. I could not get the combo to work at all
when the form was related by positionid, but it will work when related by
contactid only that doesn't keep the "contact" with the "position" as they
are related by "positionID".

It doesn't work because when you choose a second value from the ComboBox, it
just changes the first record in the subform and does nothing at the second
record. Then if you go to the next record in the subform and try to type a
second contact in without using the combo box, you get an error message about
not being able to assign a value to that object.

I hope you are not thoroughly confused now!!! I am about out of time for
this revised version of this Database, so do you know if this can actually
work somehow?

Thanks again
rebecky

You don't need to go to a new record. You are adding the new record in the
Combo if the contact name doesn't already exist.

The issue you have is that the new record is added to the table, but it is
not yet in your subform's recordset. To get it there, you have to requery
the subform. After you do that, the subform will go back to the first
record, but since you actually want to edit the new record, you can make it
the current record. Here is some additional code you can add to your
NotInList event to do that:

strSql = "INSERT INTO [tblsitecontacts] (contactname) VALUES (""" &
NewData & """);"
Currentdb.Execute strSQL, dbFailOnError

Me.MySubForm.Form.Requery

With Me.MySubForm.Form.RecordsetClone
.FindFirst "[contactname] = """ & NewData & """"
If Not .NoMatch Then
Me.MySubForm.Form.Bookmark = .Bookmark
End If
End With

Thanks for the tip! Certainly SOUNDS easy enough! I will use it next
time

[quoted text clipped - 41 lines]

 

[Klatuu]

Actually, it will work. I have an almost identical subform working, but the
only difference it is not the subform of a subform, but that should not make
a difference.

The only reason it is not the subform of a subform is because I almost never
allow any editing directly in a datasheet subform. If a user wants to edit
data in a subform record, they have to open a form that does the editing in
a single form form.

On the main form (editing form for subform), I have a combo that allows the
user to add a new existing contact to the job or, if the contact doesn't
exits, it opens the form to a new record with the contact name already
entered and allows the user to complete the record.

Thank you. You have been MOST helpful. I cannot seem to accomplish what
I
want, and though I almost Always refuse to believe "it can't be done", I
don't think it can be done.

This was my intention:

I have a MainForm for Employers(table1) and a Subform for Job Order
Information(table2) - related by EmployerID. Within the Job Order
Information Subform, there is a Subform for Job Order Contacts
(tblSiteContacts) related by PositionID. It is a subform because some Job
Orders have more than one contact. What I wanted to do was have a Combo
box
on the Job Order Information Subform, so the user could choose the
contact
if it was already there and it would pop up in the subform, and if not
there,
add it(NotInList Event), and then go to the next record in the subform and
choose a second contact for THAT job order from the combo box or "enter" a
second contact for that job order. I could not get the combo to work at
all
when the form was related by positionid, but it will work when related by
contactid only that doesn't keep the "contact" with the "position" as they
are related by "positionID".

It doesn't work because when you choose a second value from the ComboBox,
it
just changes the first record in the subform and does nothing at the
second
record. Then if you go to the next record in the subform and try to type
a
second contact in without using the combo box, you get an error message
about
not being able to assign a value to that object.

I hope you are not thoroughly confused now!!! I am about out of time for
this revised version of this Database, so do you know if this can actually
work somehow?

Thanks again
rebecky

You don't need to go to a new record. You are adding the new record in
the
Combo if the contact name doesn't already exist.

The issue you have is that the new record is added to the table, but it is
not yet in your subform's recordset. To get it there, you have to
requery
the subform. After you do that, the subform will go back to the first
record, but since you actually want to edit the new record, you can make
it
the current record. Here is some additional code you can add to your
NotInList event to do that:

strSql = "INSERT INTO [tblsitecontacts] (contactname) VALUES (""" &
NewData & """);"
Currentdb.Execute strSQL, dbFailOnError

Me.MySubForm.Form.Requery

With Me.MySubForm.Form.RecordsetClone
.FindFirst "[contactname] = """ & NewData & """"
If Not .NoMatch Then
Me.MySubForm.Form.Bookmark = .Bookmark
End If
End With

Thanks for the tip! Certainly SOUNDS easy enough! I will use it next
time

[quoted text clipped - 41 lines]

'ErrContinue
Me.ContactID.Requery

 

[rebecky via AccessMonster.com]

Well, the subform is not in Data Sheet View. It is in Single Form View with
buttons to go to next or previous or to add. The idea of opening a form to
enter is something I will investigate......

Actually, it will work. I have an almost identical subform working, but the
only difference it is not the subform of a subform, but that should not make
a difference.

The only reason it is not the subform of a subform is because I almost never
allow any editing directly in a datasheet subform. If a user wants to edit
data in a subform record, they have to open a form that does the editing in
a single form form.

On the main form (editing form for subform), I have a combo that allows the
user to add a new existing contact to the job or, if the contact doesn't
exits, it opens the form to a new record with the contact name already
entered and allows the user to complete the record.

Thank you. You have been MOST helpful. I cannot seem to accomplish what
I

[quoted text clipped - 69 lines]

 

[Klatuu]

If you are using a single form, then you don't need another form, you just
need to go to a new record.

Well, the subform is not in Data Sheet View. It is in Single Form View
with
buttons to go to next or previous or to add. The idea of opening a form
to
enter is something I will investigate......

Actually, it will work. I have an almost identical subform working, but
the
only difference it is not the subform of a subform, but that should not
make
a difference.

The only reason it is not the subform of a subform is because I almost
never
allow any editing directly in a datasheet subform. If a user wants to
edit
data in a subform record, they have to open a form that does the editing
in
a single form form.

On the main form (editing form for subform), I have a combo that allows
the
user to add a new existing contact to the job or, if the contact doesn't
exits, it opens the form to a new record with the contact name already
entered and allows the user to complete the record.

Thank you. You have been MOST helpful. I cannot seem to accomplish
what
I

[quoted text clipped - 69 lines]

'ErrContinue
Me.ContactID.Requery

 

[rebecky via AccessMonster.com]

Yeah, I know.... I was just hoping there was a way to go to a new record in
that subform and pick a different value from that combo box on the parent
form, but it won't work.

If you are using a single form, then you don't need another form, you just
need to go to a new record.

Well, the subform is not in Data Sheet View. It is in Single Form View
with

[quoted text clipped - 28 lines]