Showing the results in the same form

[mopat]

Really Need help!

I am still new to MS Access 07 and was wondering if someone can help me with this task.

I have a FORM 1-->linked to QUERY 1-->linked to Expertise:TABLE.

As of now, when I search for an item in the search box, the results are shown in a separate form. My goal is to have the results appear in the same search form.

The code I obtained to generate the search results was something I found online. I have no experience with coding and was wondering if someone can explain to me in the most simple terms how to complete this task. It would be greatly appreciated. Thanks!!

 

[JamesMcS]

Hi, welcome to the forum. It sounds like you've got some code attached to the search box. Can you post it? You'll probably see the problem when you look at it...

 

[mopat]

Thanks for the reply!

As of now, the search text box contains no code. It is only linked to the query. As for the Search button, that code is:



Private Sub Command2_Click()
On Error GoTo Err_Command2_Click

Dim stDocName As String

stDocName = "Query1"
DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub

Private Sub Expertise1_Click()

End Sub

Private Sub FirstName_Click()

End Sub


I am afraid that If i substitute this code with another code, it wont allow the results to appear at all.

Right now the search is based on "expertise" which are linked to employees names.


I have it so that if i search "people" then a new window pops up with the employee names who have anything to do with "people" such as "people search capabilities"

I need it so that when someone types in a search, all results with that text appears in the same form rather than a new window opening up.

Once again I really do appreciate your help. Ive been working on this thing for over 3 days now and its extremely frustrating

 

[JamesMcS]

What's happening there is you're running the query, and by default it will pop up in another window - just as if you'd double clicked on it in the database window.

What you need is to embed the query in a subform but you'll have to fiddle with the main form a little bit.

Create a subform based on query1 (always best to name them properly too, so you know what you're on about later). Make your master link criteria the name of the search text box and the child link criteria the relevant field in the query. As you type stuff in the text box and press enter, the query in the subform will be filtered accordingly.

 

[mopat]

Thanks James, It seems to be working.

If there are two categories in the query, "people search capabilities" & "technical People" and the user enters half of the phrase, "people", Is there anyway that both of the categories appear in the subform?

As of now when i search "people" both categories show up in the 2nd window but nothing appears in the subform.

 

[JamesMcS]

You could try *people* in the text box but I'm not 100% if that'll work. If it doesn't we'll need to make the search button change the record source of the form, quite easy, but try the * way first (more flexible if it does work)

 

[mopat]

Hey James,

So I tried the * way and it didnt seem to work. I guess it would be easier if you saw exactly what im trying to do which is why I attached the file below.

My main goal is:

If there are two categories in a query, "people search capabilities" & "technical People" and the user enters half of the phrase into the search box, "people", Is there anyway that both of the categories appear in the subform?

As of now when i search "people" both categories show up in the Query1 window but nothing appears in the subformQuery1.


Really do appreciate your time and help. Thanks again

 

[vbaInet]

Please avoid double posting in this forum. Follow-up your original thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=194966&page=2

 

[mopat]

Sorry I wasnt aware!

 

[JamesMcS]

Yeah that was a poor hangover-induced guess... vba has it right in your other post.

My plan was to remove link criteria from the subform as per vba, but rather than having the query in the subform, make it a form whose recordsource is the same SQL statement as query1. Then, you can use the contents of the text box as the WHERE criteria in your SQL statement, requery, and presto! Records displayed as you want.