Familiar old Search From Problems

[Igor via AccessMonster.com]

HI..

Im really hoping someone can help me out. Ive scoured the forums and can see
my problem is not a new one. But still cannot find a solution to my set of
circumstances. Please help..

I have a form that Im using to search my db. (access 2003). It has 5 search
fields, (one textbox and 4 combobox). My query uses the parameters supplied
by these search fields, there is a subform based on the query on this form
too. My query criteria is Like [Forms]![SearchQuery]![boxName] & "*" Or Is
Null.

This is working except it is not returning records where there are blank
fields.

I want to place a button on the form that filters the query and populates the
subform with the results. Is this a macro? I can't figure this out. At the
moment, the subform populates once and once only.

Can someone shed some light on this for me? Its been 3 days now, Ive had this
problem..

Thanks

 

[Allen Browne]

Yes, you often see people teaching/recommending the approach you are using,
yet it has exactly the flaw you identified: it fails to consider nulls.

You can work around the problem if you change the WHERE clause of the query
so that the expression evaluates to True when the search box is null. The
WHERE clause (in SQL View), would look like this:
WHERE (([Forms]![SearchQuery]![boxName] Is Null)
OR (Field1 Like [Forms]![SearchQuery]![boxName] & "*"))

By the time you do this 5 times in a query though, it makes a really messy
WHERE clause, is quite error prone (e.g. you must get the brackets right),
and it is inefficient to execute.

Consider an alternative approach that builds the WHERE clause from *only*
the boxes where the user actually enters something. You can then use this
WHERE clause as the Filter of a form, as the WhereCondition of a report, or
even by concatenating it into the rest of the SQL string and assigning it to
the SQL property of a QueryDef.

For an example of that aproach download this little example for Access 2000
and above:
http://allenbrowne.com/unlinked/Search2000.zip
The sample database applies it as the Filter for a form.

 

[Igor via AccessMonster.com]

That is a fantastic example Allen. I think Im going to follow your template
closely. I haven't used filter by forms? Are they idiotproof-able? The only
thing, is purely for layout purposes, I would like to layout the search
fields alongside the form, essentially making the search form as a subform.
Can you describe how I can reference the search boxes to the subform?

Many thanks.

 

[Allen Browne]

If you are writing code in the subform's module, you can refer to Combo1 on
the main form like this:
Me.Parent.Combo1

If you are writing code in the main form's module, and you want to apply the
result to the filter of the form in the subform control named Sub1:
Me.[Sub1].Form.Filter = strWhere
Me.[Sub1].Form.FilterOn = True

If the ".Form" bit is new, see:
http://allenbrowne.com/casu-04.html

If your main form is unbound, you should be fine. However, if you apply a
Filter to the main form and to the subform as well, Access will get
confused:
http://allenbrowne.com/bug-02.html

 

[Igor via AccessMonster.com]

Hi Allen, Thanks for the immense work you do on these forums. Can I lean on
you a bit more?


Okay... just to make sure Im on the right track before I rush into this....

Recap.. My Main form has the search fields, my subform is the results.

I create a filter by form button on my main form (beneath my search fields).
The on_click event should run the filter. Which looks like this:

If Not IsNull(Me.cboSurname) Then
strWhere = strWhere & "([Surname] = """ & Me.cboSurname & """) AND "
End If

And just create duplicates of this string for each combobox.

Where do I create the reference that the [Surname] field is in the subform?

As for the reset button. Im a bit lost in terms of applying your code to my
own form. I have a few other controls on my form that do not apply to this
search feature. Will it work if I just copy and paste?

For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next


By the way, I HATE resorting to using someone elses work as my own. But I am
l-o-s-t . Thanks for your time.

 

[Igor via AccessMonster.com]

for example... does this look right?

Private Sub Find_Click()

If Not IsNull(Me.cboSurname) Then
strWhere = strWhere & "(Me.[subfrmMember].[Surname] = """ & Me.
cboSurname & """) AND "
End If

If Not IsNull(Me.cboPosition) Then
strWhere = strWhere & "(Me.[subfrmMember].[Position] = """ & Me.
cboPosition & """) AND "
End If

End Sub

Thanks

 

[Allen Browne]

Once you have built the strWhere string, you code:
Me.[Sub1].Form.Filter = strWhere
Since the subform you are applying the string to already has the Surname
field, there is no need to change the string. Each of the If blocks stays
just the same.

As for the Reset code, you have the filter controls in the Detail section,
not in the Form Header section. The main form has no bound controls, so just
change the first line to:
For Each ctl in Me.Controls

*Learning* from someone else's work is a good idea. From your questions, I
think that's what you are striving to do.

 

[Allen Browne]

No. You don't need to change the string like that, because once it is
applied to the subform's Filter, the subform understands the fields.

 

[Iona via AccessMonster.com]

Hi allen,

I hope you take a look at this thread. I have managed to get the search up
and running, by using ur suggestions with a little 'tweaking'. I can't seem
to get the Refresh button to clear the search fields. It requeries my listbox
fine. Im using ur code, but... Im not sure whats wrong. Its running fine,
just nothing is happening. Thanks.

Private Sub New_Search_Click()
On Error GoTo Err_New_Search_Click
Me.FilterOn = False

List121.RowSource = "SELECT tblContactInfo.ContactNumber," _
& "tblContactInfo.Prefix, tblContactInfo.[First Name]," _
& "tblContactInfo.Surname, tblContactInfo.Position," _
& "tblContactInfo.[Job Title], tblContactInfo.Hospitals," _
& "tblContactInfo.[Employing Agency] FROM tblContactInfo"

List121.Requery

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_New_Search_Click:
Exit Sub

Err_New_Search_Click:
MsgBox Err.Description
Resume Exit_New_Search_Click

'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next

'Remove the form's filter.
Me.FilterOn = False

End Sub

 

[Igor via AccessMonster.com]

Hi allen,

I hope you take a look at this thread. I have managed to get the search up
and running, by using ur suggestions with a little 'tweaking'. I can't seem
to get the Refresh button to clear the search fields. It requeries my listbox
fine. Im using ur code, but... Im not sure whats wrong. Its running fine,
just nothing is happening. Thanks.

Private Sub New_Search_Click()
On Error GoTo Err_New_Search_Click
Me.FilterOn = False

List121.RowSource = "SELECT tblContactInfo.ContactNumber," _
& "tblContactInfo.Prefix, tblContactInfo.[First Name]," _
& "tblContactInfo.Surname, tblContactInfo.Position," _
& "tblContactInfo.[Job Title], tblContactInfo.Hospitals," _
& "tblContactInfo.[Employing Agency] FROM tblContactInfo"

List121.Requery

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_New_Search_Click:
Exit Sub

Err_New_Search_Click:
MsgBox Err.Description
Resume Exit_New_Search_Click

'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next

'Remove the form's filter.
Me.FilterOn = False

End Sub

 

[Allen Browne]

The code you posted seems to have 2 subs run together.

Try adding a new command button named cmdReset, with this event procedure:

Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
Case acCheckBox
ctl.Value = False
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

Note: if these

 

[Igor via AccessMonster.com]

So you CAN'T run two procedures from one button?

 

[Allen Browne]

You can, but not if there is an:
Exit Sub
half way through so that the 2nd half never executes.

 

[Igor via AccessMonster.com]

Ah.. Thankyou sir.