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.

 

[MagiCat]

Vic: It's more that I'm the only one one using the database. But since there is data that's not related directly to the trucks but links via a foreign key, I decided to go with Access to store it instead of Excel (plus, I'm the only one with Access.) The forms that people submit are in Excel (which is why I asked the same basic thing in the Excel forum about data validation)

Mike, Yeah, it was more that I was looking for a way to use actual validation code. I tested it on our local fleet of about 150 trucks and fully 25% of them had invalid VINs in the current database. Went to the trucks and got the real ones. I don't really understand why a validation routine isn't already out there for VBA but I guess it's because it's not that difficult to create your own.

In case you need it, I also created one to determine the model year from the VIN:

Function VINYear(VINNumber As String) As String

    workingvinnumber = Trim(VINNumber)
    If Len(VINNumber) <> 17 Then
        VINYear = "Invalid"
        Exit Function
    End If
    
    yearData = "ABCDEFGHJKLMNPRSTVWXY123456789"
    yearCharacter = Mid(workingvinnumber, 10, 1)
    
    VINYearNumber = InStr(1, yearData, yearCharacter)
    If VINYearNumber > 0 Then
        VINYear = CStr(1979 + VINYearNumber)
    Else
        VINYear = "Invalid"
    End If
    
End Function

 

[Norie]

MagiCat

There are some people who believe that any data input to a database should be done solely through forms.

The user shouldn't have direct access to the underlying tables/queries.

By using forms you have much more control on how/what data is entered.

 

[VicRauch]

I'll stick to the form entry though. It is not that hard to change the form when the table changes, and if you make the form a Datasheet style, then is feels just like entering the data into the table, but you get the advantages of using the form (validations, etc.)

 

[mdmilner]

Wish I could add more - I've never actually needed to do it precisely the way you are now so I haven't tested the limits of validation in access.

Mike

 

[MagiCat]

Ok, I'm sold on the form now that I know I can use a Datasheet view (that's the other reason I wasn't going to use one, because since I'm the only one viewing it, I kindof want to sift through the data as I use it.

So what I've done is created the following as a BeforeUpdate:

Private Sub VIN_BeforeUpdate(Cancel As Integer)

    If Not IsNull(Me!VIN) Then
        If Not VINValidate(Me!VIN) Then
            MsgBox "Invalid VIN Number"
            Cancel = True
        End If
    End If

End Sub

Still kindof disappointed that I can't do this at the table level, that way if I make multiple forms I wouldn't have to keep creating the procedure over and over again. Oh well, I'll live

 

[Norie]

MagiCat

Why not just copy the original form(s)?

 

[mdmilner]

Perhaps you could have looked at embedding the function inside a SQL statement which would look like this in most basic form. This would just give you a select query which you could use elsewhere.

SELECT flda
FROM myTbl
WHERE VINValidate(VinField) = True

Mike

 

[MagiCat]

Norie: Thanks, might have came to that myself, might not have Forms are kindof new to me.

Mike: Good idea, had to change the parameter value to a variant to allow for the potential that the VIN would be a Null, but after that it worked great to let me know if there are any problems.