set form based on query to VBA variable (1 Viewer)

[JJSHEP89]

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.

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.

 

[Cronk]

The where part of the docmd.openform has to be based on a field name in the form's recordsource
ie
DoCmd.OpenForm "qryDieBook", acNormal, , "TM_ToolNumber ='" & strToolNumber & "'", acFormReadOnly, acWindowNormal

Incidentally, a string variable cannot contain a Null as in
strSearch = Me.txtDB2_ToolSearch will give an error if the text box is null. Use a variant.

 

[JJSHEP89]

Thanks Cronk!

so i changed the strSearch to a variant type and the error's went away. However the form is not populating the data when the code is run. i've tried the following DoCmd statements

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

the first line pop's up a seperate entry box asking for the value of "txtDB2_ToolSearch".
the second two both open the form but with no data.

Would it be simpler to eliminate the physical query and just generate the SQL to set the recordsource from the VBA itself?

 

[Cronk]

What is the recordsource for your form qryDieBook?

If it includes the field TM_ToolNumber and it is a text field, then the form should open at the record with the value contained in strToolNumber (and if you now have declared that variable to be a variant, it would be good practice to rename it varToolNumber).

If that does not work and you don't want to post data, then create a blank database and import just the form in question and post that.

 

[jdraw]

In addition to Cronk's question, do you really have a Form named qryDieBook?
When you say, people are confused with

item number with the job number which is basically the item number

that's a sign that users have not been trained or retrained.
There really shouldn't be confusion for the user.

I'm a little confused that a user would enter "-17h".
Most common approach for user searching is to select terms/chars from a drop down.
Or a FindAsYouType approach to continually narrow down a topic, but I don't know your environment or business.

Good luck.

 

[JJSHEP89]

In addition to Cronk's question, do you really have a Form named qryDieBook?

yes, Its labeled this way to remind me that it's a form based on a query.

I'm a little confused that a user would enter "-17h".
Most common approach for user searching is to select terms/chars from a drop down.
Or a FindAsYouType approach to continually narrow down a topic, but I don't know your environment or business.

the job number is not just the "-17h" in itself, say an item number is AA100, the corresponding job number would be AA100-17h. We have 2 systems one uses the job number which is the more frequently used system which is used for accounting and labor tracking. then the database in question. the users only input the wrong information for a search out of habit from the other system. there are other area's where an autocomplete is used, just not here.

 

[jdraw]

So in the system you are dealing with --could the user enter AA100?
And, if so, would they find the record AA100-17h?
Or are there multiple records starting AA100?

 

[JJSHEP89]

So in the system you are dealing with --could the user enter AA100?
And, if so, would they find the record AA100-17h?
Or are there multiple records starting AA100?

yes they could enter and find AA100 but could not find AA100-17h, hence the reason for the search function to remove the "-17h" from the text string.

 

[jdraw]

Can you mock up 10 records? Doesn't have to be real data, just the fields and format.

What is your experience with Access? I did see that this issue has been bugging you for a number of months...

 

[Frothingslosh]

Have you considering an alternate route?

Instead of a text box, you could have them enter the item number into a combo box, instead. Have the row source in the combo box be based on a query that lists every item number in your database, and set the 'Limit to List' property to true. Then you avoid any issues with the suffix, because the user will be notified as soon as they try to do leave the control that the entry is invalid. In fact, it will even auto-populate as they type, which will also help get across that the job number isn't what you're looking for since they can see that the numbers appearing don't have that suffix.

 

[JJSHEP89]

Can you mock up 10 records? Doesn't have to be real data, just the fields and format.

What is your experience with Access? I did see that this issue has been bugging you for a number of months...

BEGINNER - INTERMEDIATE based on the second post in this sticky http://www.access-programmers.co.uk/forums/showthread.php?t=253162

its been bugging me for months because Im not able to carve out enough time to focus on this project and actually accomplish something of merit. my access abilities get alot better when i can work with it for months at a time, as it stands right now its 4-5 hours every couple of weeks, and by that time i've forgotten what i did the last time it was worked on.

 

[JJSHEP89]

Have you considering an alternate route?

