Search Facility

[Elainie]

I would like to search a form with many fields on it, with out using
the search facility through access. Througth a drop down list if
possible.... How would I go about this? How could I also create this
from a top bar menu option too...?

Elaine

[ARC]

Hi Elaine,

The following code will cycle through all controls on a form...I actually go
deeper with this routine, and cycle through the controls on up to 2 subforms
deep, so if you need that to, let me know. The code below actually formats
all date fields to the user's preference, but what you're really intersted
in is if the control type is a textbox, then you would add code for your
search, otherwise, you could ignore the other fields:
For Each ctl In f.Controls
If ctl.ControlType = acTextBox Then
If f(ctl.Name).For mat = "Short Date" Then
If nomask = 0 Then f(ctl.Name).Inp utMask = f2!DateInputMas k
f(ctl.Name).For mat = f2!DateStyle
End If
'If f(ctl.Name).fon tname <DefFont Then
' f(ctl.Name).fon tname = DefFont
'End If
ElseIf ctl.ControlType = acLabel Or ctl.ControlType = acCommandButton
Then
If TransYN = -1 And f(ctl.Name).Vis ible = -1 Then
CurrCap = f(ctl.Name).Cap tion
criteria = "lblPhrase = " & Chr(34) & CurrCap & Chr(34)
f(ctl.Name).Cap tion = CheckTranslateD b(rs, criteria, CurrCap)
End If
'If f(ctl.Name).fon tname <DefFont Then
' f(ctl.Name).fon tname = DefFont
'End If
ElseIf ctl.ControlType = acSubform Then
If f(ctl.Name).Sou rceObject = "" Then
GoTo Contin
end if
' i took out the rest of this code that cycled through the controls
on any subforms
end if
Next Ctrl

[King Ron]

On May 9, 9:48 am, Elainie <Elaine.Macint. [e-mail] s.ukwrote:

I would like to search a form with many fields on it, with out using
the search facility through access. Througth a drop down list if
possible.... How would I go about this? How could I also create this
from a top bar menu option too...?

Elaine

ARC's solution looks pretty complex: I think you can do what you want
by adding two .ComboBox es and a button to your form.

..cbxFields.Row SourceType = 'Field List'
..cbxFields.Row Source = 'myTable' (the table you want to search)

On open, this box will be populated with a list of the fields in the
myTable base table.

..cbxValues.Row SourceType = 'Table/Query'
..cbxValues.Row Source = "SELECT " & Me.cbxFields & " FROM myTable WHERE
" & Me.cbxFields & " IS NOT NULL GROUP BY " & Me.cbxFields

Add an event to cbxFields_After Update:

Private Sub cbxFields_After Update
Me.cbxValues.Re query
End Sub

So after you select which field to search, the second ComboBox is
populated with a list of all of the unique values in the field chosen
in cbxFields

As you type in the Value combo box, the selection will narrow. Add a
button to click when you have selected the value you want to search
for. In that button's _OnClick

Private Sub btnFilter_OnCli ck()
Me.Filter = Me.cbxFields & " = " & Me.cbxValues
Me.FilterOn = True
End Sub
This will filter the rows returned from the base table to those
matching the value in the filter setting. This is simplified somewhat
- you will have to add buttons to clear the filter and reset the
comboboxes, and you will also have to alter the filter syntax slightly
to accomodate text, numeric and date datatypes (damn it!). This should
be a start.

Ron, King of Chi

[ARC]

I agree that it's overkill, maybe. That code is made to cylcle through all
controls on any form. So if this is for a specific form only, then King's
post would definetely be easier...
"King Ron" <Ki************ ***@covad.netwr ote in message
news:11******** **************@ e65g2000hsc.goo glegroups.com.. .

On May 9, 9:48 am, Elainie <Elaine.Macint. [e-mail] s.ukwrote:

>I would like to search a form with many fields on it, with out using
the search facility through access. Througth a drop down list if
possible.... How would I go about this? How could I also create this
from a top bar menu option too...?

Elaine

ARC's solution looks pretty complex: I think you can do what you want
by adding two .ComboBox es and a button to your form.

.cbxFields.RowS ourceType = 'Field List'
.cbxFields.RowS ource = 'myTable' (the table you want to search)

On open, this box will be populated with a list of the fields in the
myTable base table.

.cbxValues.RowS ourceType = 'Table/Query'
.cbxValues.RowS ource = "SELECT " & Me.cbxFields & " FROM myTable WHERE
" & Me.cbxFields & " IS NOT NULL GROUP BY " & Me.cbxFields

Add an event to cbxFields_After Update:

Private Sub cbxFields_After Update
Me.cbxValues.Re query
End Sub

So after you select which field to search, the second ComboBox is
populated with a list of all of the unique values in the field chosen
in cbxFields

As you type in the Value combo box, the selection will narrow. Add a
button to click when you have selected the value you want to search
for. In that button's _OnClick

Private Sub btnFilter_OnCli ck()
Me.Filter = Me.cbxFields & " = " & Me.cbxValues
Me.FilterOn = True
End Sub
This will filter the rows returned from the base table to those
matching the value in the filter setting. This is simplified somewhat
- you will have to add buttons to clear the filter and reset the
comboboxes, and you will also have to alter the filter syntax slightly
to accomodate text, numeric and date datatypes (damn it!). This should
be a start.

Ron, King of Chi