Advanced Find Box on Form Access 2003

[greeni91]

I am currently trying to put a find box at the top of a form I have created for reviewing machining methods. I have setup the box to show the Part Number field and take you to that record when selected, but I want to filter the selection of Part Numbers based on certain criteria that I have filled out on various records.

I want to know how to word my SQL/Query statement to show only parts that are similar to the one I am viewing at that moment in time. e.g. I only want to see parts with the same material type and form that are the same size and have the same critical drawing features.

[NeoPa]

Tell me if Example Filtering on a Form helps Sandy. If not we can look in more detail and help you find a solution I'm sure :)

[greeni91]

Thanks for the Help NeoPa,

I have tweeked the code on the Example Filtering on a form page and got it to work for the majority of my fields, but I want to do filtering by Yes/No boxes as well as text and numeric fields. The total amount of fields I wish to filter by are as below: (This is the format it will follow)

FieldName (Form) - FieldName (Table) - Type of Field

VisualDescripti on - Visual Description - Text (Drop Down Box)
MaterialForm - Material Form - Text (Drop Down Box)
MaterialType - Material Type - Text
Size - Size - Text (Drop Down Box)
CriticalPart? - Critical Part? - Yes/No (Boolean)
SealFins? - Seal Fins? - Yes/No (Boolean)
Runout? - Runout? - Yes/No (Boolean)
FaceGrooves? - Face Grooves - Yes/No (Boolean)
Roundness? - Roundness? - Yes/No (Boolean)
IntGrooves? - Int Grooves? - Yes/No (Boolean)
Concentricity? - Concentricity? - Yes/No (Boolean)
ExtGrooves? - Ext Grooves? - Yes/No (Boolean)
Flatness? - Flatness? - Yes/No (Boolean)
Splines? - Splines? - Yes/No (Boolean)
ThinWall? - Thin Wall? - Yes/No (Boolean)
ThickWall? - Thick Wall? - Yes/No (Boolean)

I couldn't work out how I would word the Filter control to take into account all the fields mentioned above.

Thanks Again,

/Sandy

[NeoPa]

Booleans behave very similarly to numeric items. No delimiters are required. However, it is misguided to compare boolean values with other values to produce a test. Generally when comparing A with B, the test is designed to produce a boolean result. Booleans need no such test to produce a boolean result. They are already of that form. Hence, the following is perfectly sensible :

SELECT *
FROM   [MyTable]
WHERE  [MyBoolean]

How this fits into your code is generally quite straightforward , but if you'd like some more pointers perhaps you could post some example code illustrating where you're at at the moment.

Filtering can be done as hierarchical of course, or on a more flat basis - If there is data there then filter on it, otherwise ignore and process next item - and your code may indicate how you intend to approach it.

[greeni91]

This is the code I have placed into my form (DevelopmentRep orts) at the moment, I have placed the code the same as VisualDescripti on for my booleans at the moment. The thing I am finding about this is that it has no connection to my "Find Box" called Filter.

Option Compare Database
Option Explicit
  
Private Sub VisualDescription_AfterUpdate()
    Call CheckFilter
End Sub
      
Private Sub MaterialForm_AfterUpdate()
    Call CheckFilter
End Sub
  
Private Sub MaterialType_AfterUpdate()
    Call CheckFilter
End Sub

Private Sub Size_AfterUpdate()
    Call CheckFilter
End Sub

Private Sub CriticalPart_AfterUpdate()
    Call CheckFilter
End Sub

Private Sub SealFins_AfterUpdate()
    Call CheckFilter
End Sub

Private Sub Runout_AfterUpdate()
    Call CheckFilter
End Sub

Private Sub FaceGrooves_AfterUpdate()
    Call CheckFilter
End Sub

Private Sub Roundness_AfterUpdate()
    Call CheckFilter
End Sub

Private Sub IntGrooves_AfterUpdate()
    Call CheckFilter
End Sub

Private Sub Concentricity_AfterUpdate()
    Call CheckFilter