Instead of a text box, you could have them enter the item number into a combo box, instead. Have the row source in the combo box be based on a query that lists every item number in your database, and set the 'Limit to List' property to true. Then you avoid any issues with the suffix, because the user will be notified as soon as they try to do leave the control that the entry is invalid. In fact, it will even auto-populate as they type, which will also help get across that the job number isn't what you're looking for since they can see that the numbers appearing don't have that suffix.

so just something i probably should have included in the first post, the search box looks up based on part number or item number (Tool Number) so essentially one item/tool will manufacture 1-10 different part numbers, different departments use either the item/tool number or the part number to retrieve the data and the search box will accept either form and return the appropriate information.

 

[JJSHEP89]

so im trying to upload a stripped database but even zipped its still 4MB. im not sure whats keeping it so large but i'll get it uploaded soon.

 

[JJSHEP89]

ok so this is weird and i dont want to get too far off topic, but i made a copy of the original database, created new tables, the relevant ones, identical to the originals that were linked just without the data. stripped it down, compact & repaired and the database wouldn't get any smaller than 4.5MB. however when i created a new blank database and simply copied over the tables, queries, etc. it wound up being less than 700kB... why is that?

The stripped database is attached, hopefully i havent removed too much in an attempt to decrease size. the "test search" button is the button im attempting to code with the new method to remove the job number suffix, the just plain "search" button is what they are using now.

 

[Cronk]

Firstly, it is most confusing when you use the same name for both a query and a form. More experienced developers will prefix the object or variable with something to indicate what is is, not what it is based on.

You have a query which is used as a data source for a form and the query is in turn based on parameter values from the form. Remove these.

Your query also makes reference to some non existent fields, or at least non existent in the tables you supplied. You need to fix that.

In the code for the search button you have this

    If ValidateEntry(strSearch) = "0" Then
        .....
    Else
        strToolNumber = ValidateEntry(strSearch)
    End If

This is inefficient and inelegant.

Your original question was how to open a form showing the particular record.

The code is in that form ie it is already opened.

Just filter the full data set to show what is required, viz

me.filter = "TM_ToolNumber ='" & strToolNumber & "'"
me.filteron = true

 

[JJSHEP89]

the fields were probably to other tables that were deleted.

the form is usually left open on all the workstations around the plant(s). the user simply inputs the new search term and the form refreshes with the new appropriate data. the filter option sounds like it might be a better route. im pretty new to this stuff so i'm not familiar with all the elegant methods to use. this whole database is full of inefficiencies as well that i've been correcting as i learn more. but the users still need to have access and the functionality while its under construction.

 

[JJSHEP89]

so i've finally got this working and figured i would post the solution here. Basically going off what cronk said in his last post about filtering the data, i essentially removed the query itself and replaced it with an SQL line within the VBA code. I kept trying to use a DoCmd to opend the form but the form was already open.... :banghead: so i replaced those lines with a line to update the forms recordsource property based off the SQL a few lines up. Now whenever the end user enters a tool number, Job #, part number etc. the form will return the proper data.

Private Sub btnToolSearchTest_Click()
Dim strSearch As Variant
Dim strSQL 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
        strToolNumber = ValidateEntry(strSearch)
    End If
           
'Section2:::: Set the query data to the information required.
    strSQL = "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 ='" & strToolNumber & "';"
           
'Section3:::: Refresh the main form in read only window, with new query data.
    Form.RecordSource = strSQL
            
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

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

my coding has been called inefficient and ineloquent, which im sure it is... but im still pretty new to all of this so if you have some suggestions for improvements im all ears, just do me a favor and thoroughly explain it so i can understand and learn from it.

Thanks again for everyone's help!

 

[Cronk]

I'm glad you achieved what you wanted.

In relation to "inefficient and ineloquent", that was not meant as criticism of you, rather than my feedback. I cringe when I look back on work I did years ago that deserves the same comment.

 

[JJSHEP89]

I'm glad you achieved what you wanted.

In relation to "inefficient and ineloquent", that was not meant as criticism of you, rather than my feedback. I cringe when I look back on work I did years ago that deserves the same comment.

no worries, i didnt take it as such. I know where my abilities stand and the only way i will learn is to identify my mistakes and learn from them. i know there are alot of people on here who know way more than i would ever need to so im hoping i can learn something from them.