ok so I've built a database that is used by everyone at my company and I've got one section of code that has been bugging me off an on for months now.
A quick explanation of the issue i'm trying to solve here.... I have a form based off a query where the user enters a value in a textbox and hits a button called "search" (basically searches one table for a specific item number) which populates the form with the necessary data related to that item (industrial tools to be exact). the issue is the user frequently confuses the item number with the job number which is basically the item number with a "-17h" where the number represents the year and the letter designates the plant location. which doesn't work in my form.
My code is below where i have written a function to remove any job number designation from the search and then return the remainder to the query to open and populate the form. However, the issue is in the DoCmd.open form, the input values are going in just fine but the code crashes every time. with or without the error handler turned on. It would be nice to eliminate the query as well and replace with some SQL within the code (which is what i started to try by setting the recordsource property) but it just seemed to open a bigger can of worms.
There's a ton of red tape that revolves around this database so before anyone asks i don't think i'll be able to attach a copy here.
A quick explanation of the issue i'm trying to solve here.... I have a form based off a query where the user enters a value in a textbox and hits a button called "search" (basically searches one table for a specific item number) which populates the form with the necessary data related to that item (industrial tools to be exact). the issue is the user frequently confuses the item number with the job number which is basically the item number with a "-17h" where the number represents the year and the letter designates the plant location. which doesn't work in my form.
My code is below where i have written a function to remove any job number designation from the search and then return the remainder to the query to open and populate the form. However, the issue is in the DoCmd.open form, the input values are going in just fine but the code crashes every time. with or without the error handler turned on. It would be nice to eliminate the query as well and replace with some SQL within the code (which is what i started to try by setting the recordsource property) but it just seemed to open a bigger can of worms.
Code:
Private Sub btnToolSearchTest_Click()
Dim strSearch As String
'Dim strToolNumber 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.Detail.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.Detail.Visible = False
Exit Sub
Else
strToolNumber = ValidateEntry(strSearch)
End If
'Section2:::: Open the main form in read only window.
' Set Form.RecordSource = "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" FROM ToolMatrix INNER JOIN PartMatrix ON ToolMatrix.TM_ToolNumber = PartMatrix.PM_ToolNumber _
& WHERE (((ToolMatrix.TM_ToolNumber)='" & strToolNumber & "';"
DoCmd.OpenForm "qryDieBook", acNormal, , "txtDB2_ToolSearch ='" & strToolNumber & "'", acFormReadOnly, acWindowNormal
' DoCmd.OpenForm "qryDieBook", acNormal, , "ToolMatrix.TM_ToolNumber ='" & strToolNumber & "'", acFormReadOnly, acWindowNormal
Me.Detail.Visible = True
' DoCmd.RefreshRecord
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
Private Sub cboSelectPartNumber_AfterUpdate()
' This subroutine displays the actual instructions (long text data type) in a larger text box on the form. _
the instructions field is selected from the 2 column ComboBox, which has the second column hidden
Me.txtChangeOverInstructions = Me.cboSelectPartNumber.Column(1)
Me.txtChangeOverNotes = Me.cboSelectPartNumber.Column(2)
End Sub
There's a ton of red tape that revolves around this database so before anyone asks i don't think i'll be able to attach a copy here.