Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to Trim correctly (A2K)

    Once more do I mess up my code.

    The following works perfectly as the data in the [cbo_RA_No] is a text field.

    Private Sub cbo_RA_No_Exit(Cancel As Integer)
    Dim intButType As Integer
    Dim strMsgPrompt As String, strMsgTitle As String
    If Trim(Nz([cbo_RA_No])) = "" Then
    strMsgPrompt = "You must enter an RA # in order to " & vbCrLf & _
    "to continue entering data" & vbCrLf & _
    " for this Item"
    strMsgTitle = "No RA # Retrieved!!"
    intButType = vbExclamation
    MsgBox strMsgPrompt, intButType, strMsgTitle
    Cancel = True
    End If
    End Sub

    When I try the following where [cbo_UserId] is numeric, it ignores the error condition. And I know the answer will be simple. And I have tried a few variations.And I think it's the "If Trim"
    Cheers,
    Andy

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

    Re: How to Trim correctly (A2K)

    Your code doesn't use cbo_UserId. I'll assume that you meant to use cbo_UserId instead of cbo_RA in the second piece of code. Numeric values are not strings, therefore they are not "". You can test if the value is Null instead; you don't need to trim (Trim is for strings):

    If IsNull([cbo_UserId]) Then
    MsgBox "Mumble mumble mumble"
    ...
    End If

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Trim correctly (A2K)

    Hans, the thick plottens.

    Your suggested code worked of course, but, not quite in this situation

    First, I have stripped all fields except for txt_UserId and a description field so that I have somewhere to tab to, and all code from this form except for the following:

    Option Compare Database
    Option Explicit

    Private Sub txt_UserId_Exit(Cancel As Integer)
    Dim intButType As Integer
    Dim strMsgPrompt As String, strMsgTitle As String
    If IsNull([txt_UserId]) Then
    strMsgPrompt = "You must enter your User Id in order to " & vbCrLf & _
    "to continue entering data" & vbCrLf & _
    " for this Item"
    strMsgTitle = "No Id!! No Entry!!"
    intButType = vbExclamation
    MsgBox strMsgPrompt, intButType, strMsgTitle
    Cancel = True
    End If
    End Sub

    Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeyF10 Then
    On Error GoTo Err_AddNewRecord_Click
    DoCmd.GoToRecord , , acNewRec
    KeyCode = 0
    Exit_AddNewRecord_Click:
    Exit Sub
    Err_AddNewRecord_Click:
    MsgBox Err.Description
    Resume Exit_AddNewRecord_Click
    End If
    End Sub

    Here's the three scenarios that occur:

    I open the form
    The cursor is sitting on the txt_UserId field of the first record
    I press Function Key F10
    The cursor sits on the now empty txt_UserId field
    I tab advance to the next field and no error message.

    I open the form
    The cursor is sitting on the txt_UserId field of the first record
    I click on the New Record icon on the menu bar
    The cursor sits on the now empty txt_UserId field
    I tab advance to the next field and no error message.

    I open the form
    The cursor is sitting on the txt_UserId field of the first record
    I backspace to delete the data in the field
    I tab advance to the next field and
    Big Error Message.

    What have I messed up this time?

    Thanks in advance as always,
    Cheers,
    Andy

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

    Re: How to Trim correctly (A2K)

    Make sure that your event procedure is "connected" to the text box:
    - Open the form in design view.
    - Click in txt_UserId.
    - Activate the Event tab of the Properties window.
    - Make sure that the On Exit event contains [Event Procedure].
    - Click the builder button (the ... to the right of the dropdown arrow).
    - You should be taken to the code you created (and posted)
    - Switch back to Access, and save the form.

    If that doesn't help, you might post a stripped down copy of the database.

  5. #5
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Trim correctly (A2K)

    Hans,

    Did It - Open the form in design view.
    Did It - Click in txt_UserId.
    Did It - Activate the Event tab of the Properties window.
    Did It - Make sure that the On Exit event contains [Event Procedure].
    Did It - Click the builder button (the ... to the right of the dropdown arrow).
    Did It - You should be taken to the code you created (and posted)
    Did It - Switch back to Access, and save the form.

    Will do this today - If that doesn't help, you might post a stripped down copy of the database.

    Small, question. If I hadn't already done all of the above, how could I have received the Message in Scenario #3? Just curious.

    Regards,
    Hans
    Cheers,
    Andy

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

    Re: How to Trim correctly (A2K)

    I can't tell you without seeing the database.

  7. #7
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Trim correctly (A2K)

    Hans,

    I knew that, just curious. Anyway here's my latest Albatross,
    Cheers,
    Andy

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

    Re: How to Trim correctly (A2K)

    The combo box misleadingly named txt_UserId is bound to AGM UserId. This field in tbl Assets has a default value of 0, so it is not null in a newly created record. The combo box looks empty because there is no record in tbl Usernames with primary key value 0.
    So although the combo box doesn't display a value, the underlying field is non-null and hence you get no warning. If you explicitly delete an existing value in the combo box, you clear the underlying field, so you do get a warning.
    Solution: open tbl Assets in design view and clear the Default Value property of AGM UserId.

  9. #9
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Trim correctly (A2K)

    Hans,

    In order to save myself a lot of repetitive typing, I think I'll store a macro or something somewhere that will say, "Hans, thanks once more for coming through and helping me", and just keep using it each time that you come through and help me. 'Twas the answer, albeit not overly obvious. It gives me one more thing to be beware of in my coding attempts. Again, thanks,
    Cheers,
    Andy

  10. #10
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Trim correctly (A2K)

    Hans,

    This is more a curiosity thing than anything else. We know, thanks to you, that the following works:

    Private Sub txt_UserId_Exit(Cancel As Integer)
    Dim intButType As Integer
    Dim strMsgPrompt As String, strMsgTitle As String
    If IsNull([txt_UserId]) Then
    strMsgPrompt = "You must enter your User Id in order to " & vbCrLf & _
    "to continue entering data" & vbCrLf & _
    " for this Item"
    strMsgTitle = "No Id!! No Entry!!"
    intButType = vbExclamation
    MsgBox strMsgPrompt, intButType, strMsgTitle
    Cancel = True
    End If
    End Sub


    Is there a way that you can dump the user back to the previous form, such as "frm
    Cheers,
    Andy

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

    Re: How to Trim correctly (A2K)

    You could use a module-level variable to keep track of how many times the user tries to exit txt_UserId without having selected a user id. Would you like to reset the count when the user moves to another record (for example a new record), so that (s)he gets three tries in each record, or would you prefer to limit the number of tries overall?

  12. #12
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Trim correctly (A2K)

    Hans,

    It would be a case of 3 strikes and you are out. If they don't know their own Id number, they shouldn't be there, and should be dumped out.
    Cheers,
    Andy

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

    Re: How to Trim correctly (A2K)

    You can use code like this:

    Private Strikes As Integer

    Private Sub txt_UserId_Exit(Cancel As Integer)
    Dim intButType As Integer
    Dim strMsgPrompt As String, strMsgTitle As String
    If IsNull(<!t>[txt_UserId]<!/t>) Then
    Strikes = Strikes + 1
    If Strikes = 3 Then
    MsgBox "You lose!", vbCritical
    DoCmd.OpenForm "frm - Menu - Administration"
    DoCmd.Close acForm, Me.Name, acSaveNo
    End If
    strMsgPrompt = "You must enter your User Id in order to " & vbCrLf & _
    "to continue entering data" & vbCrLf & _
    " for this Item"
    strMsgTitle = "No Id!! No Entry!!"
    intButType = vbExclamation
    MsgBox strMsgPrompt, intButType, strMsgTitle
    Cancel = True
    End If
    End Sub

  14. #14
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to Trim correctly (A2K)

    Hans,

    Hate to mess up your code, but when I replaced your original code with your update, I got a:

    Compile Error: Variable not defined

    with the second Strike in

    Strikes = Strikes +1

    being highlighted. I've obviously not placed the "Private Strikes As Integer" in its correct place.
    Cheers,
    Andy

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

    Re: How to Trim correctly (A2K)

    The line

    Private Strikes As Integer

    should be near the top of the module belonging to the form, below the line(s) beginning with Option, but before all Subs.

Page 1 of 3 123 LastLast

Posting Permissions

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