End Sub

Private Sub ExtGrooves_AfterUpdate()
    Call CheckFilter
End Sub

Private Sub Flatness_AfterUpdate()
    Call CheckFilter
End Sub

Private Sub Splines_AfterUpdate()
    Call CheckFilter
End Sub

Private Sub ThinWall_AfterUpdate()
    Call CheckFilter
End Sub

Private Sub ThickWall_AfterUpdate()
    Call CheckFilter
End Sub
  

Private Sub CheckFilter()
    Dim strFilter As String, strOldFilter As String
  
    strOldFilter = Me.Filter
    If Me!VisualDescription > "" Then _
        strFilter = strFilter & _
                    " AND ([Visual Description] Like '" & _
                    Me!VisualDescription & "*')"
    
    If Me!MaterialForm > "" Then _
        strFilter = strFilter & _
                    " AND ([Material Form] Like '" & _
                    Me!MaterialForm & "*')"

    If Me!MaterialType > "" Then _
        strFilter = strFilter & _
                    " AND ([Material Type] Like '" & _
                    Me!MaterialType & "*')"

    If Me!Size > "" Then _
        strFilter = strFilter & _
                    " AND ([Size] Like '" & _
                    Me!Size & "*')"

    If Me!CriticalPart > "" Then _
        strFilter = strFilter & _
                    " AND ([Critical Part?] Like '" & _
                    Me!CriticalPart & "*')"

    If Me!SealFins > "" Then _
        strFilter = strFilter & _
                    " AND ([Seal Fins?] Like '" & _
                    Me!SealFins & "*')"

    If Me!Runout > "" Then _
        strFilter = strFilter & _
                    " AND ([RunouT?] Like '" & _
                    Me!Runout & "*')"

    If Me!FaceGrooves > "" Then _
        strFilter = strFilter & _
                    " AND ([Face Grooves?] Like '" & _
                    Me!FaceGrooves & "*')"

    If Me!Roundness > "" Then _
        strFilter = strFilter & _
                    " AND ([Roundness?] Like '" & _
                    Me!Roundness & "*')"

    If Me!IntGrooves > "" Then _
        strFilter = strFilter & _
                    " AND ([Int Grooves?] Like '" & _
                    Me!IntGrooves & "*')"

    If Me!Concentricity > "" Then _
        strFilter = strFilter & _
                    " AND ([Concentricity?] Like '" & _
                    Me!Concentricity & "*')"

    If Me!ExtGrooves > "" Then _
        strFilter = strFilter & _
                    " AND ([Ext Grooves?] Like '" & _
                    Me!ExtGrooves & "*')"

    If Me!Flatness > "" Then _
        strFilter = strFilter & _
                    " AND ([Flatness?] Like '" & _
                    Me!Flatness & "*')"

    If Me!Splines > "" Then _
        strFilter = strFilter & _
                    " AND ([Splines?] Like '" & _
                    Me!Splines & "*')"

    If Me!ThinWall > "" Then _
        strFilter = strFilter & _
                    " AND ([Thin Wall?] Like '" & _
                    Me!ThinWall & "*')"

    If Me!ThickWall > "" Then _
        strFilter = strFilter & _
                    " AND ([Thick Wall?] Like '" & _
                    Me!ThickWall & "*')"
    

    'Debug.Print ".Filter = '" & strOldFilter & "' - ";
    'Debug.Print "strFilter = '" & strFilter & " '"
    'Tidy up results and apply IF NECESSARY


    If strFilter > "" Then strFilter = Mid(strFilter, 6)
    If strFilter <> strOldFilter Then
        Me.Filter = strFilter
        Me.FilterOn = (strFilter > "")
    End If
End Sub

Hope this gives you better look at what I'm trying to do, usually with me it is the simple things I seem to miss.

Thanks,

/Sandy

[NeoPa]

OK. Let's have a look at one of them in more detail.

