Results 1 to 11 of 11
  1. #1
    Lounger
    Join Date
    Dec 2000
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Erase Contents of Field in Error (Access 2000 SR1)

    <P ID="del"><FONT SIZE=-1>Post deleted by CarolW</FONT>

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Erase Contents of Field in Error (Access 2000 SR1)

    Have you tried Me.Undo?
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Dec 2001
    Location
    Birmingham, Alabama USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Erase Contents of Field in Error (Access 2000 SR1)

    You should be able to add the two lines below before you cancel the event:

    Me.YourControlName.SetFocus
    Me.YourControlName.Undo
    Cancel=True '(or Cancel = -1 in your case)

    (change "YourControlName" to the actual name of your control)

    HTH
    RDH
    Ricky Hicks
    Microsoft MVP
    Birmingham, Alabama USA

  4. #4
    Star Lounger
    Join Date
    Dec 2001
    Location
    Birmingham, Alabama USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Erase Contents of Field in Error (Access 2000 SR1)

    Hmmmm ........

    Me.Undo will clear the complete entry .... I think the poster only wants to clear the single control.

    RDH
    Ricky Hicks
    Microsoft MVP
    Birmingham, Alabama USA

  5. #5
    Lounger
    Join Date
    Dec 2000
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Erase Contents of Field in Error (Access 2000 SR1)

    Thanks for the replies. Yes, that is correct, I only want to clear the control that is in error.

    Here are the results of testing using the suggestions:

    When I used the Me.ControlName.SetFocus
    (substituting my control name), I got an error message stating that the field needs to be saved in order to use a setfocus.

    When I used the Me.ControlName.Undo
    (also subsituting my control name), it erased the entire entry as well as that of the previous field. The field in error was at the second tab stop and it went back to and cleared the field at the first tab stop. This is not what I need. In this example, I would only need to clear the second control (the one that is in error).

    Thanks.

  6. #6
    Star Lounger
    Join Date
    Dec 2001
    Location
    Birmingham, Alabama USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Erase Contents of Field in Error (Access 2000 SR1)

    Are you using the Before Update event of the Control or the Form? It would help if you would post the complete SubRoutine you are using. This may fill in the holes needed to give the exact solution.

    RDH
    Ricky Hicks
    Microsoft MVP
    Birmingham, Alabama USA

  7. #7
    Lounger
    Join Date
    Dec 2000
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Erase Contents of Field in Error (Access 2000 SR1)

    I am using the Before Update event of the control. Here is the code. I have bolded the relevant part. Thanks.

    PS - I apologize, in advance, for the novice approach but I'm a VBA novice!
    ************************************************** *************************
    Private Sub frm_mealnum_BeforeUpdate(Cancel As Integer)

    Dim strfilter As String
    Dim mealdesc As Variant
    Dim pluspos As Integer
    Dim searchchar As String
    Dim mealsearch As String
    Dim plusposm1 As Integer
    Debug.Print "in 111 mealnum bu-mealnum = " & [frm-mealnum]
    If IsNull([frm-mealnum]) Then
    GoTo vis1
    End If

    searchchar = "+"
    pluspos = InStr(1, [frm-mealnum], searchchar)

    plusposm1 = pluspos - 1
    plusflag = "N"
    If pluspos > 0 Then
    mealsearch = Left([frm-mealnum], plusposm1)
    plusflag = "Y"
    GoTo bypoth
    Else
    mealsearch = [frm-mealnum]
    End If

    searchchar = "*"
    astpos = InStr(1, [frm-mealnum], searchchar)
    astposm1 = astpos - 1
    astflag = "N"
    If astpos > 0 Then
    mealsearch = Left([frm-mealnum], astposm1)
    astflag = "Y"
    GoTo bypoth
    Else
    mealsearch = [frm-mealnum]
    End If

    searchchar = "-"
    minpos = InStr(1, [frm-mealnum], searchchar)
    minposm1 = minpos - 1
    minflag = "N"
    If minpos > 0 Then
    mealsearch = Left([frm-mealnum], minposm1)
    minflag = "Y"
    GoTo bypoth
    Else
    mealsearch = [frm-mealnum]
    End If

    bypoth:
    strfilter = "[Meal number] = " & mealsearch

    mealdesc = DLookup("[Meal Description]", "Meal Table", strfilter)

    If IsNull(mealdesc) Then
    [frm-error-msg] = "Meal number " & mealsearch & " not found"
    [frm-Meal-desc] = " "
    Cancel = -1
    GoTo endrtn

    Else
    savemealdesc = mealdesc
    plusdesc = " "
    plusind = " "

    If pluspos > 0 Or astpos > 0 Then
    plusind = " + "
    strfilter = "[Control Table Constant] = ""CTRL"""
    mnforplus = DLookup("[Meal Number for Plus]", "Control Table", strfilter)
    strfilter = "[Meal number] = " & mnforplus
    plusdesc = DLookup("[Meal Description]", "Meal Table", strfilter)
    End If

    [frm-Meal-desc] = mealdesc & plusind & plusdesc
    [frm-error-msg] = " "
    [frm-Meal-desc].Visible = True

    ' === set next field visible and make not next field invisible ===
    vis1:
    If allx1 = "Y" Or minflag = "Y" Or astflag = "Y" Then
    Label17.Visible = True
    [frm-mealextrainfo].Visible = True
    Else
    Label17.Visible = False
    [frm-mealextrainfo].Visible = False
    Label20.Visible = True
    [frm-beveragenum].Visible = True
    End If
    End If

    endrtn:
    End Sub
    ************************************************** ****

  8. #8
    Star Lounger
    Join Date
    Dec 2001
    Location
    Birmingham, Alabama USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Erase Contents of Field in Error (Access 2000 SR1)

    Well ... that a lot of "nested" If statements. It is very hard for me to follow the logic behind your code. All of the "GoTo" statements were sending me all over the place.

    The response I gave in my earlier reply was using the "Forms On Before Update" event and not the controls event. I have looked at your code for several minutes and can not get a complete grasp on what all you are attempting to do in the routine.

    I normally validate the forms entries at "form level" instead of at control level. Maybe now that you have posted the code you are using ... someone can come up with a reasonable solution.

    Sorry I could not be more help.

    RDH
    Ricky Hicks
    Microsoft MVP
    Birmingham, Alabama USA

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Erase Contents of Field in Error (Access 2000 SR1)

    I don't normally delete the contents of the field when I find an error so I don't normally run into exactly your problem, but try sticking me.repaint before your goto endrtn statement.

    It's purely a matter of taste, but I use the msgbox function for error messages rather than setting a field. It makes the message painfully obvious and you're guaranteed to see the message when the code hits the msgbox (i.e. no hassle with making sure you do the proper contortions to make the message appear when it's supposed to).

  10. #10
    Lounger
    Join Date
    Dec 2000
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Erase Contents of Field in Error (Access 2000 SR1)

    Douglas:

    Thanks for the reply.

    Me.repaint did not work. The contents of the field stayed at the invalid value. I believe that I had tried this solution before I posted my question (I had tried so many things that I may have easily forgotten).

    I even tried a Sendkeys "{BKSP}" for the number of characters entered but that seemed to trigger another execution of Before Update with nulls in the field. I want the field to be empty but I don't want the Before Update to trigger again.

    Re the use of msgbox : This application is replicating a 20 year old DOS application. The client has requested that we stick to keyboard functionality! Sounds archaic, I know, but that's what he wants.

    Any other thoughts on my problem would be appreciated. Thanks.

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Erase Contents of Field in Error (Access 2000 SR1)

    If you're serious about VBA, learn to do it right and *don't* use gotos. Theyre holdovers from old linear programming and make code nearly impossible to maintain with any confidence. You DO find them in old programs, but part of the job of converting same is to figure out a better way to do it, not just to do it the old way in a new language. Believe me, I sympathize. I've converted plenty of old dBASE apps to Access, and I've had to wade through FORTRAN and VAX programs for the same purpose. Yuck!

    First, what is this the BeforeUpdate event of? I've never seen a beforeUpdate event named like this except when using WithEvents to trap events for a form using automation. The normal BeforeUpdate event for the current form is named Form_BeforeUpdate, so I suspect this isn't it.

    Second, why aren't you using the BeforeUpdate event of the *control*? It isn't necessary to use the BeforeUpdate of the whole form. All you need in the BeforeUpdate event of the control is something like this (where <controlname> represents the name of the control:

    <pre>If <whatever returns a boolean True> Then
    Me.<controlname>.Undo
    Cancel = True
    End If</pre>


    That will undo any changes to the control and cancel the update, leaving the cursor positioned in that control. It will NOT change any other control in the record.
    Charlotte

Posting Permissions

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