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....
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....