Forms based on dynamic SQL query's

[JJSHEP89]

So i'm thinking there has got to be a better way of doing this.

Here's the background info...
I have a multi user database that is used to track information for industrial tooling (maintenance records, tooling spec's, usage history etc.). Through this database i have one main form that has a search bar up at the top where the end user can input the tool number and pull up the required information for that tool. Once the form is populated they can view information as well as enter any maintenance records for that tool. This form was based off a query where when the user performed the search and it would refresh the form with the new query tool number criteria.

The issue here was that the user's were inputting the wrong information into the search bar thus corrupting the data stored in the records. Sometimes they would input a job# instead of a tool number, or would enter the part number (parts are produced by the tools) produced, or even worse they would enter information with no tool selected leaving a record that's not tied to anything. To fix this I wrote a function to test against all these scenario's, correct the information, and output the correct tool number to open, which was then assigned to a public variable. (see code below)

Public varToolNumber As Variant
'------------------------------------------------------------
' Library Function - ValidateEntry()
'------------------------------------------------------------
Public Function ValidateEntry(varEntry As Variant) As Variant
Dim varToolNumber As Variant

'The ValidateEntry() Function takes an input string from the user and removes the "-16h" section of a job number if input as so _
    It then verifies that tool number exists in the tables and returns that tool number.  If the string was entered _
    as a part number it then verifies the part number exists in the part matrix table and then returns the coresponding _
    tool number associated with that part.  If no tool number matches the input string, weather part number, job number, _
    or tool number the function returns a "0".
        
' Section1:::: Determines if entry was input as Job Number
'       Select...Case statement checks to see if the user has entered an invalid _
            entry in the form of a Job number and not a Tool or Part number, the code _
            removes the Job number tag off the end and returns the corrected tool number.
    Select Case Right$(varEntry, 1)
           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
    End Select

'Section2:::: Sets varToolNumber equal to the tool number if entered as tool number or part number
    If Not IsNull(DLookup("TM_ToolNumber", "ToolMatrix", "TM_ToolNumber ='" & varEntry & "'")) Then
        varToolNumber = varEntry
        
    ElseIf Not IsNull(DLookup("PM_ToolNumber", "PartMatrix", "PM_PartNumber ='" & varEntry & "'")) Then
        varToolNumber = DLookup("PM_ToolNumber", "PartMatrix", _
                "PM_PartNumber ='" & varEntry & "'")
                
    Else: varToolNumber = "0"
    End If
     
    ValidateEntry = varToolNumber
    
End Function

and this is the code for the main form, triggered by the click of the search button

'------------------------------------------------------------
' btnToolSearchTest_Click
'------------------------------------------------------------
Private Sub btnToolSearchTest_Click()
Dim strSearch As Variant
Dim strInfoSQL, strPartSQL, strOrdersSQL, strInventorySQL, strProductionSQL, strProjectsSQL As String
  
On Error GoTo ErrorHandler:

    strSearch = Me.txtDB2_ToolSearch
         
'Section1:::: Determines an entry exists
    If IsNull(strSearch) Or strSearch = "" Then
        MsgBox "Please enter a valid search term in the form of a tool or part number."
        Me.DB_MainSub.Visible = False
        Exit Sub
    End If
     
    If ValidateEntry(strSearch) = "0" Then
        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."
        Me.DB_MainSub.Visible = False
        Exit Sub
    Else
        varToolNumber = ValidateEntry(strSearch)
    End If
           
'Section2:::: Set the query data to the information required.
    strInfoSQL = "SELECT ToolMatrix.TM_ToolNumber, PartMatrix.PM_PartNumber, PartMatrix.PM_Customer, PartMatrix.PM_DrawingRev, PartMatrix.PM_PartDescription," & _
             "PartMatrix.PM_PartFamily, PartMatrix.PM_PartWeight, PartMatrix.PM_MaterialSpec, PartMatrix.PM_MaterialGrade, PartMatrix.PM_PartStatus," & _
             "PartMatrix.PM_EEOP, ToolMatrix.TM_OperatingPlant, ToolMatrix.TM_AssetNumber, ToolMatrix.TM_PONumber, ToolMatrix.TM_PODate," & _
             "ToolMatrix.TM_ToolType, ToolMatrix.TM_ToolBuilder, ToolMatrix.TM_BuildDate, ToolMatrix.TM_Length, ToolMatrix.TM_Width," & _
             "ToolMatrix.TM_ShutHeight, ToolMatrix.TM_FeedHeight, ToolMatrix.TM_TotalWeight, ToolMatrix.TM_NumStations, ToolMatrix.TM_NumCavities," & _
             "ToolMatrix.TM_PressRate, ToolMatrix.TM_MaxCapacity, ToolMatrix.TM_Press, ToolMatrix.TM_MtlType, ToolMatrix.TM_MtlThick," & _
             "ToolMatrix.TM_MtlWidth, ToolMatrix.TM_Progression, ToolMatrix.TM_RackLocation " & _
             "FROM ToolMatrix " & _
             "INNER JOIN PartMatrix ON ToolMatrix.TM_ToolNumber = PartMatrix.PM_ToolNumber " & _
             "WHERE ToolMatrix.TM_ToolNumber ='" & varToolNumber & "';"
             
    strPartSQL = "SELECT PartMatrix.PM_PartNumber, PartMatrix.PM_DrawingRev, PartMatrix.PM_PartStatus, PartMatrix.PM_PartDescription, PartMatrix.PM_PartFamily, " & _
                 "PartMatrix.PM_EEOP, ToolMatrix.TM_MtlType, ToolMatrix.TM_MtlThick, ToolMatrix.TM_MtlWidth, ToolMatrix.TM_Progression, ToolMatrix.TM_ToolNumber " & _
                 "FROM ToolMatrix " & _
                 "INNER JOIN PartMatrix ON ToolMatrix.TM_ToolNumber = PartMatrix.PM_ToolNumber " & _
                 "WHERE (((ToolMatrix.TM_ToolNumber) = '" & varToolNumber & "')) " & _
                 "ORDER BY PartMatrix.PM_PartNumber;"
                 
    strOrdersSQL = "SELECT ComponentOrders.CO_ToolNumber, ComponentOrders.CO_DateCompleted, ComponentOrders.CO_ComponentID, ComponentOrders.CO_EntryDate, " & _
                   "ComponentOrders.CO_Qty, ComponentOrders.CO_Description, ComponentOrders.CO_DueDate, ComponentOrders.CO_Status, ComponentOrders.CO_WireBlockID, " & _
                   "ComponentOrders.CO_Notes, ToolMatrix.TM_ToolNumber " & _
                   "FROM ComponentOrders " & _
                   "INNER JOIN ComponentOrders ON ToolMatrix.TM_ToolNumber = ComponentOrders.CO_ToolNumber " & _
                   "WHERE (((ComponentOrders.CO_ToolNumber)= '" & varToolNumber & "')) AND ((ComponentOrders.CO_DateCompleted) Is Null));"
                   
    strInventorySQL = "SELECT qryDieBook_Inventory.MBM_Component_ID, qryDieBook_Inventory.MBM_Qty_On_Hand, qryDieBook_Inventory.MBM_Critical_component, " & _
                      "qryDieBook_Inventory.MBM_Part_Number, qryDieBook_Inventory.MBM_Min_Qty, qryDieBook_Inventory.MBM_Status, qryDieBook_Inventory.MBM_Description " & _
                      "FROM qryDieBook_Inventory " & _
                      "WHERE (((qryDieBook_Inventory.MBM_Qty_On_Hand) > [qryDieBook_Inventory].[MBM_Qty_Needed])) " & _
                      "ORDER BY qryDieBook_Inventory.MBM_Component_ID;"
                      
    strProductionSQL = "SELECT qryDieBook_Production.DieNum, qryDieBook_Production.PlantID, qryDieBook_Production.ClockInDate, qryDieBook_Production.ClockinTime, " & _
                       "qryDieBook_Production.Name, qryDieBook_Production.JobNum, qryDieBook_Production.LaborType, qryDieBook_Production.LaborHrs, qryDieBook_Production.LaborQty " & _
                       "FROM qryDieBook_Production " & _
                       "WHERE (((qryDieBook_Production.DieNum) = [Forms]![qryDieBook]![TM_ToolNumber])) " & _
                       "ORDER BY qryDieBook_Production.ClockInDate DESC;"
                       
    strProjectsSQL = "SELECT Projects.P_ToolNumber, Projects.P_EntryDate, Projects.P_ProjectType, Projects.P_Description, Projects.P_Status, Projects.P_DateCompleted, " & _
                     "Projects.P_DueDate, Projects.P_Notes, ToolMatrix.TM_ToolNumber " & _
                     "FROM Projects " & _
                     "INNER JOIN ToolMatrix ON Projects.P_ToolNumber = ToolMatrix.TM_ToolNumber " & _
                     "WHERE (((Projects.P_ToolNumber) = '" & varToolNumber & "')) " & _
                     "ORDER BY Projects.P_EntryDate DESC;"
                       
'Section3:::: Refresh the main form in read only window, with new query data.
    Form.RecordSource = strInfoSQL
    Me.lstPartNumber.RowSource = strPartSQL
    Me.lstOrders.RowSource = strOrdersSQL
    Me.lstInventory.RowSource = strInventorySQL
    Me.lstProductionData.RowSource = strProductionSQL
    Me.lstProjectLog.RowSource = strProjectsSQL
            
Exit Sub
    
ErrorHandler:
        MsgBox "Unknown Database error, try again.  If the error persists, close and re-open the Info Center.", , "Database Error"
        Exit Sub

End Sub

So I've said all this which brings me around to the "there has got to be a better way to do this" part. On my main form I have a large number of list boxes that are updated based on queries. Originally before the function was written to correct improper search entries, everything was tied to a query but now that my tool number(the criteria for all the queries) is tied to a global variable the only way i know how to update the list boxes is with an SQL statement tied to the listbox.rowsource all within VBA. Which is why you see so many SQL strings in the code above, one for each listbox to populate (and im not even done with all of them). This means I'm writing in a ton of custom SQL statements for each listbox. So now when the search is performed there is a significant lag time for each query to run and populate the forms.

Im sure most of you can tell by my code i'm still at beginner level, but i'm wondering if there is a more elegant or efficient way of doing this same thing? Thanks in Advance.

 

[JJSHEP89]

nothing but silence.... i can hear the crickets chirping....

 

[Minty]

You could use stored parameterised queries. Whilst it would save the lengthy SQL string it would still need the parameters filling out. Something like

Set db = CurrentDb
Set qdf = db.QueryDefs("SavedQueryName")

' BIND VALUES TO PARAMETERS
qdf.Parameters("Parameter1") = Me.cmbFirstParam
qdf.Parameters("Parameter2") = Me.txtboxSecondParam 
qdf.Parameters("Parameter3") = Me.ListBoxParam3
etc, etc

Set rst = qdf.OpenRecordset

 

[The_Doc_Man]

i'm wondering if there is a more elegant or efficient way of doing this same thing?

Welcome to the world of dumb-user interface management. Every database I've ever published to users had to include some "dumb-user mistake catchers." Is there a better way? Not without assuring better users. But, like the football coach reminds the team... "It doesn't matter how good we are on paper. We still can only play the players who dressed out for the game."

You will in all likelihood have to come up with even MORE hand-holding functions as time passes and you learn the differences between yourself (who fully understands the data requirements) and your users (who hopefully are trained well enough to know the difference between a screwdriver and a wrench). No, I'm not trying to be elitist or cynical. It is merely a fact of life that they were trained differently than you and have different life experiences that don't overlap yours. (Otherwise, why would you have the job you do?)

Minty's comments on how to save code are correct. The only reason I'm commenting on it is because that is the kind of advice you will need. Not how to avoid the need for defensive code, but rather how to ADDRESS the need, 'cause trust me - it ain't going away any time soon.

Is there a more elegant approach? Perhaps, perhaps not. You are the one in the trenches so only you can decide what needs to be done. I will offer this advice: Divide and conquer. (Worked for Julius Caesar...). Seriously, break down your validations into discrete functions. Code up a few. You might start seeing ways to repeat certain code segments or patterns. Only YOU will be in a place to do so. We won't be able to tell from our distant-view-with-blinders-on.

You should EXPECT your understanding of these checks and validations to evolve and your code will evolve as you apply any changes. You have acknowledged that your code is at "beginner level." So what? We ALL were at beginner level once. Maybe part of this is only a "pep talk" but you need to understand that what you are going through is NORMAL. You are beginning to develop a deeper understanding not only of the theoretical problem but of the PRACTICAL side of that same problem - i.e. making it work in your specific situation. You are stepping to a new level and can see the cracks in what you hoped would be a smooth interface. This is GOOD! I also know how frustrating it can be when you first hit that new level.

DON'T hesitate to come here with specific questions. You have seen how many we field here in a given day. But unfortunately, to the question I quoted at the top of this little ramble, there is no simple answer.

 

[JJSHEP89]

Welcome to the world of dumb-user interface management.........

I appreciate the pep talk Doc, it makes me feel a little bit better about some of the questions i'm posting here. I know that these type forums are the best places to learn form others "in the trenches" as well. so comments like minty's defiantly help, in fact minty has been helping me a lot here lately. My coding skills are the best but with practice and persistence and a lot of help from this forum i'll hopefully be good enough to get done what i need to.

 

[jdraw]

I agree with what Doc has posted. And would emphasize that it's difficult (read that as practically impossible) to out guess what a user might do. Whether it's just confusion, uncertainty, can't read, missed the training sessions, or outright malicious --be prepared. Sometimes a dialog is useful where an entry/selection is made and a response/confirmation is presented.
eg: Please select your query type Job, or Tool or Part.
You have selected____whatever they chose. (Let's say Job)
Please enter the Job number..... and yadayada
Often useful with the casual/infrequent user or new user.

You might have an option group with 3 options, and a dialog based on their initial choice.
Preventing invalid choices/entries as close to source as possible will help reduce such a bad choice affecting records in your database.

As you will learn, getting users to test various scenarios during analysis and design and testing with good/frequent feedback cycles is a positive way to find the "wrinkles" early.

As one poster says --we try to make it foolproof, but there's just too many fools.

Good luck

 

[static]

the user's were inputting the wrong information into the search bar thus corrupting the data stored in the records.

This doesn't make any sense. Filter criteria for a recordset won't corrupt the data.

 

[JJSHEP89]

This doesn't make any sense. Filter criteria for a recordset won't corrupt the data.

it wasnt the filter itself that was corrupting the data. when the users input the tool number it sets that value to a public variable which is assigned to the tool number field when they enter maintenance log entries, as well as other stuff.

 

[jdraw]

It appears from comments so far that there may be other "logic related" issues.
It might be more productive to "tighten-up" the interface, and training such that users can't get to record creation. Force them back to the start and ensure all selections/entries to do whatever hey intend to do are validated before moving on.

Good luck.