Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
| 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 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 April 12th, 2009, 05:30 AM
Authorized User
 
Join Date: Apr 2004
Location: LEOMINSTER, Herefordshire, United Kingdom.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default Supplying query parameter in VBA

I have a form based upon "myQuery" which includes one parameter. I am attempting to supply that parameter in code within Form_Load by modifying the QueryDef prior to the calling Form.RecordSource = "myQuery". Debug shows that the parameter has been correctly set but I am still prompted to supply the parameter. What am I missing?
 
Old April 12th, 2009, 09:54 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

Probably the query doesn't work. Did you try it outside code??
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
 
Old April 14th, 2009, 03:14 PM
Friend of Wrox
Points: 4,007, Level: 26
Points: 4,007, Level: 26 Points: 4,007, Level: 26 Points: 4,007, Level: 26
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Lansing, Michigan, USA.
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG
Default

Remove the parameter from the query itself. Then when the form loads, ask the user via an input box what they want. Then set the form's record source via VBA code. View the query in SQL view to get the proper syntax for the VBA code, but replace the portion where you had the parameter with the proper input box answer.

e.g.

Code:
strCustomerID = Trim(Nz(InputBox("Enter the Customer ID.", "ID?"), ""))
 
If Len(strCustomerID) = 0 Then
    'Input box is empty.  Select all records.
    Me.RecordSource = "SELECT * FROM tblMyTable;"
Else
    'Input box is not empty.  Select specific records.
    Me.RecordSource = "SELECT * FROM tblMyTable " & _
        "WHERE [strCustomerID] = '" & strCustomerID & "';"
End If
I have not tested this.
__________________
Greg Serrano
Michigan Dept. of Environmental Quality
Air Quality Division
 
Old April 14th, 2009, 05:02 PM
Authorized User
 
Join Date: Apr 2004
Location: LEOMINSTER, Herefordshire, United Kingdom.
Posts: 13
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I got the solution from another forum, but thanks for your time. You may be interested in the answer.

This form is part of a working data entry sequence. Currently "param1" is known when the form is opened so I build the SQL in code with a WHERE clause to limit the recordset to "param1". Having read about modifying QueryDefs I thought that in future I could avoid all the coded SQL and base the form on a stored parameter query, and just supply "param1" by modifying the QueryDef as per my Wrox book. My question was: if the QueryDef reports that the parameter has been set, why is the parameter being requested? My code looked like this:

Private Sub Form_Load()
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryCourse")
Debug.Print "param1: " & qdf.Parameters("param1").Value -> "param1: "
qdf.Parameters("param1").Value = pLanguageID
Debug.Print "param1: " & qdf.Parameters("param1").Value -> "param1: 99"
qdf.Close
Set qdf = Nothing
Form.RecordSource = "qryCourse"
End sub

The error here was that the instance of "qryCourse" used as the RecordSource wasn't the same one that I had supplied parameters for. The solution is:

Private Sub Form_Load()
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryCourse")
qdf.Parameters("param1").Value = pLanguageID
Set Me.Recordset = qdf.OpenRecordset
qdf.Close
Set qdf = Nothing
End sub




Similar Threads
Thread Thread Starter Forum Replies Last Post
Parameter Query Help Normie Access 5 February 19th, 2008 01:50 PM
Covering yourself legally when supplying software BananaJim Classic ASP Professional 1 March 12th, 2007 10:11 AM
Interactively supplying inputs to SQL script vmaruv SQL Server 2000 4 February 4th, 2005 07:17 AM
Parameter Query Teqlump Access 4 November 11th, 2004 07:21 PM
Parameter Query Ben Access VBA 1 June 27th, 2003 12:13 PM





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