Results 1 to 4 of 4
  1. #1
    crockee is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2016
    Location
    Tennessee
    Posts
    2

    Single Quote in Cascading Combo Box

    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

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I am replacing the single quote with two single quotes in that query in order to escape the single quote.
    Why? a properly constructed query or sql statement should not have a problem with the single quote/apostrophe in the table. Note Sammy's.
    DsID LocationID RequestName
    a 15 sammy's
    b 20 joe
    c 25 linda
    k 10 Kasey
    S 10 Scott

    I realize you posted the reason, but my meaning was that you might benefit from stepping back and resolving whatever made you take this route.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    crockee is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jan 2016
    Location
    Tennessee
    Posts
    2
    Micron,

    The original problem was where I got the error when I click on the 2nd combo box after I've made a selection in the first combo box. I only got the original error If I selected an Agency that contained an apostrophe. For example:
    Select Agency:My Place
    Your Place
    Joe's Place
    and where the selection contains an apostrophe.
    ql statement that populates the first combo box is not the issue. I get the error when I click on the dependent combo box after I've made a selection in the first combo box and where the selection contains an apostrophe. Without the code to do the escape, I get the following error when I click on the 2nd combo box, cboProject.

    Syntax error (missing operator) in query expression '[tblMain].[Agency] = 'Joe's Place'.

    I did a search on the error + cascading combo boxes and found quite a few things that talked about the same type of problem so I know others are coming across this as well. All of the sites I found said to do this replace to escape the single quote but none of them were doing these cascading combo boxes to select a specific record. All examples I found were where they already had the specific record and were adding or changing a record. Nothing addressed my specific problem where my form containing the two cascading combo boxes was being used as a way of navigating to a specific record.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    From the beginning, I believed something had to be wrong that was not evident because I don't see why you'd have a problem with the apostrophe unless you were creating a sql statement incorrectly so I created my own version based on your information.
    Click image for larger version. 

Name:	frmMain.jpg 
Views:	10 
Size:	42.4 KB 
ID:	24770
    As you can see, I got results in a data sheet (instead of creating a report) for Joe's Place, and I did nothing to deal with the apostrophe. For me, it does not matter if the datasheet record source is a query or a sql statement - it works either way.
    Data sheet record source:
    Code:
    SELECT tblMain.PROJECT, tblMain.AGENCY FROM tblMain WHERE 
    (((tblMain.PROJECT)=[forms]![frmMain].[cboProject]) AND ((tblMain.AGENCY)=[forms]![frmMain].[cboAgency]));
    cboAgency row source: SELECT DISTINCT AGENCY FROM tblMain;
    cboProject row source: SELECT PROJECT FROM tblMain WHERE AGENCY=Forms!frmMain.cboAgency;
    The Agency AfterUpdate requeries the project combo.

    This may be one of those times where I can be short-sighted, but I fail to see the solution because I haven't picked up on the cause. Nor do I understand why you're doing anything about the apostrophe. If this post doesn't help, you can post links to the sites where you said others had to deal with this and maybe something will click for me. Or you can try posting a stripped down, zipped copy of your db. Unfortunately, I might have an issue opening a 2013 64 bit version. You might have to try creating an mdb if your version allows it. At this point, I haven't been able to replicate the problem at all, and it works as I figured it should.

    As for apologies and scolding: if you read a thousand posts in this forum, I doubt you will find any examples of that except for responses to one individual who asked for help then started slinging insults and name calling after one response. Everyone here who helps was a newbie at one time and it is fine (and perhaps helpful to us) to declare your level of knowledge. Just concentrate on answering as many questions that helpers may have and be as accurate and detailed as you can when explaining what you have, what you've tried and what you want. Many of us provide posting tips in our 'signatures'.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Cascading Combo - HELP!!!
    By ajh2014 in forum Forms
    Replies: 5
    Last Post: 10-17-2014, 06:20 AM
  2. Replies: 7
    Last Post: 08-21-2014, 11:41 PM
  3. Cascading Combo Box help
    By kspabo in forum Access
    Replies: 11
    Last Post: 06-27-2014, 01:00 PM
  4. Replies: 1
    Last Post: 02-27-2014, 03:43 PM
  5. Cascading Combo Box Help
    By euphoricdrop in forum Forms
    Replies: 3
    Last Post: 04-12-2011, 05:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums