So im new to the forum and still fairly green on my VBA, just about everything i know has been self taught (im sure yall know how that goes). what i am struggling with is a code module that I'm building to work with a database to track manufacturing tooling. i have put together a number of read and write forms with buttons etc. for the shop workers to use for viewing and tracking tooling data. the problem i am having is with a module for a search box where a user can input a tool number or a part number (each tool produces different parts, some tools produce multiple different parts) and the vba will lookup and return the proper form with data corresponding to the tool.
I have 2 separate tables one containing all the specific tools, and one the other containing all the specific parts. they are linked with a one to many relationship.
Here is my code, I have commented out quite a bit of it while ive been trouble shooting but you'll get the idea. Im having trouble when i get to the dlookup for the tables. i believe im missing something where i need to define the database or the recordset maybe? this is where my knowlege fades.
I am expecting the code to take the user input in the form of a part number, job number, etc. and return the tool number which the query uses to populate the forms. I have the job number portion working just fine but the DLookup to return the tool number from the part number is whats failing.
I have 2 separate tables one containing all the specific tools, and one the other containing all the specific parts. they are linked with a one to many relationship.
Here is my code, I have commented out quite a bit of it while ive been trouble shooting but you'll get the idea. Im having trouble when i get to the dlookup for the tables. i believe im missing something where i need to define the database or the recordset maybe? this is where my knowlege fades.
I am expecting the code to take the user input in the form of a part number, job number, etc. and return the tool number which the query uses to populate the forms. I have the job number portion working just fine but the DLookup to return the tool number from the part number is whats failing.
Code:
Sub VBATest()
Dim strSearch As String
Dim strToolNumber As Variant
Dim intCount As Integer
On Error GoTo ErrorHandler:
strSearch = InputBox("Enter a Tool Number or Part 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$(strSearch, 1)
Case "H", "V", "S"
If Mid(strSearch, Len(strSearch) - 1, 1) <= 9 And Mid(strSearch, Len(strSearch) - 2, 1) <= 9 And Mid(strSearch, Len(strSearch) - 3, 1) = "-" Then
strToolNumber = Left(strSearch, Len(strSearch) - 4)
Else
strToolNumber = strSearch
End If
End Select
strToolNumber = DLookup("PM_ToolNumber", "PartMatrix", "PM_PartNumber =" & strSearch)
' MsgBox "tool number: " & strToolNumber, , "Tool Number"
' If DLookup("PM_ToolNumber", "PartMatrix", _
' "PM_PartNumber =" & strSearch) Is Not Null Then
' strToolNumber = DLookup("PM_ToolNumber", "PartMatrix", _
' "PM_PartNumber =" & strSearch)
' MsgBox "The Tool Number for " & strSearch & "is " & strToolNumber
'
' ElseIf DCount("PM_PartNumber", "PartMatrix", _
' "PM_PartNumber =" & strSearch) > 0 Then
' MsgBox "This search term in part matrix."
' Else
' MsgBox "This tool is available."
' End If
'Open the main form in read only window.
' DoCmd.OpenForm "qryDieBook", acFormDS, , [Forms]![qryDieBook]![txtDB2_ToolSearch], acFormReadOnly, acWindowNormal
' DoCmd.RefreshRecord
Exit Sub
' If intCount < 0 Then
' DLookup("TM_ToolNumber", "ToolMatrix", _
TM_ToolNumber = strSearch) = Null Then
' DLookup("PM_PartNumber", "PartMatrix", _
PM_PartNumber = strSearch) = Null Then
' MsgBox "Tool Not Found.", vbOKOnly, "Invalid Entry"
' Else:
' strToolNumber = DLookup("PM_PartNumber" _
, , PM_ToolNumber = strSearch)
' MsgBox "Success! " & vbNewLine & _
"Tool Number: " & strSearch & vbNewLine & _
"Part Number: " & strToolNumber, vbOKOnly, "Message"
' End If
' 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
Last edited: