Access VBA search for last numbers in a car's VIN

[Crazyjo]

Greetings,

I have a database that is used to track all the vehicles in the company, currently over 550 vehicles. I will admit I am very new to VBA. The powers that be want to be able to search all the vehicles by the last 5 o 6 numbers in the VIN of the vehicle. I have this code right now.

Private Sub cmdVIN_Click()
    
    Dim txtTest As String
    
    If Not IsNull(Me.txtSearch) Then
        txtTest = "*" & Me.txtSearch
    End If
'    Debug.Print txtTest
    
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("VehVIN")
    DoCmd.FindRecord txtTest
            
'    If txtTest = VehVIN Then
        
    stDocName = "frmVehicleMod"
    stLinkCriteria = "[VehVIN]=" & "'" & Me![VehVIN] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    
'    Else
'    MsgBox "VehVIN not found."
'    End If
    
End Sub

The problem I am having is that if I uncomment the second if all searches come back with the message box. As the code is right now if the VIN searched for is not in the database it will display the first record in the database.

I have tried:

If txtSearch = VehVIN Then

With this I have enter the full VIN or it comes back with the message box every time.

If "*" & txtSearch = VehVIN Then

With this very search brings back the VIN.

Any ideas or tips on what I am missing would be great!
Thanks


When all is said and done, more is said then done. So do something

 

[CaptainD]

Another option that might work better would be to add a listbox that displays the vin numbers and as they enter the vin, update the rowsource.

I tested this on a small database I have with a personnel table and it works. I did a similar thing on another program I created some time back that searched by APN number

Once they find what they are looking for you can set it up to pull the selected vin information.

Private Sub txtTest_Change()
On Error GoTo ErrHandler
Dim strSql As String
Dim strWhere As String
Dim strSearch As String

txtTest.SetFocus
strSearch = Trim(txtTest.Text)

If strSearch = "" Then
    Exit Sub
Else
    
strSql = "SELECT [LName] FROM dbo_tblPersonnel "
strWhere = "WHERE [LName] Like " & Chr$(39) & "*" & strSearch & "*" & Chr$(39) & ";"
lstLNames.RowSource = strSql & strWhere
    

End If



Exit Sub
ErrHandler:
    MsgBox "Error getting vin number. Error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
End Sub

 

[Crazyjo]

I offered them the option however; I have been told that they want to search by the last 5 or 6 numbers of the VIN. This is how they have always done and don't want to change that. The big draw back to the list box here is that there are over 550 VIN's (17 charters long) they would have to scroll through to find the one they want.

When all is said and done, more is said then done. So do something

 

[randy700]

try changing this:
txtTest = "*" & Me.txtSearch

to this:
txtTest Like "*" & Me.txtSearch

Randy

 

[kjv1611]

If your data set is very large, you may want to look into using Regular Expressions (RegEx) in VBA, as when used correctly, that can GREATLY speed up your searches.

--

"If to err is human, then I must be some kind of human!" -Me

 

[Crazyjo]

Randy I tried your idea
txtTest Like "*" & Me.txtSearch
and it comes back with a Compile error Expected: expression as soon as I try to leave the line.

I have not come across RegEx yet I will look at it. Like I said I'm very new to VBA, let me get back to my subnetting or ADUC lol.

When all is said and done, more is said then done. So do something

 

[randy700]

Possibly this...

If len(txtTest) > 0 Then
    stDocName = "frmVehicleMod"
    stLinkCriteria = "[VehVIN] LIKE '" & txtText & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
Else

Randy

 

[CaptainD]

If you would of created a simple test form you would see that the listbox filters down with each letter or number added.
In my test app the database has 150 names, enter "a" and every name with an a shows up, now a "d" = "ad" and only names with "ad" etc. to the point that you are down to two names, both "adams" with "adam" in the textbox.

If you apply it to the vin numbers enter "AB1234" and only vins with that combination would be in the listbox.

For grins, create a form with a textbox and listbox, change names to match mine and paste in my code with the adjustments to your vin table and see what happens. Should not take more then 5 minutes

 

[Crazyjo]

Sorry I have not had time to test your idea CaptainD I will do so. Been a busy day putting out other fires.

When all is said and done, more is said then done. So do something

 

[CaptainD]

If you want to futher limit the number of vin numbers that show up in the listbox, the code below clears the listbox until there are at least 3 letters or numbers entered into the textbox.

When I created the program this ran on there were over 200,000 APN numbers and it work great.

Private Sub txtTest_Change()
On Error GoTo ErrHandler
Dim strSql As String
Dim strWhere As String
Dim strSearch As String

txtTest.SetFocus
strSearch = Trim(txtTest.Text)

If Len(strSearch) < 3 Then
    lstvehVIN.RowSource = ""
    Exit Sub
Else
    
strSql = "SELECT [vehVIN] FROM tVinNumbers "
strWhere = "WHERE [vehVIN] Like " & Chr$(39) & "*" & strSearch & "*" & Chr$(39) & ";"
lstvehVIN.RowSource = strSql & strWhere
    

End If



Exit Sub
ErrHandler:
    MsgBox "Error getting vin number. Error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
End Sub