1.

Solve : Access 2003 Question?

Answer»

I'm trying to put a database together for a friend who has a car dealership but I'm fairly new to databases and this is my FIRST attempt at a REAL one.

The problem I'm having at the moment is trying to validate some data.

I have a box for the Vehicle Identification Number (VIN) and I want to validate whatever is entered in 2 ways.
 
1.  It has to be exactly 17 characters long
2.  If a lowe case letter in entered I want it to be automatically changed to upper case

I have succeeded in making sure the string is 17 characters long by using LIKE "??".  That works fine.  

I have also seen a function called UCase and I think that's the function I should use to make the lower case LETTERS into upper case. But I can't get the two to work together.  Well....actually...I haven't managed to get the UCase function to work at all.

I'm probably just doing something wrong so would appreciated it if someone could let me know what I should do to have the input validated in the ways I want.

Thanks in adance

DavvaDo VINs for all vehicles have the same format?  Do they all have alpha characters in the same position?No, the VIN's are all different but they all contain 17 characters and at least some letters.  The letters can (and do) appear anywhere in the string.I can't get the UCase function to work either.  If I try to use it as a Validation Rule and then enter a string of upper case letters in the field, Access beeps and tells me I can only use upper case letters in that field, even though I did so.  If I figure out how to do this, I'll post another reply. Quote

I can't get the UCase function to work either.  If I try to use it as a Validation Rule and then enter a string of upper case letters in the field, Access beeps and tells me I can only use upper case letters in that field, even though I did so.  If I figure out how to do this, I'll post another reply.


OK, thanks.  I'll keep my eye on this thread.

DavvaThe trick is, in your form, to run a little Visual Basic when the "onChange" event fires for VIN field in the data the entry form.  I don't have acces to Access (groan) on the computer I'm currently using, but if you look for "Events" in the online help, that may start you on your way to doing much more powerful things with Access.

So, in the code for the onChange event, you do something like this (this is psuedo code; it won't actually work):

Code: [Select]if Len(fieldname.value) <> 17 then
  echo "VIN must be exactly 17 characters; yours is " + Len(fieldname.value)
  fieldname.SetFocus
  exit
else
  fieldname.value = UCase(fieldname.value)
end ifHope this helps a little.As an Access geek who also happens to work in the automotive industry, you shouldn't worry about how the VINs are entered in the underlying tables of the database.  From typing VINs for years, it is much easier to do in lower case than constantly toggling back and forth on caps lock - just my opinion.  For any forms or reports, you would want to add this code to the VIN control so that it displays as all uppercase.  This code is from VB help, but should be easily adapted to your situation.

UCase Function Example
This example uses the UCase function to return an uppercase version of a string.

Dim LowerCase, UpperCase
LowerCase = "Hello World 1234"    ' String to convert.
UpperCase = UCase(LowerCase)    ' Returns "HELLO WORLD 1234".

Hope this helps!

Ben

Quote
As an Access geek who also happens to work in the automotive industry, you shouldn't worry about how the VINs are entered in the underlying tables of the database.

Agreed, it should be transparent to the user how the data is entered, but storing the data in the database should be in UPPERCASE. Having to do uppercase translation everytime a report or query is run is a waste of system resources.

Rob, has the right idea. Do the uppercase translation once, at point of entry.

Just my 2¢.  8-)Hi all

Thanks for the advice so far.

I had put this problem on the back burner as a couple of other things had come up.

Now, I'm back on it and it's doing what I want (thanks Rob) except for the focus is not returning to the VIN box after I close the message box.

My code is like this:

Private Sub VIN_LostFocus()
If Len(VIN.Value) <> 17 Then
MsgBox "VIN must be exactly 17 characters long.  Please check and re-enter"
        VIN.SetFocus
        Else
        VIN.Value = UCase(VIN.Value)
    End If
End Sub



How do I make the focus return to the VIN box?

Thanks in advance (again)

Davva
If I remember right, you have to cancel the event that's about to occur.  E.g. say your user has entered the wrong info, and then tabbed to the next field; the lostFocus event fires - this causes your code to run.  But the onEnter event is queued for whatever control the user is TABBING to.  And that event will cause the control to gain focus after your code runs.

My memory may be a little rusty - it's about six years since I did any VBA programming.  Did you try running the code on the onChange event rather than lostFocus?I tried running the code on the onchange event and also on the onExit event but neither worked.  It went all funny.  As soon as I clicked in the VIN box all the characters got hi-lighted and I couldn't make any changes inside the box.  

Your comment about the next event being queued SOUNDS like it could be the problem.  How do I cancel that next event and send it back to the VIN box?


Discussion

No Comment Found

Related InterviewSolutions