By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,604 Members | 858 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,604 IT Pros & Developers. It's quick & easy.

Search Facility

P: n/a
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

May 9 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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).Format = "Short Date" Then
If nomask = 0 Then f(ctl.Name).InputMask = f2!DateInputMask
f(ctl.Name).Format = f2!DateStyle
End If
'If f(ctl.Name).fontname <DefFont Then
' f(ctl.Name).fontname = DefFont
'End If
ElseIf ctl.ControlType = acLabel Or ctl.ControlType = acCommandButton
Then
If TransYN = -1 And f(ctl.Name).Visible = -1 Then
CurrCap = f(ctl.Name).Caption
criteria = "lblPhrase = " & Chr(34) & CurrCap & Chr(34)
f(ctl.Name).Caption = CheckTranslateDb(rs, criteria, CurrCap)
End If
'If f(ctl.Name).fontname <DefFont Then
' f(ctl.Name).fontname = DefFont
'End If
ElseIf ctl.ControlType = acSubform Then
If f(ctl.Name).SourceObject = "" 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
May 9 '07 #2

P: n/a
On May 9, 9:48 am, Elainie <Elaine.Macint...@bsc.wales.nhs.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.RowSourceType = 'Field List'
..cbxFields.RowSource = '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.RowSourceType = 'Table/Query'
..cbxValues.RowSource = "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_AfterUpdate
Me.cbxValues.Requery
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_OnClick()
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
May 9 '07 #3

P: n/a
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.netwrote in message
news:11**********************@e65g2000hsc.googlegr oups.com...
On May 9, 9:48 am, Elainie <Elaine.Macint...@bsc.wales.nhs.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.RowSourceType = 'Field List'
.cbxFields.RowSource = '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.RowSourceType = 'Table/Query'
.cbxValues.RowSource = "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_AfterUpdate
Me.cbxValues.Requery
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_OnClick()
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


May 9 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.