View Single Post
  #1 (permalink)  
Old August 5th, 2004, 03:43 PM
gpboy gpboy is offline
Registered User
 
Join Date: Aug 2004
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Sending Search results to a Form

I have a search criteria form and would like to the Searched information to be loaded into a form instead of a queried Datasheet view. I will use the form as a subform in my Main Vendor Table.
I have changed the DoCmd.Openform "frm_Search", acNormal and still no luck.
Thanks.

Here are the codes.

Option Compare Database

Private Sub SearchButton_Click()

Dim strSQL As String, strOrder As String, strWhere As String

Dim db As DAO.Database
Dim qryDef As DAO.QueryDef
Set db = CurrentDb()

strSQL = "SELECT tbl_Vendor.F_Name, tbl_Vendor.L_Name, tbl_Vendor.Company, tbl_Vendor.ST " & _
"FROM tbl_Vendor"

strWhere = "WHERE"

strOrder = "ORDER BY tbl_Vendor.L_name;"

' set where clause conditions

If Not IsNull(Me.F_name) Then
strWhere = strWhere & " (tbl_Vendor.F_name) Like '*" & Me.F_name & "*' AND"
End If

If Not IsNull(Me.L_name) Then
strWhere = strWhere & " (tbl_Vendor.L_Name) Like '*" & Me.L_name & "*' AND"
End If

If Not IsNull(Me.Company) Then
strWhere = strWhere & " (tbl_Vendor.Company) Like '*" & Me.Company & "*' AND"
End If

If Not IsNull(Me.ST) Then
strWhere = strWhere & " (tbl_Vendor.ST) Like '*" & Me.ST & "*' AND"
End If

strWhere = Mid(strWhere, 1, Len(strWhere) - 5) ' remove ' and'

Set qryDef = db.QueryDefs("qry_SearchV")
   qryDef.SQL = strSQL & " " & strWhere & "" & strOrder

    DoCmd.OpenQuery "qry_SearchV", acNormal

  qryDef.Close


Exit_SearchButton_Click:
    Exit Sub

End Sub


Reply With Quote