Newbie needs some help with VBA Modules (1 Viewer)

[JJSHEP89]

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.

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

 

[jdraw]

Can you give us a few more details of the processes and the things involved?
And create a copy of your database and zip it before posting.
It will be easier for readers to help you once they understand WHAT you are trying to do,
and can see your approach.

Welcome.

 

[JJSHEP89]

Can you give us a few more details of the processes and the things involved?
And create a copy of your database and zip it before posting.
It will be easier for readers to help you once they understand WHAT you are trying to do,
and can see your approach.

Welcome.

Sure, i'll try to be as detailed as possible. i wont be able to Zip the database however due to company restrictions and red tape with sharing data but i may be able to zip the front end of it with the forms etc.

what kind of detials are you looking for?

 

[jdraw]

The 30,000 ft overview of the "business facts".
Here are 2 samples to give the sort of level/detail to orient the readers.


Narrative 1
ZYX Laboratories requires an employee tracking database. They want to track information about employees, the employee's job history, and their certifications. Employee information includes first name, middle initial, last name, social security number, address, city, state, zip, home phone, cell phone, email address. Job history would include job title, job description, pay grade, pay range, salary, and date of promotion. For certifications, they want certification type and date achieved.
An employee can have multiple jobs over time, (ie, Analyst, Sr. Analyst, QA Administrator). Employees can also earn certifications necessary for their job.



Class Information narrative 2
The CIS 253 class wants to create a database to store information about the students in the class. Information will include student demographic information, contact information and course information and history.

Information about the students will include name, address, city, state, zip, phone, email, fax, college major, Social Security Number, and gender. Each student can have more than one phone number, email address or fax number.

Course Information includes course name, course number, number of credits, and grade received. Each student will take many courses in their college career. And naturally, each course will have many students enrolled.


It sounds like 3 tables might be used...

Tool --->ToolProducesPart<---Part

 

[JJSHEP89]

the database has roughly 20 tables total but they are all used for different things and come from different area's, some are an ODBC read only link to other systems and the rest are to an access backend. they are all used to track tooling and part related data. some the tables are listed below

