Search Function

[Oldsoftboss]

That is why I use a popup search form. It sort of hides the main form and lets the user know they are searching. No matter what you do, someone will complain about how it works !!

The sample I posted an easily be made to search multiple fields and narrows down the list as you type.

Dave

 

[cubsguy]

I have the search fields at the top of my main form. I was able to use that bit of code and change it from setting the form to "" to setting the specific field to "".

I have no coding background at all and don't know any of the commands but if I see some code that works I can usually modifiy for it my needs. I'm just not able to write anything from scratch, I usually create a bunch of macro and cut the code from them....in case you guys were wondering how I could get the search function to work in my db and not know how to set a field to ""

Thanks for the help!

 

[igr11]

Yet another question about search

Hi.

I have a data entry form set-up in my Access database. Is there any way to program a single search box in this form that will allow me to search the entire database for anything and take me to the first record with matching info highlighted? Think Mac OS spotlight or vista search. Search as your type functionality would also be super! I want the database users to be able to quickly pull up records without much thought.

thanks,
igor

 

[Rabbie]

You shouldn't allow your users direct access to anything except forms and reports. If they have direct access to Tables there is a big risk of data getting corrupted.

You should be able to pull up customer details for example using a combo box to select the Customer name. You can find other info in a similar way. See the Northwind sample database shipped with Access to se how this is done.

 

[Kurth]

hi!
I know this is digging up a very old topic, but the files here have helped me a lot.

I have almost successfully inplemented the multiple field search features
as in the attached file frm post #3! Just a few problems remain.
The display box in the advanced search form will display the relevant records, but

(a)
only the bits of the record that actually match the description and

(b)
it requires me to enter values into a field that has a lookup as a data source through a message box upon opening, before the actual form even pops up...

could anybody help me with this? It should not be too complicated, as even I managed to understand most of the content. (Yes, I consider myself a novice in case you wonder...)

Cheers

Chris

PS: if a quick solution could be found, you would really help me and my company a lot. this DB I am creating will be of good use. Thanks a lot in advance!

 

[Oldsoftboss]

Maybe the sample Db with the problem highlighted would be good.

Dave

 

[Kurth]

Oah, I finally got it to work. Was just going to upload the file, when I discovered an error in my programing. (Yes, I did debug several times before) But thank you for the quick reply nonetheless!
I have, however encountered another problem, that is definitely NOT a typing error since the function itself works perfectly.

(I will insert the uploaded file as soon as I get home. I can not upload from this computer. Company firewall stops me! Maybe in the meantime you can help me using this description?)


Everytime I doubleclick in the a record in the subform of the form corresponding yo your "tblAdvancedSearch" of "Search Multiple Fields.mdb", it links to the correct form. But this form is has a subform, which is lists the relevant entries from another table.
I discovered, that doubleclick uses the correct matID to find the record, instead of jumping to the correct record in the main form (as desired), it jumps to the record in the subform. Ususally, this does not make a difference, but if there is no relevant record in the subform, it will just jump to the first record in the list.
How do I resolve this problem?

____EDIT____
I have not yet managed to upload the relevant file, but I noticed that the jump to the correct record works differently, when I use a built button to reference to the same form (although from another form). Here, it will not jump to the first entry, but to a completely new entry form (with default values and no ID assigned in the main form), when there is no corresponding match.

 

[CidRich]

I am extremely new to access and I have been tasked with creating a search function. I have viewed all the examples that have been posted and the only question I currently have is how to actually implement the code listed on these posts into my datat base.

Maybe a Access for dummies section would be brilliant. I suppose what I am trying to say is I like the very first example listed but I am at aloss when it comes to actually developing the search function.

 

[georgedwilkinson]

I'm not sure how we can help if you can't understand our answers. Maybe you should ask a simpler question that might lead to your understanding of more complex issues?

 

[rob.low]

Thanks for all the good examples of the search pages
this has helped me with my database

what a great forum

 

[RCheesley]

Thanks for this hugely useful thread, I've got the form using multiple values working perfectly in my database, you've saved me a huuuuuuuuge amount of time and effort!

Woohoo

Ruth

 

[rawnblade7]

Hi,

I have some access and vb experience under my belt, but it was a year back and I've forgotten a LOT of it. I'm helping a non-profit organization right now and I've just moved their db over to a sql server. The searches have become incredibly slow now and I think it is due to using the built-in access search. So I was thinking of writing a basic search hoping that would go faster than the access one. I'd post the database but its quite large and has sensitive information. The main page has a ton of information with many subforms. Could anyone help me with writing a search and then posting each different column to all the textboxes? I'd really appreciate it. I've actually done this before but I'm in Australia right now and all my notes are back home in the U.S. Thanks ahead of time.

andrew

 

[miketong]

