Page 2 of 2 FirstFirst 12
Results 16 to 17 of 17
  1. #16
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    As mentioned in post #13 RunSql is only for action queries. For a simple stored select query, I'd do something like this if you only need to know there is at least one matching record. If you need to know the count, MoveLast before getting the recordset count. If you need all the records because you're going to step through them, you can do that too. So you have at least 3 ways you can use the recordset.

    Dim rs As DAO.Recordset

    Set rs = CurrentDb.OpenRecordset("contact_info_query")
    If rs.RecordCount <> 0 Then.... ' will be 1 if there is at least one record
    'or to get the full count


    rs.MoveLast
    Msgbox rs.RecordCount

    A solution that employs OpenQuery will display the results in a new window and I'm pretty sure there is nothing you can do about that.
    Last edited by Micron; 03-23-2017 at 02:44 PM. Reason: added info

  2. #17
    dgmdvm is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2014
    Posts
    84
    I did use open query and noticed that my form loses focus; that makes clearing the form a problem if is is a duplicate. By replacing "Me.undo" with "DoCmd.RunCommand acCmdUndo" I am able to open and close the query and return to the form so that I can clear it without saving the duplicate data. It works correctly.
    Here's the code so the next person that needs help can find it.
    Code:
    Set db = CurrentDbstrwordF = Me.First_Name
    strwordL = Me.Last_Name
     
       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"
    
    
       strcount = "Select Count (Last_Name) AS CountofLast_Name FROM contact_info_query;"
       Set rs = db.OpenRecordset(strcount)
       x = rs.Fields(0) 
    
    
       DoCmd.Close acQuery, "contact_info_query", acSaveNo
       db.QueryDefs.Delete ("contact_info_query")
    
    
       If x < 1 Then
         Forms!New_Contact_Entry.SetFocus
         Cmdinvisible.Visible = True
         Add_Contribution_SubForm.Visible = True
         Forms!New_Contact_Entry!Add_Contribution_SubForm.SetFocus
         DoCmd.GoToControl ("date")
      Else
         Forms!New_Contact_Entry.SetFocus
         msgbox "This contact already exists!", vbOK, "Duplicate Entry!"
         DoCmd.RunCommand acCmdUndo
         Exit Sub
                           
      End If
    I think I will try Micron's new information too to see if it gives a cleaner result. Thanks for all the help.

Page 2 of 2 FirstFirst 12
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