Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Upper casing data not working (Access 2002/Access97(SR2))

    (<!t>[pre]<!/t> and <!t>[/pre]<!/t> tags inserted by HansV to preserve double spaces and indentation in code - see <!help=19>Help 19<!/help>)

    I have a database that, amonst other things, captures town and postcode. Both of these fields need to be upper case, and postcode needs double spaces removed, for which I use the following:
    <pre>Do Until InStr(1, Me.Postcode, " ", vbTextCompare) = 0
    Pcode = UCase(ReplaceString(Me.Postcode, " ", " "))
    Me.Postcode = Pcode
    Loop
    me.town = ucase(me.town)
    </pre>

    I have tried putting it in all of the before update events and change events I can think of but it only seems to change the way the data is shown, and not the actual data when it gets saved to the database.

    i.e. If I enter the last letter of a postcode with 'd' and click out of the box, it displays as 'D'. When I click back it the box, it shows as 'd' again, and 'd' is written to the database.
    I was thinking of using ucase on the key events, but if the user pastes a postcode in, it will skip it. I also tried opening the recordset and editing the record that way, but it caused locks and I think it might be a bit inefficient.

    Any help would be much appreciated. Thanks.

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

    Re: Upper casing data not working (Access 2002/Access97(SR2))

    If you want the value to be modified immediately after it has been entered, you should use the After Update event of the text box, not its Before Update or On Change event. You should only modify the value of (the field in) the text box, not other fields as well.
    <pre>Private Sub Postcode_AfterUpdate()
    Me.Postcode = UCase(Replace(Me.PostCode, " ", " "))
    End Sub
    </pre>

    If you want the value to be modified when the record is being saved (for example because the user moves to another record), use the Before Update of the form itself; you can modify multiple values there.
    <pre>Private Sub Form_BeforeUpdate(Cancel As Integer)
    Me.Postcode = UCase(Replace(Me.PostCode, " ", " "))
    Me.Town = UCase(Me.Town)
    End Sub
    </pre>


  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Upper casing data not working (Access 2002/Access97(SR2))

    Thanks for your reply Hans.
    Thats how I thought the events should work so I've had a bit of further investigation....

    There was some other code in the before update, and no error handling, it was bombing on the other code so not running any of the code i'd written.
    That'll teach me for not reading the whole section of code! I stupidly assumed that whatever was there previously was actually working.

Posting Permissions

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