When employee is made inactive and leave date is entered the position needs to become available in the Fills screen and status is Open RRS feed

  • Question

  •     Hello, 

    I am a newbie to VBA access, hopefully you can help me here...

    Private Sub Active__Inactive_AfterUpdate()

    If Me.Active__Inactive = "Inactive" Then
       If Not IsNull(Me.Leave_Date) And Me.Leave_Date <> "" Then
       'Populate fills table with all of this position
        Dim str As String
        str = "INSERT INTO tbl_GCDS_Operations_Positions_fills ([Name],[Position],[Reporting Level 1],[Group],[Location], [Leave date]) _
              SELECT ([Name],[Position],[Reporting Level 1],[Group],[Location],[Leave date] _
              FROM [tbl_Register_MemberList] Where [Position] = '" & Me.[Position] & "';"
        db.Execute str, dbFailOnError
       End If
        MsgBox "Please make sure you have entered a Leave Date otherwise this position will no appear in the Postion Fills File"
    End If

    End Sub

    Tuesday, October 17, 2017 4:05 PM

All replies

  • Hi,

    What exactly is the problem? If you're code is not working, what error message are you getting, if any?

    Tuesday, October 17, 2017 4:19 PM
  • If the Leave_Date column is of date/time data type, as would be expected, it cannot be a zero-length string.  It can only contain a valid date/time value or be NULL.  You can delete And Me.Leave_Date <> "" from the If statement therefore.

    Ken Sheridan, Stafford, England

    Tuesday, October 17, 2017 5:45 PM
  • Hello,

    Thank you for replying...first, it is not tranfer the data of the employee that is inactive from tbl_Register_MemberList to tbl_GCDS_Operations_Positions_fills and second it's giving an error: db.Execute str, dbFailOnError = 128


    Tuesday, October 17, 2017 6:00 PM
  • Try the DoCmd.RunSQL command instead of db.Execute.

    Tuesday, October 17, 2017 7:39 PM