Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 5th, 2004, 03:43 PM
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
  #2 (permalink)  
Old August 5th, 2004, 05:44 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
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


Reply With Quote
  #3 (permalink)  
Old August 6th, 2004, 02:54 PM
Registered User
 
Join Date: Aug 2004
Location: , , .
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.



Reply With Quote
  #4 (permalink)  
Old August 6th, 2004, 03:34 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
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



Reply With Quote
  #5 (permalink)  
Old August 6th, 2004, 04:33 PM
Registered User
 
Join Date: Aug 2004
Location: , , .
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?

Reply With Quote
  #6 (permalink)  
Old August 6th, 2004, 05:01 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
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



Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 10:38 PM.


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