Results 1 to 3 of 3
  1. #1
    Lounger
    Join Date
    Aug 2008
    Location
    Daytona Beach, Florida, USA
    Posts
    42
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Dlookup incorrect value

    I have a database I am using to maintain monthly schedules for various locations. I have a table with the shift schedules for each location and a schedule for that I would like the start and end time to automatically update after the shift is chosen using a macro. I have created the macro "Update Start Time" which is only giving 3:00 pm as the start time, which is incorrect according to the shift schedule table. I have attached a narrowed down copy of the database for review. Any help would be appreciated.
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts
    How and where are you using a macro to update a field?

    And anyway, your macro contains the WHERE clause "[Shift]=[Shift]", which evaluates to a true; so it is returning the first record in the table, which has a [Start] = 3:00pm.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I see that the macro is run from the After Update event from the shift field on the form Copy of Schedule.

    I can't work out the right syntax to use in the macro. (Sorry I don't write macros..nothing I tried worked.)

    Here is how I would do it.

    Add two extra columns to the Shift combo box, so that the Start and End times are displayed when you are selecting a Shift. Then use this code in the After Update event:

    Code:
    Private Sub Shift_AfterUpdate()
    Me.Start_Time = Me.Shift.Column(2)
    Me.End_Time = Me.Shift.Column(3)
    End Sub
    Regards
    John



Posting Permissions

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