Set frm = Forms!DateRangePreviousCurrentYear ' Set database variable to current database. Private Sub Report_Open(Cancel As Integer) In report open, create a querydef, to which you will append your form parameters. Make the record source of the report TArrivalsByRegion.Ģ. Sorry about the delay in responding, here goes:ġ. The problem I am describingĪny help you could provide me would be greatly appreciated. Problem solved? I am trying to do this myself. You received the following message from: benmay14 ( )ĭid you ever get report using a pass through query with arguments Rst.Open strQueryName, CurrentProject.AccessConnection Or you could return the result set to an ADO recordset A query should open in datasheet view displaying Nancy In the immediate window, type the following and hitĢ. Test the Procedures, Resetting the Queries Parametersġ. Set cat.Procedures(strQName).Command = cmd "Jet OLEDB:ODBC Pass-Through Statement") = True Set cmd = cat.Procedures(strQName).Command Set cat.ActiveConnection = CurrentProject.Connection StrTSQL = "EXEC up_parmsel_Employees " & lngParamĬall BuildPassThrough(strQueryName, strTSQL) Public Sub ExecutePassThrough(lngParam As Long) Open a new module in Access and set a Reference toĪDOX (Microsoft ADO Ext. Write the Parameter Setting Module in Accessġ. The query and the proc it executes are now WHERE EmployeeID = Test that the proc was created successfully byĬlearing the Query Analyzer window and running theĪ single employee record for "Nancy Davolio" should beĭisplayed. SELECT EmployeeID, LastName, FirstName, Title Type the following SQL in the Query Analyzer, parse, Select the Northwind database form the Toolbar'sģ. With SQL Server running open the Query Analyzer.Ģ.
Running (the stored procedure doesn't exit yet).Ĭreate the Stored Procedure in SQL Server 2000ġ. Save the query as "qsptEmployees" and close without The ellipsis in the ODBC Connect Str propertyįield will open the DSN Data Source dialog if youĤ.
ODBC Driver= Server=(local) _ĭatabase=Northwind Trusted_Connection=YesĪll the other default properties are fine. Enter the following DSN-lessĬonnection string in the ODBC Connect Str property: Click the properties button on the Toolbar to open the Type "EXEC up_parmsel_Employees" as the query's SQLģ. In Query design view, from the main menu selectĢ. BuildPassThrough is then called which uses ADOX to reset the pass- through query's command text.ĭesign the Pass-Through Query in Access (.mdb)ġ. The ExecutePassThrough sub recieves an Employee ID number as an argument and concatenates it to the Pass-Through query's SQL.
#Ms access query parameter code#
The query returns a single employee record using EmployeeID as criteria.įirst the pass-through query get's built, then a stored procedure gets created on SQL Server which the pass-through query will execute, then some code gets written to re-build the pass-through query everytime a new employee record is requested. This pass-through query executes a parameterized stored procedure against the SQL Server version of the Northwind database using a DSN-less ODBC connection. Because of that I have code that sets the textbox on the left (above picture) to "Like *" so it theoretically returns all results, but that isn't working at the moment.Here's an approach I picked up from the "Microsoft Access Developer's Guide to SQL Server" by Mary Chipman and Andy Baron. This would work, but when a criteria is not selected then I need that query (which is specific to that filter) to return all results. If you are using this as input to a query - just put your Like expression (minus the quotes) into the criteria box for that field in the query? If I manually enter "Like Test" in the criteria field and run the query it displays the expected results. When the textbox contains the value "Like Test" and the query runs it doesn't display any results. The query references the texbox on the left (it's only being displayed for debugging purposes currently). In the dropdown box on the right a user selects a category then the box on the left is updated with what the criteria should be for the query (using code). That did not work either, but it may be because the way I'm doing it. Should be something along the lines of “Like “ & to force it to evaluate Kevinhughes2 wrote:Because your string is being interpreted as that string, without inserting the VALUE of the expression I attempted that but it gave the same error message of invalid syntax. DoCmd.SetParameter "Customer", me.Customer
0 kommentar(er)
