473,289 Members | 1,729 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

Search Facility

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
3 2182
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: bissatch | last post by:
Hi, I would like to add a search engine facility onto my website. This way, if anybody uses it to search my site they will be presented with a page of results where their search query matched...
4
by: Speaking Clock | last post by:
Hi, I have recently taken over the responsibility for a medium sized .org website that has been written entirely in .asp. Because there is a highly restrictive proprietary Content Management...
5
by: Deryck | last post by:
Hi, I am working on an e-commerce site. It uses a CMS. One requirement is to have a static off-line version so that the company's sales reps can visit customers and take orders on a laptop...
4
by: KiwiBrian | last post by:
What do you recommend for an application I can host on a web site that can provide a site search facility? I thought I read that Google had a new aplication for this but I can't find any reference...
2
by: GMK | last post by:
HI ALL I'M COMING FROM A FOX PRO BACKGROUND WHERE I COULD IN A VERY EASY WAY CREATE A SEACH FACILITY BY PRESSING N F1 AND ANOTHER WINDOW WILL DIRECTLY APPEAR WHICH WOULD LET THE USER TO SEARCH...
2
by: Alan Silver | last post by:
Hello, I am in the planning stages of a new ASP.NET web site, and would like to know if it's possible to add a search facility to it. The site will be a mixture of static pages and ones...
6
by: jej1216 | last post by:
I am trying to put together a PHP search page in which the user can select none, one, two, or three fields to search, and then the results php will build the SQL with dynamic where caluses to reflect...
3
by: tonydraper | last post by:
I have built a website and for some reason the search facility has stoppped working? When I carry out a job search the page that it redirects to comes up with this. Source Error: Line...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.