Opening a subform by a search function?

[JvM]

Good afternoon,

I have a piece of code wich searches my table with articles. It opens a query with results.

I want it to open a subform in my headform with the search results. Can anybody help me?

This is the code....

Private Sub Zoeken_Click()
' exit if nothing is entered on the form.
If IsNull(Me.txtKeywords) Then Exit Sub

' declare variables.


Dim Keywords As String
Dim fieldToSearch As String
Dim Criteria As String
Dim SQL As String

' remove leading and trailing spaces, if any, from user input.
Keywords = Trim(Me.txtKeywords)

' specify which field to search.
fieldToSearch = "[Artikelnummer] & [Artikel_omschrijving]"

' create criteria based on input.
Do While InStr(Keywords, " ") > 0
Criteria = Criteria & fieldToSearch & " Like ""*" & Left(Keywords, InStr(Keywords, " ") - 1) & "*"" or "
Keywords = Mid(Keywords, InStr(Keywords, " ") + 1)
Loop
Criteria = Criteria & fieldToSearch & " Like ""*" & Keywords & "*"""

' build SQL statement.
SQL = "SELECT Artikelnummer, Artikel_omschrijving" & _
" FROM Voorraad" & _
" WHERE " & Criteria

Set db = CurrentDb

' delete query qryTempSearch if already exists.
On Error Resume Next
db.QueryDefs.Delete "qryTempSearch"
On Error GoTo 0

' create query gryTempSearch.
Set querydef = db.CreateQueryDef("qryTempSearch", SQL)

' if there are records to be returned, run the query,
' otherwise display a message.
If DCount("*", "qryTempSearch") > 0 Then
DoCmd.OpenQuery "qryTempSearch"
Else
MsgBox "Geen records aanwezig met dergelijke zoek criteria"
End If

Set db = Nothing
End Sub


Thank you very mucha....

 

[beef]

Please find attached a sample database that may be what you are looking for.

Have fun

Cheers!!!!

Lou

 

[JvM]

Thanks a lot for your sample dbase..
Great piece of work.. A little to difficult for me yet.

But I'm advancing..

Again thank you very much for your help.