Results 1 to 10 of 10

Thread: DLookUp (AXP)

  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DLookUp (AXP)

    New employees are assigned a unique employee ID number (lngEmpId as long) in a tblNewHires table, and a unique system ID (strSysId as string) in a tblNewSystemUser table via a frmNewHires. The last step in the hire process is to add the tblNewEmployees records to the tblEmployees and add the tblNewSystemUser records to the tblSysUesrs table. I need to check the tblEmployees table and the tblSysUsers to be sure the unique numbers being assigned have not already been assigned to someone else. I do this:

    'check employee Id number for duplicates in the main employees table

    Private Sub EmpId_AfterUpdate()
    On Error GoTo Err_EmpId_AfterUpdate

    Dim blnEmpId As Boolean
    Dim lngNewEmpId As Long

    lngNewEmpId = Me.EmpId.Value

    blnEmpId = DLookup([EmpId], "tblEmployee", "[EmpId]=" _
    & lngNewEmpId)
    If blnEmpId = True Then
    MsgBox "This Employee Id number is already assigned. Please assign an unused employee id number for this employee.", vbOKOnly, "Invalid Entry"
    Me.EmpId.SetFocus
    End If

    Exit_EmpId_AfterUpdate:
    Exit Sub

    Err_EmpId_AfterUpdate:
    Resume Exit_EmpId_AfterUpdate
    End Sub

    This seems to work fine for the employee ID number. (although there is probably a way to do this with a little less code.)
    However, when I use the same code to test the SysId field, I get an error that says "You cancelled the previous operation".
    Does the datatype of the (strSysId as string) have anything to do with this or is there some other problem?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DLookUp (AXP)

    It's probably a type conflict: you try to assign a string or Null value to a boolean. Try

    blnSysId = Not IsNull(DLookup([SysId], "tblSysUsers", "[SysId]=" & Chr(34) strSysId & Chr(34)))

    Also note the use of Chr(34) to enclose strSysId in quotes.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookUp (AXP)

    Try using the before_update event instead of after_update event. You want to cancel the update if the id is already in use.

  4. #4
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookUp (AXP)

    Still getting the same error but I did move the msgbox to the Err_SysId_AfterUpdate: and now, at least, I get my message to display instead of the system message. However, it is apparent that the error occurs before the DLookup has completed. When I test with a SysId that is already in use, it jumps to the error handler and never turns the bln to TRUE.

  5. #5
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookUp (AXP)

    Thanks for the pointer.
    I am using the After Update event of the textbox where the user enters the SysID because I want them to get the message at the time of data entry. The final step of appending the new employee stuff to the current employee table comes after HR reviews all the info and clicks a "Save Record" command button. It is my hope that by then, all the information will have been validated and corrected if necessary.
    Thanks again,

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DLookUp (AXP)

    For testing, comment out the On Error Goto ... instruction, and put a breakpoint at the beginning of the procedure. Then, you can single step through the code and see where the error occurs, and inspect the value of variables at that point.

    But maybe a different approach would simplify things: why not scrap the tblNewEmployees and tblNewSysUsers tables, and add the records directly to tblEmployees and tblSysUsers. I assume they have a aunique index on EmpID and SysID, respectively, so you wouldn't be able to add a record with a duplicate ID. To distinguish new records from existing records, you could add a Boolean field, say NewRec, with a default value of True. Filter the standard reports etc. for NewRec=False, so that only new records are excluded. When HR OK's the new record, it can set NewRec to False.

  7. #7
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookUp (AXP)

    Sorry if I wasn't clear I meant the before_update of the control rather than the after_update. This way you can set the Cancel argument to True if the ID already exists which will cancel the update and even issue a docmd.undo if you want to remove what the user entered.

  8. #8
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookUp (AXP)

    Got it. This works better than after update. Thanks!

  9. #9
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Virginia, USA
    Posts
    127
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DLookUp (AXP)

    Single table works much better. By turning on a "NewEmployee" yes/no field in each record, we differentiate between pending new employees and current employees. Active employees are identified as those whose termination date is null.

    A question about efficiency: When DLookUp is run from a front end, does it return an entire table from the back end over the network and then do the filtering for the record? Or is filtering done on back end and only the selected record sent across the network line? Is it more efficient in a split db model to have DLookUp or maybe a combo box who's rowsource is a query?

    Thanks,

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: DLookUp (AXP)

    If your backend is Access, all processing will take place in the frontend database, because the backend database is just a passive data storage. If your backend is SQL Server, it might be more efficient to use passthrough queries.

Posting Permissions

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