Find Repeated IDs using VBA

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • Lou699
    New Member
    • Nov 2020
    • 11

    #1

    Find Repeated IDs using VBA

    Hey guys,

    I'm creating a db to monitor employees vacations, I created a table to record it and then a tabular form to preview all the records.

    Then I created a txt box to check of there is an employee takes multiple vacations.

    So I need a VBA code that will be assigned to Txtbx1 so that Afterupdating the text box with numbers for example 5, the form will show the employees that have taken 5 or more vacations.

    Filter will be applied on EMPID column.

    Thanks.
  • Lou699
    New Member
    • Nov 2020
    • 11

    #2
    I've tried to run SQL command as following but it didn't work

    Code:
    Private sub txtbx1 Afterupdate()
    Dim SQL As String
    SQL = SELECT EMPID, COUNT(*) 
    FROM table 
    GROUP By EMPID 
    HAVING COUNT(*) > '" & me.txtbx1 & "'"
    DoCmd.RunSQL SQL 
    End sub

    Comment

    • isladogs
      Recognized Expert Moderator Contributor
      • Jul 2007
      • 483

      #3
      You have started yet another thread on the same topic as before!

      First of all as your textbox contains a number, you don't want text delimiters.

      Secondly you cannot run or execute a SQL SELECT statement.

      However, there are several approaches you can use. For example:

      1. create a saved query (referencing the form name & control) and run that in your after update prcedure

      qryCountEmpID
      SELECT EMPID, COUNT(*) AS CountEmpID
      FROM table
      GROUP By EMPID
      HAVING COUNT(*) > " & Forms!FormName. txtbx1 & "

      Code:
      Private sub txtbx1 Afterupdate()
      
      DoCmd.OpenQuery "qryCountEmpID"
      
      End Sub
      2. use a query def

      Code:
      Private sub txtbx1 Afterupdate()
      Dim db As dao.Database
      Dim qdef As dao.QueryDef
      Dim strSQL As String
      Set db = CurrentDb
      
      strSQL = "SELECT EMPID, COUNT(*) AS CountEmpID  FROM table GROUP By EMPID HAVING COUNT(*) > " & Forms!FormName.txtbx1 & ";"
      
      Set qdef = db.CreateQueryDef("qryCountEmpID", strSQL)
      qdef.Close
      Set qdef = Nothing
      Set db = Nothing
      DoCmd.OpenQuery "qryCountEmpID", acViewNormal
      
      End Sub
      Last edited by isladogs; Nov 29 '20, 02:37 PM. Reason: Corrections and added info

      Comment

      Working...