Hi, i am very new here. I saw the sample of the 'search multiple fields' DB, however, i still cannot implementing it. I put my DB here, can anyone help me?

 

[Raff2145]

I am very new to Access, and know little about VBA- can somebody help me? I am in the process of setting up a database, and the idea is to create a form to allow a user to look up information in the database and edit the results that are presented as a result of that search.

I've created a form linked to my database, and would like to add a search function that allows the user to search for records that meet two search criteria (I'd like to have a combo box that shows all info in the first column of the database and another combo box showing all info in the second column- and a button next to both that initiates the search). I'm trying to make it so that it will show the record and all fields associated with that record just below the search function so that the user can edit the information right there on the same form.

Like I said, I only just started using Access and this form will help me a lot! Can someone show me how to do this?

 

[Johnta]

When using the search multiple fields example ive gotten everything to work except the search text box to effect the fields in the searchlist. Could it be that it wont work if I have a massive number of fields? Here's my code:
Option Compare Database
Option Explicit
Private Sub Combo12_AfterUpdate()
On Error GoTo Err_Combo12_AfterUpdate
'There are 2 ways to do show a record on a separare form
'1 Filter the form
'2 Open the form and goto the specific record

'Example 1
If Not IsNull(Me.Combo12) Then 'if the combo box is empty, do nothing
DoCmd.OpenForm "TRquery", , , "TR# = " & Me.Combo12
End If

'Example 2 --- comment out the above lines, uncomment below
'Dim rs As Object
'If Not IsNull(Me.Combo12) Then 'if the combo box is empty, do nothing
' DoCmd.OpenForm "frmSales"
' Set rs = Forms!frmSales.Recordset.Clone
' rs.FindFirst "CustID = " & Me.Combo12
' Forms!frmSales.Bookmark = rs.Bookmark
'End If
Exit_Combo12_AfterUpdate:
Exit Sub
Err_Combo12_AfterUpdate:
MsgBox Err.Description, , " Client Database"
Resume Exit_Combo12_AfterUpdate
End Sub
Private Sub SearchList_DblClick(Cancel As Integer)
On Error GoTo Err_SearchList_DblClick
Dim db As DAO.Database
Dim rst As DAO.Recordset
DoCmd.OpenForm "TRquery"
Set rst = Forms!TRquery.Recordset.Clone
rst.FindFirst "TR# = " & Me.SearchList
Forms!TRquery.Bookmark = rst.Bookmark
DoCmd.Close acForm, Me.Name
Exit_SearchList_DblClick:
Exit Sub
Err_SearchList_DblClick:
MsgBox Err.Description
Resume Exit_SearchList_DblClick
End Sub
Private Sub txtSearch_Change()
Dim vSearchString As String
vSearchString = Me.txtSearch.Text
Me.txtSearch2.Value = vSearchString
Me.SearchList.Requery
End Sub
Private Sub cmdClose_Click()
On Error GoTo Err_cmdClose_Click
DoCmd.Close acForm, Me.Name
Exit_cmdClose_Click:
Exit Sub
Err_cmdClose_Click:
MsgBox Err.Description
Resume Exit_cmdClose_Click

End Sub

 

[Johnta]

Okay I resloved that problem now when I try to open the form or enter data into the text box I get a "Enter parameter" text box open. This is probably a simple fix, but I havent used Access in years. Though it does work other than that.

 

[saip15]

I just used this form and it works great! Thanks Oldsoftboss!

Make sure you read through the entire code again and replace the strings with your DB info, (frmSales with the form that you want to use.) That's all I did.

 

[Johnta]

I finally got it to work, thx oldsoftboss. Now I need to somehow make a report with a similiar search function that could print out a report based on say all the information on a set customer, which will usually consist of many records.

 

[saip15]

I have a similar question: How can I print out only the contents of the table on a separate sheet? How can I print the entire list if the table is longer than the form?

 

[Acelink]

I am new to the whole Access scene. I don't quite understand what rst/dao are so I got really confused looking at the code mentioned ealier.

At first I tried using the code in my databse. Surpise... it didn't work. It failed saying something like 'invaid field name'.

Dim db As DAO.Database
Dim rst As DAO.Recordset

DoCmd.OpenForm "Frm_Patient_Overview"

Set rst = Forms!Frm_Patient_Overview.Recordset.Clone

rst.FindFirst "MR = " & Me.SearchResults
Forms!Frm_Patient_Overview.Bookmark = rst.Bookmark

I got flustered trying to make it work. In my anger I went and tried something simple. IT WORKS!

    DoCmd.OpenForm "Frm_Patient_Overview"
    DoCmd.GoToControl "MR"
    DoCmd.FindRecord Me.SearchResults
    DoCmd.Close acForm, Me.Name

What exactly is the difference between the two codes? What did I do wrong with the first code?