Results 1 to 10 of 10
  1. #1
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    After Update Error (2002)

    Hi,

    I have a subform on a form that is working fine with the exception of a piece of code that is giving me issues. On the afer update event for [status date] I havwe the following code:

    'Checks to be sure the new status date is not prior to the existing status date

    If Me.Status_Date.Value < Forms!frmfacesheet!txtStatusDate Then
    MsgBox "The date you are trying to enter preceeds the current status date. Please enter a date that is later than the present status date."
    Me.Status_Date.SetFocus
    Me.Status_Date = Null

    End If

    My problem is that no matter what date is entered, the message box comes up. I am using similar code in the same event to check against a different date and it runs fine. That code is:

    'Checks to be sure DC transfer or hold dates are not prior to soc date

    If Me.Status_Date.Value < Forms!frmfacesheet!SOC Then
    MsgBox "The date you are trying to enter preceeds the SOC date. Please enter a date that is later than the SOC date."
    Me.Status_Date.SetFocus
    Me.Status_Date = Null

    End If

    All fields are set as short date format. I've tried putting the code in the click event of a save button (prior to the save code) but the same thing happens. I'm not sure what I'm missing and/or why it would work for one piece of code that is almost identical but another.

    Thanks!
    Leesha

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

    Re: After Update Error (2002)

    I would use the Before Update event instead of the After Update event. The Before Update event procedure has an argument Cancel. If you set this to True, the user can't leave the control; you won't have to set the focus back to it:

    If Me.Status_Date.Value < Forms!frmfacesheet!txtStatusDate Then
    MsgBox "The date you are trying to enter preceeds the current status date. Please enter a date that is later than the present status date."
    Me.Status_Date = Null
    Cancel = True
    End If

    See if that helps. If not, click in the line I Me.Status_Date.Value ... and press F9 to set a breakpoint. When you open the form, enter a date and press Tab, the code will pause on the line with the breakpoint. You can proceed one step at a time by pressing F8. You can hover the mouse pointer over variables and control names to see their value. This may help you to pinpoint the cause of the problem.

  3. #3
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: After Update Error (2002)

    Hi Hans,

    First, thanks for the explanation on why to use the before update event as well as how to step though stuff. The bright side of having problems is that I'm forever learning something new!

    The code still doesn't work. I tried hovering the curser as you suggested. When I hovered over me.status_date is showed the date as beign = 5/12/05. When I hovered it over frmFaceSheet!txtStatusDate is had quote marks around the date ..........."5/12/05". The date format for both fields is as short date. I'm assuming ( pretty bold of me) that the quotes on one and not on the other has something to do with the issue? When I went back to the "similar" code that is working correctly and hovered the curser, it works because it alerts if [SOC] is null, not because its comparing dates.

    In case it helps to know, txtStatusDate comes from a date that is in a list. The form has lstName and and unbound text box with the data set to =lstName.column.2. I have that box set to short date.

    Leesha

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

    Re: After Update Error (2002)

    Thanks, that is relevant information. Since txtStatusDate is derived from a column in a list box, it is seen as text, even though you formatted the column as a date. Try the following:

    If Me.Status_Date < CDate(Forms!frmFaceSheet!txtStatusDate) Then

    The CDate function converts txtStatusDate into a date value. Note: if txtStatusDate can be blank, you'll encounter another problem, but that can be solved by building in an extra check.

  5. #5
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: After Update Error (2002)

    Well that worked beautifully and I've added that cdate info to my growing list of Hans hints! To answer your question, the field can be null and I tested it and sure enough I get an error stating invalid use of null. After a little bit or work and testing I'm fixed that as well. Thank you soooooooooo much!

    Leesha

  6. #6
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: After Update Error (2002)

    Hi Hans, back again.

    How would I write this code to be sure that the format of the date comes up as short date format. The status_date field in tblStatusTrending is set to short date format. However the date keeps coming up as general date format. I need it to be short date format for this piece of code. Is this even possible? I don't understand why its coming up as a general date.

    Thanks,
    Leesha

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

    Re: After Update Error (2002)

    Which control on which form are we talking about now? txtStatusDate on frmFaceSheet or Status Date on the subform you mentioned in the first post in this thread?

  8. #8
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: After Update Error (2002)

    Sorry Hans. It would be txtStatusDate on frmFaceSheet. I didn't know if there was a way to write Cdate(frmFaceSheet.................. to format it to to short date vs general date as it appears now.

    Thanks,
    Leesha

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

    Re: After Update Error (2002)

    Try setting the Control Source property of txtStatusDate to

    <code>=Format([lstName].[Column](2),"Short Date")</code>

  10. #10
    Silver Lounger
    Join Date
    Jan 2002
    Posts
    1,615
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: After Update Error (2002)

    That's perfect! I had no idea you could do that in the control source format itself!

    Thanks Hans,
    Leesha

Posting Permissions

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