Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2014
    Posts
    4

    Question Unanswered: Form Controls, Triggers & User Interface

    Hello all,
    I'm struggling with adding some codes to my 3 existing data entry forms to control their behavior, to limit what users can do, and to avoid duplicate records. I would appreciate any help.

    I've unsuccessfully tried using various codes, including the one below, either as Before Update, After Update, or On Enter for various form controls:

    Private Sub EmployeeID_FK_Enter()

    Dim varEmployeeID_FK As Variant
    Dim ingEmployeeID_FK As Long

    varEmployeeID_FK = DLookup("[EmployeeID_FK]", "LoanT", "[CheckOutDateTime]" Is notnull And "[CheckInDateTime]" = Null And "[EmployeeID_FK]" = EmployeeID_FK)
    If IsNull(varEmployeeID_FK) Then

    Call MsgBox("There is no existing record.", vbOKOnly)

    Else
    Call MsgBox("There is an existing check-out record. Please simply update that by time stamping the Check In Date/Time field. Do not add/create a separate check-in record.", vbOKOnly)

    End If
    End Sub

    This is a very basic db for checking documents in & out of a file room, very similar to a library. I wanted the Dlookup to check my LoanTable and see if there is an existing record for this employee (or document) with a time stamp in checkoutdatetime field, but no entry in checkindatetime field.

    As it is, users may use the search or Find Record button on my Loan Form and find such a record, but they can still either chose to or by accident use Add Record button and create a new record to check in a document that has been on loan. Even though what they should do is to update the existing record. By making that mistake, they create a 2nd line/record just for checkin. That affects my other queries and reports. So, I need to fix/control this at form level.

    Thank you.
    FYaz

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Change the criteria:
    Code:
     "[CheckOutDateTime] Is Not Null And [CheckInDateTime] Is Null And  [EmployeeID_FK] = " & EmployeeID_FK)
    If EmployeeID_FK is not numeric, use:
    Code:
    [EmployeeID_FK] = '" & EmployeeID_FK & "'")
    Have a nice day!

  3. #3
    Join Date
    Sep 2014
    Posts
    4

    Thank you

    Hi,
    Thank you for your help.

    The EmployeeID_FK field has a text value. The IDs of course are numeric, but I won't be using them in any calculations. So, they have a text value. In this case, I wasn't sure which one your codes I have to use. I tried both. They still give me error msg. When I use the first one, it says: "compile error, expecting separators or )". When I use the 2nd one, it says: "compile error, expected expression".

    Am I using the codes for the right event? Or should I use it as Before or After Update?

    Thanks. Have a great day.
    Best,
    Farshid

  4. #4
    Join Date
    Sep 2014
    Posts
    4

    Some progress

    Thank you. :-)
    I'm adding this code to my Loan form for the Employee ID field or control. There is a Document No. field on the same form
    and I plan to add a similar code (below) for that too, after I'm sure this code is working.


    Private Sub DocumentNum_FK_BeforeUpdate(Cancel As Integer)

    Dim varDocumentNum_FK As Variant
    Dim ingDocumentNum_FK As Long

    varDocumentNum_FK = DLookup ("[DocumentNum_FK]", "LoanT", "[CheckOutDateTime] Is Not Null And [CheckInDateTime] Is Null And [DocumentNum_FK] = '" & DocumentNum_FK & "'")

    If IsNull(varDocumentNum_FK) Then

    Call MsgBox("There is no existing record that can be updated for check-in.", vbOKOnly)

    Else
    Call MsgBox("There is an existing check-out record. Please simply update that by time stamping the Check In Date/Time field. Do not add/create a separate check-in record.", vbOKOnly)

    End If

    End Sub


    The good news is that when I used the code for EmployeeID field under On Enter Event, it worked and prompted user with both msg boxes that are included. Then based on your previous email, I changed that and used them for Before Update Event. But now I don't get any msg boxes or response. So, should I go back to On Enter Event? I want this trigger to prompt users with msg boxes when they enter a Employee ID or Document No. to search for records.

    -FYaz

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •