Keyword Search with subforms

[jeffjohnson29]

I am really new to building access databases and using vbs and I have watched the how to from steve bishop on how to create a keyword search using subforms but for some reason it does not work for me and I am unsure where my mistake is. I have been able to get the search to function inside the subform which is how I have it now. attached is a sample of my database. below is the code I used when the search box and button was outside the subform.

Private Sub btnSearch_Click()
Dim SQL As String

SQL = "SELECT [Asset Inventory].[Service Tag], [Asset Inventory].[Location Name], [Asset Inventory].Name, [Asset Inventory].[Asset Number], [Asset Inventory].[Computer Model] " _
& "FROM [Asset Inventory] " _
& "WHERE Name LIKE '*" & Me.txtTerm & "*' " _
& " OR [Service Tag] LIKE '*" & Me.txtTerm & "*' " _
& " OR [Asset Number] LIKE '*" & Me.txtTerm & "*' " _
& "ORDER BY [Asset Inventory].[Asset Number] "

Me.subSearch.Form.RecordSource = SQL
Me.subSearch.Form.Requery

End Sub

My ultimate goal is to be able to search in that sub form and then be able to click on the desired record and have it autofill the fields in the form that correlate to that record.

Any help would be appreciated thank you in advance.

 

[Uncle Gizmo]

Is "txtTerm" and "btnSearch" on the main form?

 

[jeffjohnson29]

in the accdb that I uploaded first the "txtTerm" and the "btnSearch" are in the subform and it will search properly there.

When I move it to the main form and use the vbs script in my initial post it errors out stating"The expression On Click you entered as the event property setting produced the following error: A problem occured while Microsoft Access was communicating with the OLE server or ActiveX Control."

Attached to this response is the sample accdb with the "txtTerm" and "btnSearch" in the main form the way I had it initially and I was unable to get functioning.

 

[Uncle Gizmo]

Is "txtTerm" and "btnSearch" on the main form?

Yes or No will suffice ..

 

[jeffjohnson29]

...okay then yes it is...

 

[Uncle Gizmo]

Well, if "txtTerm" and "btnSearch" are on the main form, (that implies the button VBA code as well) then it's not the usual problem, so I'm out of ideas...

 

[Uncle Gizmo]

Not 100% sure, but I think Me.subSearch.Requery would be better...

 

[Uncle Gizmo]

Not 100% sure, but I think Me.subSearch.Requery would be better...

or it might be:- Me.subSearch.Refresh

 

[JHB]

..
When I move it to the main form and use the vbs script in my initial post it errors out stating"The expression On Click you entered as the event property setting produced the following error: A problem occured while Microsoft Access was communicating with the OLE server or ActiveX Control."
..

Your database is corrupt in a way, so next time create a new database and import all into it.
A new database with your forms, table and macro is attached.

 

[AccessBlaster]

jeffjohnson29: Just curious is this for a school district? The choice of field names looks familiar. I would almost expect to see PO's and Fixed Asset in your descriptions and maybe barcoding.

 

[mike60smart]

A little puzzled as to why you have Only 1 table and then using a

Main Form / Subform for data entry with BOTH Main Form & Subform based on the same table??

Please explain why?

 

[jeffjohnson29]

Your database is corrupt in a way, so next time create a new database and import all into it.
A new database with your forms, table and macro is attached.

Thank you! This seems to have been the problem. When I created a new Database and copied all the tables, fields, and Macros over the search worked correctly outside of the subform.