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

Set the forms WhereCondition argument when you open it like:

DoCmd.OpenForm "Form2", acNormal, , strWhere

Your strWhere variable will work fine with one modification. The WhereCondition argument takes a SQL Where clause without the word 'Where'. So earlier in your code change:

strWhere = "WHERE"

to

strWhere = ""

HTH,

Bob

Bob,

When I changed the strWhere = ""
I get a Syntax error in From Clause.

This ran fine for me, with your QueryDef settings commented out. Use Debug.Print strSQL to see where a glitch might be in your SQL string. Probably missing a space or something somewhere.

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 = ""

    strOrder = "ORDER BY tbl_Vendor.L_name;"

    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")
'
' Debug.Print strSQL & " " & strWhere & "" & strOrder
' qryDef.SQL = strSQL & " " & strWhere & "" & strOrder

     DoCmd.OpenForm "Form2", acNormal, , strWhere
' DoCmd.OpenQuery "qry_SearchV", acNormal

' qryDef.Close


Exit_SearchButton_Click:
        Exit Sub

End Sub

Bob

I'm receiving another error. I'm getting error 91. Do you have Developer Kit SR-1 installed?

Error 91 is a VBA error, not a Jet SQL error. Do you have a second form designed with the 4 appropriately named textboxes, and a form name that you are using as the name argument of the DoCmd.OpenForm method, like:

DoCmd.OpenForm "YourFormName", acNormal, , strWhere

is your code breaking on this line? This is generic stuff and will run on any version of Access.

Bob