Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84

    apostrophe misery


    My main form my Db serves to have a user enter contact information. After the main form has been completed, the user can click on a button to make a subform visible so they can add additional information. I am using that click action to check the underlying table for duplicate names. The following code works fine unless there is an apostrophe in the name. I have tried every iteration I can think of including attempts to use replace(). I tried chr(34) as well and in that circumstance the line of code doesn't error out, it doesn't appear to do anything and I can enter a duplicate name and continue filling out the form(normally, if you enter a duplicate name, the undo line clears the form and you have to start over.)

    Code:
    Private Sub CmdAdd_Click()On Error GoTo Err_CmdAdd_Click
    
    
    If DCount("*", "Contact_Info", "[Last_Name]='" & Me.Last_Name & "' And [First_Name] = '" & Me.First_Name & "") > 0 Then
     msgbox "This contact already exists!", vbOK, "Duplicate Entry!"
        Me.Undo
           
        Exit Sub
    For example, if I have the contact Nancy O'Dell already in the table and then enter it again in my form, I get the error shown on the attached image.



    Thanks for any help you can give.
    Attached Thumbnails Attached Thumbnails Capture.PNG  

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    The chr(34) should work; how did you use it? It would get concatenated into the string.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Wouldn't this be better/easier if the name(s) were displayed in combo boxes? Not in the list = not in the table.
    Another way around it IMO would be to use a recordset based on a query, even if the sql was vba constructed. A query will handle the apostrophe no problem. To get around this with DLookup may be possible, but I've never stayed with it long enough to figure out a working syntax. I'll give it a bit of a go, though.

    EDIT: or Chr(39)?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This appears to work with Red's assigned to the variable strWord:
    DCount("*", "fieldName", "tableName=""" & strWord & """")

  5. #5
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    Thank you for the prompt responses!

    The chr(34) should work; how did you use it? It would get concatenated into the string.
    This gives me a type mismatch error, as does using chr(39):
    Code:
    Private Sub CmdAdd_Click()On Error GoTo Err_CmdAdd_Click
    
    
    
    
    If DCount("*", "Contact_Info", "Last_Name= " & Chr(34) & Me.Last_Name & Chr(34) And "First_Name=" & Chr(34) & Me.First_Name & Chr(34)) > 0 Then
     msgbox "This contact already exists!", vbOK, "Duplicate Entry!"
        Me.Undo
           
        Exit Sub

    Wouldn't this be better/easier if the name(s) were displayed in combo boxes? Not in the list = not in the table.
    I have done this before in other databases I have created so I wanted to expand my knowledge a bit and try something harder... maybe too hard??


    Quote Originally Posted by Micron View Post
    This appears to work with Red's assigned to the variable strWord:
    DCount("*", "fieldName", "tableName=""" & strWord & """")
    The issue appears to be when I try to concatenate the two criteria into a single Dcount statement. I need to do this so that Contacts with the same last name but different first names are accepted but Contacts with the same first name and the same last name trigger clearing the form.

    This works (but only on the last name):

    Code:
    Private Sub CmdAdd_Click()On Error GoTo Err_CmdAdd_Click
    
    
    Dim strwordF As String
    Dim strwordL As String
    
    
    strwordF = Me.First_Name.Value
    
    
    strwordL = Me.Last_Name.Value
    
    
    
    
    If DCount("*", "Contact_Info", "Last_Name=""" & strwordL & """" ) > 0 Then
     msgbox "This contact already exists!", vbOK, "Duplicate Entry!"
        Me.Undo
           
        Exit Sub
    This fails by giving me a Type mismatch error:

    Code:
    Private Sub CmdAdd_Click()On Error GoTo Err_CmdAdd_Click
    
    
    Dim strwordF As String
    Dim strwordL As String
    
    
    strwordF = Me.First_Name.Value
    
    
    strwordL = Me.Last_Name.Value
    
    
    
    
    If DCount("*", "Contact_Info", "Last_Name=""" & strwordL & """" And "First_Name= """ & strwordF & """") > 0 Then
     msgbox "This contact already exists!", vbOK, "Duplicate Entry!"
        Me.Undo
           
        Exit Sub

  6. #6
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    After I posted this I had an idea and I think I solved my problem. This is the entire procedure:
    Code:
    Option Explicit
    Private Sub CmdAdd_Click()
    On Error GoTo Err_CmdAdd_Click
    
    
    Dim strwordF As String
    Dim strwordL As String
    
    
    strwordF = Me.First_Name
    
    
    strwordL = Me.Last_Name
    
    
    
    
    If DCount("*", "Contact_Info", "Last_Name= """ & strwordL & """") > 0 Then
      If DCount("*", "contact_Info", "First_Name= """ & strwordF & """") > 0 Then
         msgbox "This contact already exists!", vbOK, "Duplicate Entry!"
         Me.Undo
         Exit Sub
      Else
        Cmdinvisible.Visible = True
        Add_Contribution_SubForm.Visible = True
        Forms!New_Contact_entry!Add_Contribution_SubForm.SetFocus
        DoCmd.GoToControl ("date")
      End If
    
    
    Else
      Cmdinvisible.Visible = True
      Add_Contribution_SubForm.Visible = True
      Forms!New_Contact_entry!Add_Contribution_SubForm.SetFocus
      DoCmd.GoToControl ("date")
    
    
    End If
    
    
    Exit_Err_CmdAdd_Click:
    Exit Sub
    
    
    Err_CmdAdd_Click:
    msgbox Err.Description
    Resume Exit_Err_CmdAdd_Click
    
    
    End Sub

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Really, DCount is the wrong function for this AFAIC. You might make it work eventually, but it's not worth the effort. A simple select query handles multiple criteria much easier than these functions.

  8. #8
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    I've run through my code on 6 test entries and it does work; your comment makes me worry that it might not be stable and more prone to errors than an SQL query would be. I will try to create an SQL query (in VBA) and post tomorrow what I come up with.

    Thanks

  9. #9
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You quotes and concatenation are off with the attempt using Chr(34), should be:

    DCount("*", "Contact_Info", "Last_Name= " & Chr(34) & Me.Last_Name & Chr(34) & " And First_Name=" & Chr(34) & Me.First_Name & Chr(34))
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  10. #10
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    Really, DCount is the wrong function for this AFAIC. You might make it work eventually, but it's not worth the effort. A simple select query handles multiple criteria much easier than these functions.
    So I am trying to follow your advice and make a select query in VBA to perform the same way as my Dcount function but I keep getting an error. I created a simple stored query of all the fields in my contact info table and named it contact info query. The code never executes to that line, however, always giving me an error at the line DoCmd.runsql strsql

    Here is my code:
    Code:
    Dim db As DAO.DatabaseDim qdf As DAO.QueryDef
    Dim strSQL As String
    
    
    Set db = CurrentDb
    Set qdf = db.QueryDefs("contact_info_query")
    
    
    strwordF = Me.First_Name.Value
    strwordL = Me.Last_Name.Value
    
    
    strSQL = "SELECT [First_Name],[Last_Name] FROM [Contact_Info] WHERE [First_Name]= ' & strwordF & ' and [Last_name]= ' & strwordL & ';"
    DoCmd.RunSQL strSQL
    
    
    'qdf.SQL = strSQL
    'DoCmd.OpenQuery "contact_info_query"
    The error I get is runtime error 2342 " A runsql action requires an argument of a SQL statement."

    As far As I can tell, my SQL statement is correct.
    Now what?

    Thanks

  11. #11
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Code:
    strSQL = "SELECT [First_Name],[Last_Name] FROM [Contact_Info] WHERE [First_Name]= '" & strwordF & "' and [Last_name]= '" & strwordL & "';"

  12. #12
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    That change gets me a syntax error missing Operator (if there is an apostrophe in the name)...

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can't use RunSQL on a regular SELECT query, only action queries. You'd have to open a recordset on your SQL, if you don't want to use the DCount().
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  14. #14
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Change each single quote to two double-quotes - per http://allenbrowne.com/casu-17.html

  15. #15
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    Fantastic, the 2 double quotes worked.


    You can't use RunSQL on a regular SELECT query, only action queries. You'd have to open a recordset on your SQL, if you don't want to use the DCount().
    I figured that out last night after I posted. I have changed the code to an openquery and that works.
    Code:
    strSQL = "SELECT [First_Name],[Last_Name] FROM [Contact_Info] WHERE [First_Name]= """ & strwordF & """ And [Last_name]= """ & strwordL & """;"
    Set qdf = db.CreateQueryDef("contact_info_query", strSQL)
    DoCmd.OpenQuery "contact_info_query"

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Error 3077 - use of apostrophe
    By dotcanada in forum Access
    Replies: 6
    Last Post: 08-11-2016, 12:21 PM
  2. Issues using apostrophe
    By rosscortb in forum Access
    Replies: 5
    Last Post: 02-05-2015, 11:34 AM
  3. me.filter using 2 strings - problem with apostrophe.
    By wackywoo105 in forum Programming
    Replies: 3
    Last Post: 05-07-2014, 03:41 PM
  4. Replies: 2
    Last Post: 04-14-2014, 10:42 PM
  5. Apostrophe in name
    By NISMOJim in forum Programming
    Replies: 1
    Last Post: 04-04-2013, 10:14 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