troubleshooting Question

VBA select query MS Access

Avatar of ITDguy
ITDguyFlag for United States of America asked on
Microsoft AccessSQL
9 Comments1 Solution3263 ViewsLast Modified:
I am running a select query in vba to check an input control against the table field it fills for duplicates. everything works, but the query runs every time. The code is in a beforeupdate.
For some reason the condition in the if statement isn't working correctly.
Private Sub txtROLNfather_BeforeUpdate(Cancel As Integer)
Dim mResponse As Integer
Dim mySql As String
Dim sWhere As String
  If (Me.txtROLNfather) = [myTable.ROFLname] Then
       mResponse = MsgBox("Entries With The Same Last Name Detected! YES to View or NO to continue", vbYesNo)
        If mResponse = vbYes Then
            Dim db As DAO.Database
            Dim qdf As DAO.QueryDef
            mySql = "SELECT myTable.ROFLname, Cases.ROGname, Cases.txtName FROM myTable "
            sWhere = "WHERE myTable.ROFLname = Forms!frmName.txtROLNfather"
            mySql = mySql & sWhere
            Set db = CurrentDb
            Set qdf = db.QueryDefs("qryProject")
            qdf.SQL = mySql
            DoCmd.OpenQuery "qryProject", acViewNormal
            Exit Sub
        End If
   End If
 End Sub
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 9 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros