creating a more efficient search

[michellerobinso]

please see the example attached, it is a very basic search, double clicking on the return number opens that record.

what i would like to do is show the account name and date is the same box.

below is the code that i use. can someone please someone help me incorporate something into it?

rivate Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmSearchReturn"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdSearch_Click:
Exit Sub
Option Compare Database
Option Explicit

Private Sub List2_DblClick(Cancel As Integer)
Dim rs As Object

DoCmd.OpenForm "frmquery"

Set rs = Forms!frmquery.Recordset.Clone
rs.FindFirst "[RETURN NUMBER] = " & Str(Nz(Me![List2], 0))
If Not rs.EOF Then Forms!frmquery.Bookmark = rs.Bookmark

DoCmd.Close acForm, Me.Name

End Sub

Private Sub TxtSearch_Change()

Dim vSearchString As String

vSearchString = Me.TxtSearch.Text
'Me.txtSearch2.Value = vSearchString
'Me.List2.Requery
'Err_cmdSearch_Click:



End Sub

 

[ColinEssex]

Have a scout round - there are some excellent examples posted of search methods.
Some of which will search on multiple fields simultaneously and a ListBox will reduce its content in size as more search letters/numbers are entered. Then you double click on one to open the form to that record.

Col

 

[michellerobinso]

Well the searches that i have seen dont really do what i want

 

[fluffyozzy]

Do you mean to display the number, account name and date in the list box?

If so, all you have to do is to change your listbox to have more than 1 bound column and have all the fields you require in the record source of the listbox. I hope this makes sense and I have understood the problem correctly.

 

[michellerobinso]

that does make sence but i dont know how to add one?

can you help?

 

[fluffyozzy]

See the example - the code is slightly different to yours but I think you should understand it fairly easily.

I have based the listbox on a query and within that query, specified what values to look up as you're entering data into the search box. You can search by all three criteria you have specified, number, name and date and the listbox will shrink as you enter more letters in the search

Have a look at the listbox properties, specifically to the row source and bound columns and you will see how they work.

Let me know if you have anymore questions

 

[michellerobinso]

that is spot on for what i want to use this for.

your help is greatly appriciated

 

[fluffyozzy]

You're welcome! It's nice to be able to help for once instead of begging for help since I've joined LOL

I can't take all the credit though, I got the search facility from someone on this forum

 

[michellerobinso]

one quick question

Private Sub QuickSearch_AfterUpdate()

DoCmd.Requery
Me.RecordsetClone.FindFirst "[RETURN NUMBER] = " & Me![QuickSearch]
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
MsgBox "Could not locate [" & Me![QuickSearch] & "]"
End If

End Sub




the code doesnt appear to tell you where to open the form with the details on it. i really dont know anything about VB code. could anyone help?

 

[KeithG]

the code doesnt appear to tell you where to open the form with the details on it. i really dont know anything about VB code. could anyone help?

What do you mean where to open the form?

 

[michellerobinso]

well when the form is searching, when you double click on the record i would like it to open the queryform for example with that particular record.

 

[fluffyozzy]

You'll need a second event for that on the On Dbl Click of the listbox.

Open the Properties of the listbox on Design view - Event tab - On Dbl Click - create a new Event Procedure

And write something like:

Private Sub QuickSearch_DblClick()
DoCmd.OpenForm "FormName", acNormal
End Sub

where FormName is the name you have given to the form you want to open.

Hope this helps

 

[michellerobinso]

Thank you for your help but that doesnt actuallu open the particular form. the code i have found

rs.FindFirst "[MONTH][YEAR][RETURN NUMBER][ACCOUNT NO][CUSTOMER NAME] = " & Str(Nz(Me![QuickSearch], 0))

keeps coming up with

run time error 13

type mismatch.

i dont understand that?????? it looks right to me! lol

 

[KeithG]

"[MONTH][YEAR][RETURN NUMBER][ACCOUNT NO][CUSTOMER NAME]

Are these all fields? I believe you can only reference one field.

 

[michellerobinso]

Yes they are all the fields, the same error appears with just one reference.

 

[KeithG]

can you post your db?

 

[michellerobinso]

please see attached. the database it based on this search

 

[fluffyozzy]

I feel I'm close to solving this - but it is sorta doing my head in at the moment. I tried the code below on another search form I have and it works but it doesn't on yours.. Mmm, very puzzled!

Private Sub QuickSearch_DblClick(Cancel As Integer)
DoCmd.OpenForm "queryform", , "[Table1].[Number] = " & Me.QuickSearch.Column(3)
End Sub

Private Sub Search_Change()
Dim vSearchString As String

vSearchString = Search.Text
Search2.Value = vSearchString
Me.QuickSearch.Requery

End Sub

Private Sub QuickSearch_AfterUpdate()

DoCmd.Requery
Me.RecordsetClone.FindFirst "[Number] = " & Me![QuickSearch]
If Not Me.RecordsetClone.NoMatch Then
Me.Bookmark = Me.RecordsetClone.Bookmark
Else
MsgBox "Could not locate [" & Me![QuickSearch] & "]"
End If

End Sub

 

[michellerobinso]

when i use that code i get the error message attached.

 

[fluffyozzy]

Hi,

Yeah, I know it does give an error message, I have tried that code on another form I have with a similar search facility and it works fine, but somehow it doesn't on yours. That's why I am puzzled. Fairly sure, it's a teeny weeny mistake somewhere. I hope someone can find it for us lol..

I don't have access to my sample databases just now, but will have a look at it again as soon as possible.