Let's take Critical Part as an illustration. I would need to understand better what Me.CriticalPart is, and what values it can return (A freeform TextBox control or maybe a ComboBox with TRUE/FALSE/NULL options, or maybe even YES/NO/NULL options), but we'll make an initial stab at it. I'll assume for now that the values in this control are strings, but this may not be the case. Boolean values could also be used depending on your form design. :

    If Me!CriticalPart > "" Then _
        strFilter = strFilter & _
                    " AND ([Critical Part?] = " & _
                    Me.CriticalPart & ")"

TRUE/FALSE, YES/NO, etc boolean literal values, though they look like text, are actually names representing numeric/boolean values, and as such, don't require delimiters.

EG. The following would be correct SQL :

WHERE [BooleanField]=TRUE

[greeni91]

I have changed my "Drawing Features" to numeric form since they are all booleans, (CriticalPart, Splines, IntGrooves, etc.) but I don't know if it is working.

As mentioned above there is no reference in any of the code to provide my Find Box on my form with the Filter I have created when entering values, so all in all my find box constantly appears with all the part numbers in my form/table.

Another thing I have just thought on is that if some of these boxes are empty, yet the ones that are filled in are the same as on another record I would wish to view them. This means that if one record had the vast majority of the boxes ticked and I had only ticked, say, 2 of the 4 boxes that record had on another record then I would want it to be part of my list of parts that I could skip to.

Thanks,

/Sandy

[NeoPa]

