VBA Function validating field

[MagiCat]

I have a VBA script that I wrote to validate the VIN number in Microsoft Excel.

The declaration is:

Function VINValidate(VIN as string) as boolean

which means that it will return TRUE if the VIN is valid and FALSE is invalid.

Is there a way to use this VBA script (which uses nothing that's exclusively Excel) to validate a field in Microsoft Access. I understand that I might have to change the declaration or something like that.

 

[MyBoo]

Not sure of your Access setup.

In Access, you need to have the VIN is some sort of table (or hardcoded in VBA) before you can test for validity.... otherwise, how would you accomplish this?

If not already done, I would create a table for my VIN numbers... making that the primary key so that I cannot enter duplicates. I would then search Help to find how to use the DLookup Function.

 

[mdmilner]

I'd like to expand the questions further, what are you actually trying to do?

The reason I'm asking is, there are multiple ways to approach this but they all depend on what you are actually doing.

Editing this because the examples I gave were a little vague.

Even though a table looks just like a Spreadsheet, Access gives you true database functionality. You might be trying to do something the hardest way you could based on a Spreadsheet approach. It's doable, it just might be far easier and simpler to do it another way.

Right now, for example, I'm suspicious that what you really need to do is build a relationship between the VIN list and the table you wish to validate. This actually provides a number of additional designed based capabilities that affect a lot of downstream tasks (data extraction, reports, etc). Even better, it's incredibly easy to do - you'll be doing it like a pro with just a short explanation.

Mike

 

[VicRauch]

Do you mean you want to use the VINValidate function as it resides in Excel, rather than copy it to your Access database? I would not suggest doing that, unless this function has a good possibility of changing. But if you must use the function while it is still in Excel, I believe there is a way to reference the Excel file as another project, therefore making the Excel functions callable from Access.
HTH,

 

[MagiCat]

I'm sorry, I think I confused people. There is an algorithm that allows you to validate a VIN for accuracy. Since it's 17 digits long, it's very easy to type it incorrectly.

I have the function as an Excel VBA function which I use in Excel all the time, mostly as =VINValidate(CELL) and then it gives me a true or false depending on if it's valid.

I want to copy the code to Access and then validate the VIN number based on this code (which returns TRUE/FALSE). The code works great in Excel, it's just that I'm always having to type the code in Excel and then copying and pasting to Access just to make sure it's correct.

Here's the actual function:

Function VINValidate(VINNumber As String) As Boolean

    Dim charLocationValue(1 To 17) As Integer
    
    charLocationValue(1) = 8
    charLocationValue(2) = 7
    charLocationValue(3) = 6
    charLocationValue(4) = 5
    charLocationValue(5) = 4
    charLocationValue(6) = 3
    charLocationValue(7) = 2
    charLocationValue(8) = 10
    charLocationValue(9) = 0
    charLocationValue(10) = 9
    charLocationValue(11) = 8
    charLocationValue(12) = 7
    charLocationValue(13) = 6
    charLocationValue(14) = 5
    charLocationValue(15) = 4
    charLocationValue(16) = 3
    charLocationValue(17) = 2

    workingvinnumber = Trim(VINNumber)
    If Len(VINNumber) <> 17 Then
        VINValidate = False
        Exit Function
    End If
    
    For x = 1 To 17
        thisVINCharacter = Mid(workingvinnumber, x, 1)
        Select Case thisVINCharacter
            Case "A", "J", "1"
                thisVINCharacterValue = 1
            Case "B", "K", "S", "2"
                thisVINCharacterValue = 2
            Case "C", "L", "T", "3"
                thisVINCharacterValue = 3
            Case "D", "M", "U", "4"
                thisVINCharacterValue = 4
            Case "E", "N", "V", "5"
                thisVINCharacterValue = 5
            Case "F", "W", "6"
                thisVINCharacterValue = 6
            Case "G", "P", "X", "7"
                thisVINCharacterValue = 7
            Case "H", "Y", "8"
                thisVINCharacterValue = 8
            Case "R", "Z", "9"
                thisVINCharacterValue = 9
            Case "0"
                thisVINCharacterValue = 0
            Case "I", "O", "Q"
                VINValidate = False
                Exit Function
        End Select
        totalvalue = totalvalue + (thisVINCharacterValue * charLocationValue(x))
    Next
    calculatedCheckDigitValue = totalvalue Mod 11
    If calculatedCheckDigitValue = 10 Then
        calculatedCheckDigit = "X"
    Else
        calculatedCheckDigit = Trim(CStr(calculatedCheckDigitValue))
    End If
    actualCheckDigit = Mid(workingvinnumber, 9, 1)
    If calculatedCheckDigit = actualCheckDigit Then
        VINValidate = True
    Else
        VINValidate = False
    End If
    
End Function

 

[VicRauch]

Just copy that code you just showed us into a module in Access. If it works in Excel, I see no reason it will not work in Access. After you copy it into a module in Access, save it, compile it, then try it. This might be where you are having some of the problem.
Where or how do you want to use it in Access? I would think that you would want it checked as soon as a user entered a new VIN number on a form. If that is the case, the in the AfterUpdate (or BeforeUpdate [thank you Norie]) event for the VIN number field on your form, have code to call this function, passing it the VIN number, then use the True of False that is returned to post an error message, or whatever you want to communicate to your user.
HTH,

 

[Norie]

As far as I'm aware you can't use a user defined function for a Validation Rule for a field in a table.

You could however validate the field on a form using an event like BeforeUpdate.

 

[MagiCat]

Was trying to avoid using a form for doing this, the table is going to be changing structure as people need new stuff added to it.

I know the limit for the amount of characters is pretty high for validation text. Would anyone have any advice (if it's possible) for converting the above code into validation code?

The algorithm is pretty simple, just cycle through the characters, get the value, Mod 11 if and see if it matches the 9th character.

 

[VicRauch]

Sounds like you are saying that you will be letting people key information directly into a table. Is that correct? Would be a lot better to do the form, even if you have to change it every time the table is changed. The table will not accept a user written function, only built in functions. So, only the form will help you with the VIN validation. But, as I told giacome earlier, don't give up, you still might find something. I have been very amazed at some of the things people have come up with even though it looked like it couldn't be done. When you find it, please let us know too.

 

[mdmilner]

And thanks for the answer - I was way off on my guess.

To a very limited extent, I have some direct contact with VIN numbers in my current position and while I haven't needed such a code snippet previously, you never know - this could come in handy, so thanks. I thought I was going to have to research the rules.

So what if you were to embed your custom function in a SQL statement as a parameter and used it to return only the FALSE items? This would give you a list of erroneous entries that you could either walk thru in a recordset (more code) to handle, or do something as simple/direct as print it out.

Mike