Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 5th, 2004, 03:43 PM
Registered User
 
Join Date: Aug 2004
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


 
Old August 5th, 2004, 05:44 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default


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


 
Old August 6th, 2004, 02:54 PM
Registered User
 
Join Date: Aug 2004
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Bob,

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



 
Old August 6th, 2004, 03:34 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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



 
Old August 6th, 2004, 04:33 PM
Registered User
 
Join Date: Aug 2004
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old August 6th, 2004, 05:01 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

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








Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I display search results... aspiretech9 PHP How-To 1 April 26th, 2005 10:12 AM
How do I display search results... aspiretech9 Beginning PHP 1 April 25th, 2005 04:59 PM
How do I display search results... aspiretech9 PHP Databases 1 April 25th, 2005 12:11 PM
adding counts to search results gilgalbiblewheel Classic ASP Databases 0 August 6th, 2004 11:35 AM
Customize Search Results bmains ASP.NET 1.0 and 1.1 Professional 4 January 15th, 2004 08:51 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.