Results 1 to 6 of 6
  1. #1
    Join Date
    May 2011
    Posts
    8

    Unanswered: Look Up before Update

    Hi. Thanks for all that will respond to this post

    I have a small program to design for a company.

    They have thier staff in different places and they engage in nation wide postings of their staff sometimes.

    I designed a table with the following features:

    1. EmployeeNumber

    2. LocationPostedTo

    3. Name

    Note: their are other information but what is applicable for my question is what I state here.

    Please, I want some body to direct me on what to do on the form I created concerning this table.

    This is my challenge

    1. I call up the employee number though a combo box I designed. It looks through the and apply in the employee name.

    2. I call up the LocationPostedTo combo box and pick the new location the employee is to be posted to.

    Now, I want that before the column is updated, the form should look through the table and check whether this employee has been posted to such location before, and if yes, it should pop up telling me that this employee has been posted to such place before, therefore cannot be posted to such a place again.

    I hope I make myself understood.

    Thanks

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    So the staff members are never welcome in the same place twice? Well isn't that embarrassing!

    You'll need to use the DCount() Function for this, but we'll need to know the Datatype for both Fields as the syntax is slightly different for each.

    I assume that LocationPostedTo is defined as Text; is that correct?

    Is EmployeeNumber defined as Text or as a Number?

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    May 2011
    Posts
    8

    re-lookup before update

    yes, they are all text. thanks

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    There's a couple of ways to do this, but this will work:
    Code:
    Private Sub cboLocationPostedTo_AfterUpdate()
    
    If Nz(Me.EmployeeNumber, "") <> "" Then
    
    If DCount("*", "PostingTable", "EmployeeNumber & LocationPostedTo = '" & Me.EmployeeNumber & Me.cboLocationPostedTo & "'") > 0 Then
      MsgBox "This Employee Has Been Posted to " & Me.LocationPostedTo & " Before!"
      Me.cboLocationPostedTo = Null
    End If
    
    Else
     
     MsgBox "You Must Select an Employee Number Before Selecting a Location!"
     Me.cboLocationPostedTo = Null
     EmployeeNumber.SetFocus
    
    End If
    
    End Sub
    The above code assumes that

    • The Table holding your data is named PostingTable
    • The Field in the Table is named EmployeeNumber
    • The Field in the Table is named LocationPostedTo
    • The Control on the Form is named EmployeeNumber
    • The Combobox for selecting a location is named cboLocationPostedTo

    You'll need too change any of these names that aren't correct and replace them with your actual names.

    The names in Red are the Fields, while those in Blue are the Controls.
    "EmployeeNumber & LocationPostedTo = '" & Me.EmployeeNumber & Me.cboLocationPostedTo & "'"

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    May 2011
    Posts
    8

    re-lookup before update

    Thank you very much.
    ur code worked real well for me
    I appreciate and wish you well in all ur endevors
    Engr. Clement

  6. #6
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Glad we could help!

    Good luck with your project!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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