None of that made any sense Sandy (I'm afraid). I'm looking for responses to what I post, at the same level of detail, so we can get one thing sorted before moving on to other issues knowing we're both on the same page. What I'm getting is another flood of information without any easy way to read the context (without which it is not a lot of use).

If you have a response to my last post please submit it. I need to know where you are with what I'm saying. Making changes other than what I'm suggesting while in the middle of a discussion does not aid communication. In fact it will make finding a solution very hard work indeed.

I don't think this issue should be particularly complicated. I'm sure we can get it working, but we do need to communicate a bit more effectively.

[greeni91]

Sorry it didn't make any sense,

I changed some of my fields to the code you provided above:

    If Me!CriticalPart > "" Then _ 
        strFilter = strFilter & _ 
                    " AND ([Critical Part?] = " & _ 
                    Me.CriticalPart & ")"

It didn't make any difference to my search field which still displays all the Part Numbers available from all the records. This was what I was saying about no reference to my find box on the form.

From the code I posted earlier, from line 93 to line 151 has been changed to the new code above. All of these fields are my Yes/No tickboxes from my form.

Thanks for the Help,

/Sandy

[NeoPa]

Sandy,

Try to respond directly to the questions. It really does make it so much easier to communicate clearly (I'm not trying to criticise you - I'm simply trying to direct you to a more profitable way of working.) Let me outline the areas that still confuse me (hence stopping my ability to help) :

1. My understanding at the moment is that you basically have a form with various unbound controls that enable you to specify certain criteria about the records you may want to review. These records are shown in a List- or ComboBox control and you select an individual item from here to see the item in its entirety on this same form. Please confirm this basic understanding (or clarify if this is wrong).
2. What type are the controls where you specify the boolean values required (EG. TextBox/ComboBox/etc)?
3. What values would you expect to be contained in these controls (EG. TRUE/FALSE/NULL/YES/NO/etc)? Please list all possible vales.
4. Are the values all text, or may some of them possibly be numeric (Particularly important for boolean type values TRUE/FALSE/etc)?
5. In your posted code, please uncomment line #155 and post the result displayed in your post, as well as the value contained in the Me.CriticalPart control when you use it. This isn't working and a closer look at the details may well help.

I'm sure we can progress with this information.

[greeni91]

Okay here goes again,

All the controls on my form are bound to a table called "Developmen t Reports", the only unbound control I have is the FindBox (combo box) at the top corner of my page. I am following a for dummies book on how to create a box that will show all parts that are similar to the one I am currently viewing on the form.

In the controls that are bound there is 3 Drop Down boxes which are saved as a numeric value in my table, yet display text on my form. I was using lookup fields for these 3 controls.

Next I have one text box which is automatically filled in with a value using a Dlookup function in the forms code.

After the all other fields are tickboxes (all field names with a ? at the end). That is why I have said they are Boolean because they can only return TRUE or FALSE.

I don't quite understand what you mean in your 5th question but when I use the form, every time I change a field value that has the CheckFilter connected to it my form throws me to a new record and won't let me go back to change anything. When I look at the taskbar it show that the Filter is on and that's not what I was looking for... I wanted it to do it after e.g. I save the record.

I hope this answers your questions...
Thanks,

/Sandy

[NeoPa]

We seem to have some problems here. They are various and I'll try to treat them in order, at least in the same order as they pertain to the 5 questions from my previous post.

1. This is a big one.
   You are not using unbound controls here it seems, but ones bound to an existing record showing on your form. This will mean that, to select different records to see, you need to update your data. I cannot say strongly enough that this approach should be avoided. If you really must have settings that reflect the latest record viewed, consider the situation as outlined in my question #1 from post #10, and just ensure that every time a record is displayed (and updated if you like) the current values are copied across to your unbound filter controls.
   I cannot even conceive of a situation where filtering on live data as you are would make sense.
2. CheckBoxes. That is clear and I can work with that.
3. They have three possible values : 0; -1; Null. 0 can be treated as FALSE or NO too, but not as "FALSE" or "NO". -1 can be treated as TRUE or YES, but not as "TRUE" or "YES".
4. Values are never text. They are always Boolean/Numeric (or Null).
5. In response to your comments, the code currently does this. If you want the code to filter just the ComboBox where you select the record you require, that can be changed. Currently though, it applies the filter directly to the form, which explains this behaviour.
   
   As far as providing the info I requested goes, uncommenting simply involves removing the quote (') character at the front of the line. When this line executes it will put some textual information in the Immediate Pane (Ctrl-G to see this). I would like you to go there and copy (Select the text and use Ctrl-C to copy it) this and paste it into your next post so that I can see what it did.

See Debugging in VBA for more details about the VBA (Visual Basic for Applications) IDE (Integrated Development Environment).

[greeni91]

Okay, This is what I got when I ran the line in the immediate window:

Debug.Print "strFilter = '" & strFilter & " '"
strFilter = ' '

As you can see I'm not very good at wording things, as seen in previous posts, but your comment on filtering the selection of parts in my FindBox rather than the form sounds more like what I need if it is best to avoid the method I'm going for at the moment. (Filtering on Live Data)

Thanks

/Sandy

[NeoPa]

Did you just run it as shown in the Immediate Pane? or did you uncomment it and allow it to run as part of the code as I suggested. As it depends on the previous code to run it will not give any result if run simply from the Immediate Pane.

I'm going to assume for now that you did the former, and explain how you need to go about producing the data I'm after :

1. Edit the code as suggested (Remove the quote from line #155).
2. Make a change somewhere to cause the code to be run. Updating any of the CheckBoxes should do that.
3. Copy the results that should have appeared in the Immediate Pane.
4. Post this to me here for me to review.

[greeni91]

Sorry about that,

I am quite new to this "programmin g" in access. Here is the code I got following your steps:

strFilter = ' AND ([Visual Description] Like '3*') AND
 ([Material Form] Like '2*') AND ([Material Type] Like 'Inconel*') AND 
([Size] Like '1*') AND ([Critical Part?] = -1) AND ([Seal Fins?] = -1) AND ([Runout?] = 0) AND ([Face Grooves?] = 0) AND ([Roundness?] = -1) AND 
([Int Grooves?] = -1) AND ([Concentricity?] = 0) AND ([Ext Grooves?] = -1) AND ([Flatness?] = -1) AND ([Splines?] = -1) AND ([Thin Wall?] = -1) AND 
([Thick Wall?] = 0) '

Hope this is what you were looking for

/Sandy