Let me preface this by saying i am new to the ADO library and all its methods and properties. However, we have moved our backend to a MySQL server and from what i currently understand the ADO library is better to use with ODBC connections than the DAO because it doesn't use the MS Jet engine.....correct me if my thinking is wrong here.
As for the issue,
I have written the following function that takes a user's input from a "Search" textbox on a form and verifies it against data is two separate tables and returns a value based on whichever data matches that in the table..
Quick background on the table structure. Two tables, ToolMatrix & PartMatrix, Tools are used to produced parts, there can be multiple tools that produce the same part & multiple parts produced from the same tool.
The issue that i've run into is when a search returns multiple values. I've been trying to have my function output the results of the recordset to a simple listbox but its not working. I'm currently getting type mismatch errors.
As for the issue,
I have written the following function that takes a user's input from a "Search" textbox on a form and verifies it against data is two separate tables and returns a value based on whichever data matches that in the table..
Quick background on the table structure. Two tables, ToolMatrix & PartMatrix, Tools are used to produced parts, there can be multiple tools that produce the same part & multiple parts produced from the same tool.
The issue that i've run into is when a search returns multiple values. I've been trying to have my function output the results of the recordset to a simple listbox but its not working. I'm currently getting type mismatch errors.
Code:
Public varToolNumber, varPartNumber As Variant
Public Function ValidateEntry(varEntry As Variant) As Variant
Dim varToolNumber As Variant
Dim Conn As ADODB.Connection
Dim rst As ADODB.Recordset
'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:::: Establish ADO Connection for Access & Create Recordset instance
Set Conn = CurrentProject.Connection
Set rst = New ADODB.Recordset
With rst
Set .ActiveConnection = Conn
.Source = "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"
.LockType = adLockReadOnly
.CursorType = adOpenStatic
.Open
End With
'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
If rst.RecordCount > 1 Then
DoCmd.OpenForm "PopUp_MultiSelect" ', acNormal, , "partmatrix.PM_PartNumber = '" & varEntry & "'", acFormReadOnly, acWindowNormal
Forms("PopUp_MultiSelect").Controls("lstMultipleValues").RowSource = rst
Else
varToolNumber = DLookup("PM_ToolNumber", "PartMatrix", _
"PM_PartNumber ='" & varEntry & "'")
End If
rst.Close
Conn.Close
Set rst = Nothing
Else: varToolNumber = "0"
End If
ValidateEntry = varToolNumber
End Function