over complicated search (1 Viewer)

[JJSHEP89]

So here is something I built years ago that I've continuously refined and built on but now i need to build on significantly more to make it work the way it needs to. Here it goes.....

Background: I have one main database with multiple specific forms that somewhat correlate to specific departments for a manufacturing company(ex. maintenance, engineering, shipping and receiving, quality etc.) these specific forms all have the same header that contains a home button, label, search box, and search button. The search box (unbound text box) is where the user enters in either a part number, tool number, or machine number (parts are produced by tools which are set up in machines) and my function takes the input, processes it, and displays the correct results based on the department form they are in.

Here's where it gets complicated.... depending on the department, each user enters in the information differently and even within some departments entries may not be standardized.
For example, lets say part number 1234 is produced by Tool Number AA100, the user could enter either one of these numbers and the function works just fine. But the user could enter any of these which are common and still relevant...

AA100-18H (Tooling Job Number)
123400A or 1234-00A (part w/ part Revision)
1234-AB0 (part w/ print Revision)
123400A_AB0 (part with part & print Rev)
123400A-038 (part production job#)

The list goes on, the only thing that's common is the tool number or part number is always at the beginning of the users input. However the length of the part numbers and tool numbers are not known. This information is all stored in two tables PartMatrix & ToolMatrix.
To add some complication the searches occasionally return multiple results, example multiple tools may be used to produce the same part, multiple parts can be produced from the same tools, or the same part can be produced from multiple tools. I've somewhat solved this problem of multiple results by displaying them in a pop up form with a listbox. I also have a solution if the tool's Job Number (AA100-18H, "always a "-" followed by the year, followed by H,V, or S to desingnate plant location) was entered.

I'm wanting to use just one function that can be called by each search box on the forms to simplify things as I continue to build more forms for each department. here is what i have thus far.

Public varToolNumber, varPartNumber, varCompID, varUserID, varAction As Variant
'------------------------------------------------------------
' Library Function - ValidateEntry()        Takes an input string from the user,(removes job number Designation if input), verifies that tool or part number exists and returns whichever is needed.
'                                           If no tool number matches the input string, weather part number, job number, or tool number the function returns a "0".
'------------------------------------------------------------
Public Function ValidateEntry(varEntry As Variant) As Variant
Dim strSQL As String

'Section1:::: Determines a valid entry exists
    If Len(Nz(varEntry, "")) = 0 Then
        ValidateEntry = "-1"
        Exit Function
    Else 'Determines if entry was input as Job Number
        Select Case Right$(varEntry, 1)     'If entered as Job Number, removes job number section off end and returns valid search
           Case "H", "V", "S"
                If Mid(varEntry, Len(varEntry) - 1, 1) <= 9 And Mid(varEntry, Len(varEntry) - 2, 1) <= 9 And Mid(varEntry, Len(varEntry) - 3, 1) = "-" Then
                            varEntry = Left(varEntry, Len(varEntry) - 4)
                End If
           Case "A" To "Z"     'starting to build on further here
                MsgBox "This entry ends in a letter."
        
        End Select
    End If
'Section2:::: Determine actions based on currently active form
    Select Case (Screen.ActiveForm.Name)
        Case "Toolbook"
            strSQL = "SELECT partmatrix.PM_PartNumber AS [Part Number], partmatrix.PM_ToolNumber AS [Tool Number], partmatrix.PM_PartDescription AS Description, " & _
                          "partmatrix.PM_PartStatus AS Status FROM partmatrix WHERE ((partmatrix.PM_PartNumber)= '" & varEntry & "') ORDER BY partmatrix.PM_ToolNumber DESC"
            
            If Not IsNull(DLookup("TM_ToolNumber", "ToolMatrix", "TM_ToolNumber ='" & varEntry & "'")) Then 'Sets varToolNumber equal to the tool number if entered as tool number or part number
                    varToolNumber = DLookup("TM_ToolNumber", "ToolMatrix", "TM_ToolNumber ='" & varEntry & "'")
            ElseIf Not IsNull(DLookup("PM_ToolNumber", "PartMatrix", "PM_PartNumber ='" & varEntry & "'")) Then
                    If (DCount("PM_PartNumber", "partmatrix", "partmatrix.PM_PartNumber = '" & varEntry & "'")) > 1 Then
                        DoCmd.OpenForm "PopUp_MultiSelect"
                        Forms("PopUp_MultiSelect").Controls("lstMultipleValues").RowSource = strSQL
                        ValidateEntry = "-2"
                        Exit Function
                    Else
                        varToolNumber = DLookup("PM_ToolNumber", "PartMatrix", "PM_PartNumber ='" & varEntry & "'")
                    End If
            Else: ValidateEntry = "0"
            End If
                          
        Case "Production"
            strSQL = "SELECT presssetup.ID, presssetup.PartNumber, presssetup.DieNumber, presssetup.ResourceGroup, presssetup.OperationSequence " & _
                            "FROM presssetup WHERE ((presssetup.PartNumber) Like '" & varEntry & "*" & "') ORDER BY presssetup.DieNumber DESC;"
            Select Case (DCount("PartNumber", "presssetup", "((presssetup.PartNumber) Like '" & varEntry & "*" & "')"))
                Case Is = 0
                
                Case Is = 1
                    varToolNumber = DLookup("ToolNumber", "presssetup", "((presssetup.PartNumber) Like '" & varEntry & "*" & "')")
                    varPartNumber = DLookup("PartNumber", "presssetup", "((presssetup.PartNumber) Like '" & varEntry & "*" & "')")
                    
                Case Is > 1
                    DoCmd.OpenForm "PopUp_MultiSelect"
                    With Forms("PopUp_MultiSelect").Controls("lstMultipleValues")
                        .ColumnCount = 5
                        .ColumnWidths = "0;1.25;1.25;;2.75;1.25;"
                        '.BoundColumn =
                    End With
                    ValidateEntry = "-2"
                    Exit Function
            End Select
    End Select
End Function

I have the following SELECT....CASE statement that processes the results

    Select Case ValidateEntry(varSearch)
        Case "0"
            MsgBox "The Part/Tool Number you have entered does not exist in the database." _
                & " Please correct the search or contact an administrator to enter the information."
            Exit Sub
        Case "-1"
            MsgBox "Please enter a valid search term in the form of a tool or part number."
            Exit Sub
        Case "-2"
            Exit Sub
    End Select

and then of course if the listbox is popped up its results from a double click selection are processed with this...

Private Sub lstMultipleValues_DblClick(Cancel As Integer)
    varPartNumber = Me.lstMultipleValues.Value
    varToolNumber = Me.lstMultipleValues.Column(1)
    DoCmd.Close
    Forms("Toolbook").Requery  'I would need to change this to requery only the open forms
End Sub

Sorry for the Novel but like the title says.... its a bit complicated
TIA!

 

[JHB]

And the question is, if any?
Only for information:

Public varToolNumber, varPartNumber, varCompID, varUserID, varAction As Variant

You've to declare each variable with data type, else they will be of type Variant, (in the above it is okay, because all will become variant type).

 

[JJSHEP89]

And the question is, if any?
Only for information:

Public varToolNumber, varPartNumber, varCompID, varUserID, varAction As Variant

You've to declare each variable with data type, else they will be of type Variant, (in the above it is okay, because all will become variant type).

The only one that may not be a variant would be the varAction variable, but only because i havent done anything with that variable yet (didn't mean to even copy it over). The rest have to be variants.