Using a search box in a form

[John Big Booty]

Perhaps the discussion and sample I posted here, will give you some further inspiration.

 

[SShafeeq]

Sandbox's suggestion is good, however, i would prefer not to have a temptable, reason being if a user needs to make changes to the data that they have searched for, I assume that will not be possible.

Is there anyway to filter the table according to the search string entered in the txt box? Or using DoCmd.FindRecord?

 

[John Big Booty]

My apologies I missed this link in my previous post. Check the sample in my post #8.

 

[SShafeeq]

JBB! Your a genius! Thats what I was looking for to help me create a search. I will need a bit of help troubleshooting though.

Ok, so now adapting your code a little bit to what I required, I put the following code on the command button I mentioned earlier (btnSearch) on its OnClick event.

The code:

Private Sub btnSearch_Click()
 
Dim strFilter As String
 
Me.Refresh
 
Dim strCombo As String
strCombo = Me.cmbGoto
If strCombo = "Title" Then
strFilter = "[Title] like '*" & Me.txtGoTo & "* '"
Forms!LibCatalogue.Form.Filter = strFilter
 
[COLOR=green]'your code said to put Forms!FormName!SubFormName.Form.Filter, but i [/COLOR]
[COLOR=green]'dont have a subform, just a continuous form, which I dont think has a s[/COLOR]
[COLOR=green]'separate name, does it?[/COLOR]
 
Forms!LibCatalogue.Form.FilterOn = True
Me.txtGoTo.SetFocus
 
[COLOR=green]'right, this wasnt in your code but I kept getting an error of "cannot [/COLOR]
[COLOR=green]'reference a control unless it has focus[/COLOR]
 
Me.txtGoTo.SelStart = Len(Me.txtGoTo)
 
Else
 
[COLOR=green]'ok, from here on i have a bunch of if-else statements according to what the user choice in the combo box[/COLOR]
 
If strCombo = "Author or Source" Then
strFilter = "[AuthorSource] like '*" & Me.txtGoTo & "* '"
Forms!LibCatalogue.Form.Filter = strFilter
Forms!LibCatalogue.Form.FilterOn = True
Me.txtGoTo.SetFocus
Me.txtGoTo.SelStart = Len(Me.txtGoTo)
Else
 
If strCombo = "Publishers" Then
strFilter = "[Publishers] like '*" & Me.txtGoTo & "* '"
Forms!LibCatalogue.Form.Filter = strFilter
Forms!LibCatalogue.Form.FilterOn = True
Me.txtGoTo.SetFocus
Me.txtGoTo.SelStart = Len(Me.txtGoTo)
Else
 
If strCombo = "Date Published" Then
strFilter = "[Publishers] like '*" & Me.txtGoTo & "* '"
Forms!LibCatalogue.Form.Filter = strFilter
Forms!LibCatalogue.Form.FilterOn = True
Me.txtGoTo.SetFocus
Me.txtGoTo.SelStart = Len(Me.txtGoTo)
Else
 
If strCombo = "Publication Type" Then
strFilter = "[PublicationType] like '*" & Me.txtGoTo & "* '"
Forms!LibCatalogue.Form.Filter = strFilter
Forms!LibCatalogue.Form.FilterOn = True
Me.txtGoTo.SetFocus
Me.txtGoTo.SelStart = Len(Me.txtGoTo)
Else
 
End If
End If
End If
End If
End If
End Sub

So, yeah, I know the code is perhaps not the most intelligent, but appologies, I am still finding my feet in VBA.

My problem currently is, that the code works but sometimes. For example I have a bunch of documents that have the word "World" in them. So I did a test search of "World". the search only displayed one record (which i know isnt right). I did a few other test searchs and some display 10 records, and some 5, but its not all the records they are displaying, just some of them according relevant to the search.

What am I doing wrong?

 

[Galaxiom]

Because of the performance issues I found re-querying on a split database setup (with the back end on a network server, front end on a local computer), I found that populating a local temporary table with the search criteria from the BE database improved performance [/QUOTE]

Creating and dropping temp tables in the front end will cause bloat and greatly contribute to the potential for corruption.

Temp tables should never be written into either the backend or the front end but to a separate local database. This database can be deleted and recreated as required.

 

[Galaxiom]

'Disable Warning that data in "temp table" will be deleted and
'repopulated via a query with current data

Application.SetOption "Confirm Action Queries", 0
Application.SetOption "Confirm Document Deletions", 0
Application.SetOption "Confirm Record Changes", 0

This can be replaced with one line:

DoCmd.SetWarnings False

Renable them using the same command with a True argument.

It is important to realise that the warnings for the entire application are off. Reenabling them only when the form closes could leave warnings off unexpectedly if you move to another form.

It is better to reenable in the Exit section of the procedure. Always ensure the procedure uses the Exit. (Avoid the common bad practice of Exit Sub commands in the middle of the code.)

Ensure the error handler resumes to the Exit where the SetWarnings True command will be processed.

 

[Galaxiom]

That's a really useful way to search, but it seems to suffer significant performance issues on a split database over a network with it requerying the database after every change of character to update the list.

John's search example isn't suited to using the Change event in a "search as you type" arrangement because it is way too slow.

Filters actually work a lot better for searches.

 

[SShafeeq]

JJB, or anyone else, any suggestions related to my questions?

 

[John Big Booty]

Can you post a copy of your DB ('03 version for preference)?

 

[SShafeeq]

Here it is

 

[John Big Booty]

Firstly the example I provided in the other thread was for a filtering a form/sub-form set up. You have a continuous form, so your reference to the form name in the filter statement will simply be;

Me.Filter = strFilter

Your form is opening with a hard coded filter in place, you will need to remove that from the filter. You have no button to remove any filter.

Additionally you have an embedded space behind the second asterisk in your filter stament which should look like;

strFilter = "[Title] like '*" & Me.txtGoTo & "*'"

fix those two things and thing will work a little better for you.

Your table has null records in some fields and uses table level lookups and finally, and by no means the least of your problems your table is not normalised , this tutorial will help you come to grips with this concept.

You have a lot of wok ahead of you in rectifying these problems, and I suggest you start your own threads if you require any help in dealing with them.

 

[John Big Booty]

I've fixed the code so you can now filter on title, YOU will need to fix the rest of the code to work for the other options, along with the other problems noted in my previous post.