partial match

[banks13cu]

I have created a form to search for a model number in a table. Say the model number is ABC-123. The current code I have written makes an exact match so ABC-123 would have to be entered for the return of the data record. I want it to be able to pull a partial match, so if you enter ABC it would pull back the ABC-123 and if there is a ABC-555 it comes back also. I have pasted my current code that gives exact matches below. Thanks for your help.



Private Sub Text0_AfterUpdate()
Dim RecNum As Variant
Dim FrmLnkCriteria

RecNum = DLookup("[Model]", "Inventory", "[Model] = '" & Forms!Model_Lookup!Text0 & "'")

If IsNull(RecNum) Then
MsgBox "The model you entered is invalid. Please check the model and try again.", vbOKOnly, "Invalid Model"
Me!Text0 = ""
Me!Text0.SetFocus
Else
FrmLnkCriteria = "[Model]=" & "'" & Me![Text0] & "'"

DoCmd.OpenForm "Inventory_Lookup", , , FrmLnkCriteria

End If

End Sub

 

[Pampers]

You can run a query on a buttonclick (instead of the textbox), with a query-criterium like:


Like "*" & [give model number or any part of:] & "*"

Pampers
You never too young to learn

 

[TheAceMan1]

. . . or just change [blue]FrmLnkCriteria[/blue] to that given by [blue]Pampers[/blue]:

[blue]    FrmLnkCriteria = Like "*" & [give model number or any part of:] & "*"[/blue]

See Ya! . . . . . .

 

[banks13cu]

Sorry but I'm not following what you are saying. Do I need to keep it as an After_Update or do I need to change it to something else. I know enough about access and VB to get myself in trouble. Just explain a little more for me please!

Thanks

 

[TheAceMan1]

banks13cu . . .

Sorry about the confusion. Also, after taking a second good look [blue]the criteria is not right for VBA[/blue]. So in your [blue]Private Sub Text0_AfterUpdate()[/blue] event, change the [blue]FrmLnkCriteria =[/blue] line to the following corrected line:

[blue] FrmLnkCriteria = "[Model] Like '*" & Me!Text0 & "*'"[/blue]

See Ya! . . . . . .

 

[banks13cu]

Ok. I've updated the code and its still not taking the 'wildcard' * into the equation. Its only doing exact matches. Here is the code...

Private Sub Text0_AfterUpdate()
Dim RecNum As Variant
Dim FrmLnkCriteria

RecNum = DLookup("[Model]", "Inventory", "[Model] = '" & Forms!Model_Lookup!Text0 & "'")

If IsNull(RecNum) Then
   MsgBox "The model you entered is invalid.  Please check the model and try again.", vbOKOnly, "Invalid Model"
   Me!Text0 = ""
   Me!Text0.SetFocus
Else
   FrmLnkCriteria = "[Model] Like '*" & Me!Text0 & "*'"
    
    DoCmd.OpenForm "Inventory_Lookup", , , FrmLnkCriteria
    
End If

End Sub

 

[TheAceMan1]

Since your putting in the the wildcards, remove the wildcards in [red]red[/red] below:

[blue]FrmLnkCriteria = "[Model] Like '[red][b]*[/b][/red]" & Me!Text0 & "[red][b]*[/b][/red]'"[/blue]

See Ya! . . . . . .

 

[banks13cu]

I really appreciate your help TheAceMan1! But it's still only pulling the exact match. If I put in a partial it gives me the error message that I have in the code of not being found. Here it is:

Private Sub Text0_AfterUpdate()
Dim RecNum As Variant
Dim FrmLnkCriteria

RecNum = DLookup("[Model]", "Inventory", "[Model] = '" & Forms!Model_Lookup!Text0 & "'")

If IsNull(RecNum) Then
   MsgBox "The model you entered is invalid.  Please check the model and try again.", vbOKOnly, "Invalid Model"
   Me!Text0 = ""
   Me!Text0.SetFocus
Else
   FrmLnkCriteria = "[Model] Like '" & Me!Text0 & "'"
    
    DoCmd.OpenForm "Inventory_Lookup", , , FrmLnkCriteria
    
End If

End Sub

I've tried variations of it, with spaces, without spaces and nothing. Any ideas?

 

[TheAceMan1]

[blue] . . . If I put in a partial it gives me the error message that I have in the code of not being found.[/blue]

According to your code thats exactly what should happen!

You use a DLookUp to hunt for a model# & determine wether or not to display the form. [purple]I doubt if any model# includes the filter characters! . . .[/purple]

What you may have to do is [blue]setup a seperate textbox for filtering . . .[/blue]

See Ya! . . . . . .

 

[TheAceMan1]

banks13cu . . .

Have a solution, just may not get to post it till this evening. If you feel energetic the technique involves a [blue]search for the filter characters [purple]*?[/purple] first[/blue], using the [purple]InStr[/purple] function to determine wether to filter or use [blue]DLookUp[/blue]. Logic would be something like:

[blue]   If FilterCharactersFound Then
      [green]'Open form with filter[/green]
   ElseIf IsNull(DLookUp(...)) Then
      [green]'Your Error Message Here[/green]
   Else
      [green]'Open form with model#[/green]
   End If[/blue]

See Ya! . . . . . .

 

[TheAceMan1]

banks13cu . . .

I thought I had posted this:

[blue]Private Sub Text0_AfterUpdate()
   Dim Msg As String, Style As Integer, Title As String, DL As String
   Dim Ctl As Control, Criteria As String
   
   DL = vbNewLine & vbNewLine
   Set Ctl = Me!Text0
   
   If Trim(Ctl & "") <> "" Then
      [green]'Setup proper criteria depending if filter characters present.[/green]
      If InStr(1, Ctl, "*") Or InStr(1, Ctl, "?") Then
         Criteria = "[Model] Like '" & Me!Text0 & "'"
      Else
         Criteria = "[Model] = '" & Me!Text0 & "'"
      End If
      
      If IsNull(DLookup("[Model]", "Inventory", Criteria)) Then
         Msg = "Search criteria not found!" & DL & _
               "Please check your criteria and try again . . ."
         Style = vbInformation + vbOKOnly
         Title = "Nothing Found! . . ."
         MsgBox Msg, Style, Title
      Else
         DoCmd.OpenForm "Inventory_Lookup", , , Criteria
      End If
   End If
   
   Set Ctl = Nothing

End Sub[/blue]

See Ya! . . . . . .

 

[PHV]

You wanted this ?

Private Sub Text0_AfterUpdate()
Dim RecNum As Variant
Dim FrmLnkCriteria
FrmLnkCriteria = "Model Like '*" & Me!Text0 & "*'"
RecNum = DLookup("Model", "Inventory", FrmLnkCriteria)
If IsNull(RecNum) Then
   MsgBox "The model you entered is invalid.  Please check the model and try again.", vbOKOnly, "Invalid Model"
   Me!Text0 = ""
   Me!Text0.SetFocus
Else
   DoCmd.OpenForm "Inventory_Lookup", , , FrmLnkCriteria
End If
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[banks13cu]

PHV, thank you that is exactly what I was looking for. Aceman, thank you for your help you defently got the wheels turning!