Error with my search

[CEB]

Hi Folks,
I have a search option on my form that allows the user to search a table by either first name or last name. They double click in the list box on the name they want. I am using the exact same code in another one but it doesn't seem to work. The error message I get is " The Microsoft jet database engine doesn't recognise (the name I selected) as a valid field name or expression"
I think that the error is in the double click event in the code. This is as follows:
_____________________________
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 "Priests"

Set rst = Forms![Priests].RecordsetClone

rst.FindFirst "[LastName] = " & Me.SearchList
Forms![Priests].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
______________________________
Any suggestions greatly appreciated.
Thanks,
COlin.

 

[CEB]

Hi Folks,
Sorry for bumping my question again. It went down to page 2 and I'm hoping someone new might see it today. I'm under pressure to get this search working. The message I'm getting is still the same. I was wondering does it need a primary key as the table I'm using is old and has none set to begin with??
Again, apologies for reposting .
Thanks,
Colin.

 

[NewfieSarah]

understand

I understand what you are going through, I am having search problems of my own, I have used this search in multiple places some places it work and others it doesnt.. GRR.. now I am getting errors, for the file names and I dont know how to fix it.. the code I am using is:

Private Sub Form_Open(Cancel As Integer)
'This will cleard the form to load as normal when reloaded after a search
Me!lstResult.RowSource = " "

End Sub

Private Sub lstResult_DblClick(Cancel As Integer)
'this will display the form for that specific record
Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Computer Info"

stLinkCriteria = "[Service Tag]=" & "'" & Me![lstResult] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub

Private Sub txtServiceTag_AfterUpdate()
'this will search for the record in the table and list it in the list box
Dim strSQL As String
strSQL = "SELECT DISTINCT [Computer Name], [Computer Id],[Inventory Number],[Emp Number],[Computer Type],[Service Tag],[Driver],[Hard Drive Capacity],[System Date],[System Time],[Cache],[Cpu Speed],[Fax/Modem],[Graphics Cards],[I/O Ports],[Monitors],[Optical Devices], [Control devices],[Operating System],[RAM],[Sound Card],[System Bus],[Comments] FROM Computer Info"
strSQ = strSQL & " WHERE [Service Tag], Like '" & Replace(Me!txtComputerName, "'", "''") & "*'"
strSQL = strSQL & " ORDER BY 1"
Me!lstResult.RowSource = strSQL
End Sub

maybe this will help you..

 

[CEB]

Thank you Sarah for your reply. I got some code from this site a couple of months ago and it works great! What I have is a textbox that the user can enter first or last name (or part of) of a person they need to find. The list box changes as they enter the letters. They can then double click on the person they want and the original form comes up with all the details.
The entire code for this is (except the queries):
______________________________________
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 "Priests"

Set rst = Forms![Priests].RecordsetClone

rst.FindFirst "[LastName] = " & Me.SearchList
Forms![Priests].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
___________________________________

There is not a need to place the details into each individual textbox. It's something small, I'm sure. Thanks again for your help...it's starting to drive me crazy!!
Colin.

 

[NewfieSarah]

you are welcome, but what did you mean by this?? can you explain this a little more. Thanks

"There is not a need to place the details into each individual textbox. It's something small, I'm sure."

 

[CEB]

Hi Sarah,
I'm sorry, I misunderstood your code. I now know what it's doing. If you are having problems with file names when you use it in other places, do you have to include the table names along with the field names (example)
________________________________________________
stLinkCriteria = "[TableName]![Service Tag]=" & "'" & Me![lstResult] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub
_________________________________________________
This is just a thought Sarah...I'm far from being pro!!!
Colin.

 

[ColinEssex]

Sarah - I've already told you about this

here's the example

Col

 

[CEB]

Hi Colin,
I'm sure that's where I got the code originally. I have used the code after Update on the company example and still get the same error message.
_______________________________
Private Sub SearchList_AfterUpdate()

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[LastName] = " & Me.SearchList
Me.Bookmark = rs.Bookmark

End Sub
______________________________________

"The Microsoft jet database engine doesn't recognise (the name I selected) as a valid field name or expression"
Any thoughts on this?
Thanks,
Colin

 

[ColinEssex]

Yes, I changed the code slighty. It was a while back I did it, I can't remember if the original code failed or not

I added a hidden TextBox to the form and called it Search2. This is populated each time a letter is added to the Combo using this code I put in the OnChange of the ComboBox. The Listbox is also requeried after each letter is added to the combo to gradually reduce the list.

Make sure you reference the hidden textBox (Search2) in the query that populates the ListBox though.

Private Sub Search_Change()
Dim vSearchString As String 

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

Works a treat I have it searching through 5 different fields.

Col

 

[CEB]

Hi Colin,
Yes you did change the code and that work great on the other table I needed it for!! However, on this table (a single table) it doesn't work. The difference now is I'm searching for Last name and First Name only and am selecting Lastname on the double click event in the list box. Do you think that there is a problem if the field I'm looking for is a string and not unique and the table has no primary key?
Colin

 

[NewfieSarah]

Hey I am looking at the search example database can someone elplain this code??

Private Sub Combo74_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[VendorID] = " & Str(Me![Combo74])
Me.Bookmark = rs.Bookmark

End Sub