I have a form containing two cascading combo boxes and based on the selections a report is displayed for a single record. The first combo box is cboAgency and the dependent combo box is cboProject.
The Agency field in the table may contain an apostrophe. I have a query that populates cboAgency and I am replacing the single quote with two single quotes in that query in order to escape the single quote. This part works fine. When I click on cboAgency, an Agency containing: Joe's Place would be displayed in the combo box as Joe''s Place. In the AfterUpdate event of cboAgency, I am filtering so that only the Projects for the selected Joe's Place are displayed in the cboProject combo box. The filtering code in the AfterUpdate event is:
cboProject.RowSource = "SELECT DISTINCT([tblMain].[Project]) " & _
"FROM [tblMain] WHERE [tblMain].[Agency] = '" & cboAgency & "'" & _
"ORDER BY Project"
All good so far.
Then I click on the cboProject and select a Project, for example, Project HELP. I click a command button to display the report. I get no results and I know why. I just don't know how to fix it. The report's Record Source is a query containing this WHERE clause:
WHERE (((tblMain.Agency)=[Forms]![frmSelect]![cboAgency]) AND ((tblMain.Project)=[Forms]![frmSelect]![cboProject]))
I get no results because in the table, Agency contains the value of Joe's Place while the cboAgency combo box value is Joe''s Place. As you can see Joe's Place with one single quote does not equal Joe''s Place with two single quotes.
I am doing this replacement because I was getting an error when I selected an Agency that contained an apostrophe. I would really appreciate help with this issue. Please keep in mind that I am a novice at VBA.
Just to prevent any scolding for having basically the same code in the after update event and in the query's WHERE clause. It is because I am a novice and really don't know any better so if there is any redundant code, please forgive me. Also, I have substituted easier names for the tables, columns and combo boxes in this post just to make things a little more straightforward.
Thanks, in advance, for your feedback.
Crockett