More apostrophe nightmares

[R. Choate]

My form is driving me crazy because of stupid apostrophes in the data which the form is drawing from. I have 2 combos. cbo #1 is
populated by project names.

(The control is also named "Project Name" while the cbo #2 is named "Tenant" and is populated by tenant names from the tenant field
in the table. I know this is bad form, so to speak, but lets please ignore that today. I didn't create the form or the control and I
have limited time so I have to pick my battles).

When I select a project in the first cbo "Project Name", the goal is to have the tenant cbo be populated only by tenants where the
project name matches the name shown in the "Project Name" cbo. The problem arises when the project name has an apostrophe, or maybe
some other punctuation mark, in it. Then I get a syntax error message.

Here is the complete code for the got focus event of the "Tenant" cbo box. This is where the problem lies (I have the entire sql
statement on one line so beware of your newsreader and please don't adjust the code to break it up into multiple shorter lines. I
will do that on my own after I find a solution):

Private Sub Tenant_GotFocus()
Me.[Tenant].RowSource = "SELECT DISTINCT [Tenant], [Project Name] FROM Commission WHERE [Tenant] is not null and [Project Name] = '"
& Me.[Project Name] & "' ORDER BY Commission.[Tenant];"
End Sub

Thank you VERY much in advance for your help !

Richard

 

[R. Choate]

Never mind. I had a similar problem earlier but I thought it was different. After studying that some more I could see that the
situation was really essentially identical and required identical solutions. I tested the solution from before and sure enough, it
worked.

I'm sorry I didn't study this carefully enough before posting again on basically the same problem.
--
RMC,CPA


My form is driving me crazy because of stupid apostrophes in the data which the form is drawing from. I have 2 combos. cbo #1 is
populated by project names.

(The control is also named "Project Name" while the cbo #2 is named "Tenant" and is populated by tenant names from the tenant field
in the table. I know this is bad form, so to speak, but lets please ignore that today. I didn't create the form or the control and I
have limited time so I have to pick my battles).

When I select a project in the first cbo "Project Name", the goal is to have the tenant cbo be populated only by tenants where the
project name matches the name shown in the "Project Name" cbo. The problem arises when the project name has an apostrophe, or maybe
some other punctuation mark, in it. Then I get a syntax error message.

Here is the complete code for the got focus event of the "Tenant" cbo box. This is where the problem lies (I have the entire sql
statement on one line so beware of your newsreader and please don't adjust the code to break it up into multiple shorter lines. I
will do that on my own after I find a solution):

Private Sub Tenant_GotFocus()
Me.[Tenant].RowSource = "SELECT DISTINCT [Tenant], [Project Name] FROM Commission WHERE [Tenant] is not null and [Project Name] = '"
& Me.[Project Name] & "' ORDER BY Commission.[Tenant];"
End Sub

Thank you VERY much in advance for your help !

Richard