Tool Matrix (tooling data such as type, size, Machine spec's, customer, etc.)
Part Matrix (part data such as tool that produces the part, part material, thickness, part type, etc.)
Tool maintenance log (tracks the data entered by our tool room reguarding maintenance)
Master BOM (contains the bill of materials for all tools)
Component orders (to track orders for tool components to be replaced or repaired etc.)

other tables are related such as employee's, suppliers, builders, etc. which are just basic tables used for reference elsewhere.

the database front end pulls from each one of these tables to display the information to the end user and allows for them to enter information through some buttons and pop up forms.

Ill attach a copy soon.

 

[JHB]

First of all, comment out the errorhandler until your code runs okay.
Second, I don't see the logic in that you have a Select Case statement where you assign strToolNumber with a value, and then after that use a Dlookup to assign strToolNumber with another value, can you explain that?
What problem do you've with the Dlookup, not getting the right value or ??

 

[JJSHEP89]

First of all, comment out the errorhandler until your code runs okay.
Second, I don't see the logic in that you have a Select Case statement where you assign strToolNumber with a value, and then after that use a Dlookup to assign strToolNumber with another value, can you explain that?
What problem do you've with the Dlookup, not getting the right value or ??

the select case statement is only to remove a job number designation if the user was to enter it accidentally instead of the tool number. the job number is essentially a dash followed by the year and a letter designating the plant location. ex. AA100 would be the tool number but the job number would be AA100-16H. the select case is to remove the job number from the string and return only the tool number. I could probably go through and remove the strToolNumber variable and just reassign the strSearch variable to lean up the code a bit. I added the strToolNumber variable so i could add a seperate watch to my code as i stepped through it, i just left it for now.

However, if the user was to enter a part number, which part number formats change with the customer, the Dlookup would need to find the corresponding tool number used to produce such part. the part numbers often contain dashes and end with S,H, or V Hence the reasoning for the elaborate Job# verification in the select case statement.

when i test my code with a part number i get a run-time error 3464 when i hit the Dlookup portion. i have a gut feeling that im missing a dimension or something that tells the code what database the dlookup needs to operate in, but as i said im still pretty green so im turning to the forums for help.

 

[jdraw]

So, your company builds tools for Customers?

Awaiting your attachment.

 

[JJSHEP89]

So, your company builds tools for Customers?

Awaiting your attachment.

we build the tools, the tools produce the finished parts and the finished parts are sold to customers.

im working on the attachment, i keep getting a "Security token Missing" error when i try and attach the file.

 

[Minty]

Without looking thouroughly at all of it - at a very basic level assuming strSearch is a string you need to enclose the search criteria in quotes;

strToolNumber = DLookup("PM_ToolNumber", "PartMatrix", "PM_PartNumber =[COLOR="red"]'[/COLOR]" & strSearch & [COLOR="Red"]"'"[/COLOR])

In any Lookup
Strings need quotes '" & yourString & "'"
Dates need the hash delimiter #" & yourdate & "#"
Numbers don't need a delimiter

 

[JJSHEP89]

Without looking thouroughly at all of it - at a very basic level assuming strSearch is a string you need to enclose the search criteria in quotes;

strToolNumber = DLookup("PM_ToolNumber", "PartMatrix", "PM_PartNumber =[COLOR="red"]'[/COLOR]" & strSearch & [COLOR="Red"]"'"[/COLOR])

In any Lookup
Strings need quotes '" & yourString & "'"
Dates need the hash delimiter #" & yourdate & "#"
Numbers don't need a delimiter

holy crap, it was seriously that easy.... the Dlookup is working just fine now, thanks! now i can move on to the next part of this module.

 

[JJSHEP89]

now im having issues with the second DLookup statement

    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

im getting run-time error 424

 

[Minty]

You need to use Not IsNull(Dlookup....) Is Not Null works in queries but not in VBA

 

[JJSHEP89]

thanks again minty!

 

[JJSHEP89]

so i have gotten everything working exactly as i need it to except for the portion of the code where i actually open the form to display all the data associated with the specific tool.

I have taken the previous code and broken it down into a public function so it can be used elsewhere (ive posted it all below) the issue is with the DoCmd.Openform in section2. the form is based on the query "qryDieBook_Info" where the WHERE condition is Toolmatrix.TM_ToolNumber = strToolNumber. the form seems to open up just fine but does not populate the way i would expect it to.

Sub VBATest()
Dim strSearch As String
Dim strToolNumber As Variant
Dim intCount As Integer

    strSearch = InputBox("Enter a Tool Number or Part Number.")
     
'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."
        Exit Sub
    End If
     
    strToolNumber = ValidateEntry(strSearch)
        MsgBox "The Tool Number for " & strSearch & " is " & strToolNumber
           
'Section2:::: Open the main form in read only window.
            DoCmd.OpenForm "qryDieBook", acNormal, "qryDieBook_Info", "ToolMatrix.TM_ToolNumber ='" & strToolNumber & "'", acFormReadOnly, acWindowNormal
            DoCmd.RefreshRecord

Here is the ValidateEntry() function

Public Function ValidateEntry(strEntry As String) As String
Dim strToolNumber As String
    
' 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$(strEntry, 1)
           Case "H", "V", "S"
                If Mid(strEntry, Len(strEntry) - 1, 1) <= 9 And Mid(strEntry, Len(strEntry) - 2, 1) <= 9 And Mid(strEntry, Len(strEntry) - 3, 1) = "-" Then
                            strEntry = Left(strEntry, Len(strEntry) - 4)
                End If
    End Select


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

 

[Minty]

Okay your logic is a little awry I think. ( it's late I haven't eaten and the "Old Speckled Hen" is very nice... So I'm probably wrong)

Section 2 - Shirely if one condition isn't met the other one must be ? In other words it's not an ElseIF , also if neither are true your function will return a null - why not return -1 or something that would indicate it's failed and act accordingly?

 

[JJSHEP89]

Okay your logic is a little awry I think. ( it's late I haven't eaten and the "Old Speckled Hen" is very nice... So I'm probably wrong)

Section 2 - Shirely if one condition isn't met the other one must be ? In other words it's not an ElseIF , also if neither are true your function will return a null - why not return -1 or something that would indicate it's failed and act accordingly?

Makes sense, you actually reminded me of one more condition that needs to be verified and that's if the user inputs a part number that does not yet exist within the tables. For that your strategy of returning a -1 would work. maybe just a 1 so it can be used as a Boolean value if i need to.

 

[JJSHEP89]

update:

when i run this code i am prompted with a pop up box asking to enter a parameter value, if i enter in the tool number then it opens the form as expected.

            DoCmd.OpenForm "qryDieBook", acNormal, , "ToolMatrix.TM_ToolNumber =" & strToolNumber, acFormReadOnly, acWindowNormal

so there must be something wrong with the '"toolMatrix.TM_ToolNumber=" & strToolNumber' section of the method

 

[jdraw]

Well earlier in Section 2 you said ToolNumber was a string--so you need to put it in quotes-- it seems.

"TM_ToolNumber ='" & strEntry & "'")

 

[Minty]

There is - you need to enclose the strToolNumber in single quotes as it is a string.

Remember the earlier post