[AccessD] ADP general question ...

Actually, that SPROC example probably should be shown with integers instead:

CREATE PROCEDURE YourStoredProcedureNameHere (@YourInputParameter AS
INTEGER)
SELECT ViewField1,
	 ViewField2,
	 ViewField3
FROM
	YourViewNameHere
WHERE Something = @YourInputParameterHere

maybe that helps a bit more?

David McAfee

-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of David McAFee
(Home)
Sent: Thursday, January 30, 2003 1:08 PM
To: accessd at databaseadvisors.com
Subject: RE: [AccessD] ADP general question ...


Its not too bad, and it all depends on what you want to do with that
returned recordset.
If you want to assign it to a listbox or combobox, in VBA it is called with
one line:

Me!MyListbox.RowSource = "EXEC YourSprocNameHere '" & YourParameter & "'"

then your SPROC (Stored Procedure) can be something like:

CREATE PROCEDURE stp_000_DealerSold (@MachID AS UNIQUEIDENTIFIER)
SELECT InvoicedTo,
	  CompanyName,
	  [Description]
FROM
	dbo.VW_000_DealerSold
WHERE MachineID = @MachID

If you want to populate fields on a form, then call it from VBA like this:

 Dim rs As ADODB.Recordset
 Set rs = New ADODB.Recordset
 'Call the stored procedure, passing it the parameter, returning recordset
rs
 CurrentProject.Connection.YourSprocNameHere YourParameter, rs
 'Fill in the fields from the returned recordset
 Me!txtField1 = rs![RecordColumn1]
 Me!txtField2 = rs![RecordColumn2]
 Me!txtField3 = rs![RecordColumn3]
 rs.Close
 Set rs = Nothing


HTH
David McAfee


-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of Tom Adams
Sent: Thursday, January 30, 2003 12:07 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] ADP general question ...


I routinely create one monster detail query (now view) with all the fields
in it and all the preassigned where conditions and then
base a set or related reports on this view.  I also have between 10 and 20
optional criteria the user can select - some single value
some multi-value.  I then base varying levels of summary, rollup and
crosstab reports on this single "Base" query.

I want a general method to pass the criteria to the "Base query" when I run
a report based on a view that is solely based in this
base query but may not necessarily need the criteria fields (eg. views with
group by and sum functions.

This is going against Sql 7 from an Access 2000 adp.

Based on my understanding now, I would have to put all this into a stored
procedure, have all the parameters in it, and with the
Base table being a User Session Table (eg. #Name) being generated first
based on the parameters and following TSql code referring to
it.  As this returns a recordset I assume I'd have to make the final sql
code return a table.

Any thoughts or hints on problems or ways to do this would be greatly
appreciated.

Tom

ps.  I'm using a product called DynamiCube from Data Dynamics and I've
fallen in love with it.
      I've programmed a general data cube form and all the rest is data
driven.  It allows total
      end user futzing around and then they can export to excel or print a
report.

      If you're in the market for this kind of power and flexibility I'd
highly recommend taking
      a look at this product - it's about $500 per developer - no license or
runtime fees.  It
      works with VB and supposedly Access though I've not had a chance to
test it in Access
      yet.




_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com