Text With Apostrophes Throw Error from Form

[irunergoiam]

I'm having a problem when an apostrophe shows up in an unbound field (it's for either the first or last name of the person of record) on a form that:

1. creates a single record in 1STARTRequestLogAll for the individual whose name is selected in from the combo box.
2. creates multiple records in 1STARTRequestLog for the same person indicating which system access , e.g., a record for each system: network access, ms outlook, etc) the person uses
3. based the records in 1STARTRequestLog for the person selected in the forms combo box, it then sends e-mails.

It all works smashingly when the person's first or last name does not have an apostrophe in it. I've attached the code that's giving me problems as I know that the " and ' delimiters are what is creating the error when it sees the ' in the person's name. How do I program around this so that if there is an ' in the person's name, it will not only continue working, but pass the person's name with the ' to the tables (1 and 2 above) as records are added AND pass the person's name with the ' in the subject line and body of the e-mails that go out (3 above).

Any help would be greatly appreciated. I attached the code in a .txt file for anyone that might be able to help sort this out.

 

[JANR]

You can either use Chr(34) as a wrapper around the variable or double up the quotes around the variable.

...[LastName] = " & Chr(34) & MyVariable & Chr(34)

...[LastName] = """ & MyVariable & """"

JR

 

[Galaxiom]

If rs("[CCSysAdminEmail1]") = Null Or Len(rs("[CCSysAdminEmail1]")) > 0 Then

This line is not doing what you expect because = Null is not a valid test since Null is not equal to anything.

Use:

If Len(vbNullString & rs("[CCSysAdminEmail1]")) > 0 Then

Also instead of this:

rs("[CCSysAdminEmail1]")

I suggest you use the easier to read:

rs!CCSysAdminEmail1