Filtering a Form

[topher83]

Hi
Im trying to create a form to search a hardware database for items, the form consists of two boxes, a dropdown box and a text box. The user needs to choose a field from the dropdown box and then enter the text the want to search for in the text box and the press search
Its comming up results have been filtered however its not displaying them
Can anyone see any problems with my code
Thanks

Private Sub cmdSearch_Click()

    If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
        MsgBox "You must select a field to search."
        
    ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
        MsgBox "You must enter a search stringtxt."
        
    Else
    
        'Generate search criteria
        GCriteria = "[" & cboSearchField.Value & "] LIKE '*" & txtSearchString & "*'"
        
        'Filter frmCustomers based on search criteria
        Forms_frmHardware.RecordSource = "SELECT * FROM tblHardware WHERE " & GCriteria
        Form_frmHardware.Caption = " tblHardware (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
        
        
        'Close frmSearch
        
        
        MsgBox "Results have been filtered."
        
    End If
    
End Sub

[sierra7]

Hi
before we get too bogged down in detail, are you

1. trying to open another form to show the selected items or
2. is the filter criteria being set in the current form's Header and you want to list the items in the Detail section below it ?

S7

[Stewart Ross]

You appear to be referring to two forms (possibly) or the forms collection (maybe) instead of a specific instance of a form in your code - Forms_frmHardwa re, instead of Form_frmHardwar e.

Have you tried the far simpler approach of defining a filter then applying it? Redefining the recordsource as a whole is unnecessary if all you want to do is apply a Where clause to the current recordset.

Using the shorthand of Me to refer to the current form you can refer to the filter property of the form as follows:

Me.Filter = GCriteria
Me.FilterOn = True

-Stewart

...
Forms_frmHardwa re.RecordSource = "SELECT * FROM tblHardware WHERE " & GCriteria
Form_frmHardwar e.Caption = " tblHardware (" & cboSearchField. Value & " contains '*" & txtSearchString & "*')"

[NeoPa]

Nicely spotted Stewart.
As he says, Forms() is a collection object which always contains a list of the currently open forms. It's worth noting (as Stewart did) that these are object instances rather than objects per se.
The actual internal name of an Access form object is the name we normally refer to it by (what we see in the Database window) with "Form_" prepended.
So, for instance, a form created as frmThis would be referred to internally by the database as Form_frmThis. When an instance is opened, there would also be an entry in Forms() called frmThis (Forms("frmThis ") or Forms!frmThis). If you look in the Project Explorer (Ctrl-R from VBA debugger) window of the VBA debugger (Alt-F11 from Access) you will see the form names shown in this way.

As a further note, it's always a good idea to compile code before proceeding with the testing. The compiler will pick up a lot of these types of errors for you, *generally* with a meaningful explanation.

PS. Stewart's point about using the .Filter property rather than changing the RecordSource is also a VERY good one. It's a really useful thing to know.

[sierra7]

Hi

I think Stewart and myself were answering this about the same time. I also picked up a comment to "Filter frmCustomers . . " in the code so thought I would take a step back until I understood what was going on.

Interesting concept to define a field in the ComboBox then serach for a match from the textBox though.

S7

[PhilippeM]

I am still very new at this (first timer basicily).. I am also trying (with numerous efforts) to create a search box in a form. I have made a dropdown box and a textbox to enter the searchcriteria, as is discussed in this case. I have copy pasted the code above and am trying to understand what Steward posted:
Me.Filter = GCriteria
Me.FilterOn = True
How do I create this filter, and do I put it in this code, or in a module where I refer to?
Further assistance would be very nice because I am spending far to much time on this.

I also have another problem. I have created 2 forms, named 'Contact List' and 'Contact Details'. The form Contact List, has a subform with a table: 'Contact subform', which displays all the contacts in a table. I also created a button (in the subform) to go from the list to the details, now I would like to go to the record that is selected in the subform's table.
Could I also have some help in this matter?

Thank you very much!

[NeoPa]

I am still very new at this (first timer basicily).. I am also trying (with numerous efforts) to create a search box in a form. I have made a dropdown box and a textbox to enter the searchcriteria, as is discussed in this case. I have copy pasted the code above and am trying to understand what Steward posted:
Me.Filter = GCriteria
Me.FilterOn = True
How do I create this filter, and do I put it in this code, or in a module where I refer to?
Further assistance would be very nice because I am spending far to much time on this.
...

GCriteria, in this case, would be a string variable.

That's about as much as I can say in this thread without allowing the thread to be hijacked onto your problem (rather than the original quetionner's). If you want further help with this, please post a separate thread.

...
I also have another problem. I have created 2 forms, named 'Contact List' and 'Contact Details'. The form Contact List, has a subform with a table: 'Contact subform', which displays all the contacts in a table. I also created a button (in the subform) to go from the list to the details, now I would like to go to the record that is selected in the subform's table.
Could I also have some help in this matter?

Thank you very much!

Not here you can't Philippe.

If you want THIS question answered you must start a separate thread for it. We don't allow threads to be hijacked onto other subjects.

It's probably better to separate these questions into separate threads too.