ADP PARAMS etc

[John Kraus]

Having found out that the Live Reports feature of A2007 doesn't work with an
ADP with SQL Server Express I used a different SP to do the job.

What I need to figure out is how to make the drop down entry box the user
sees be populated with the names of the customers so the user can easily
select the customer whose data will populate the report. I can enter the
customer name manually and all works fine but it is not very elegant. I need
some idea of which is the most appropriate way to do this among those that I
have seen here and elesewhere. Admittedly I don't fully understand all of
this in the way I would like but suspect I can muddle my way through with
some guidance. Basically, I need to figure out what to do with this WHERE
statement and whatever else may have to be done with input params to make
the input window drop down present the customer name choices to the user.
And while I'm asking, is there a document someplace that explains the pieces
of this in laymens terms, describing what each contributes to the whole?

WHERE (dbo.CustomerTable.CustName = @EnterCustomerName)

Thanks for your help and especially for your precious time.

John K.

 

[Sylvain Lafontaine]

First, the Office 2007 SP1 should be available next tuesday:

http://blogs.msdn.com:80/usisvde/archive/2007/12/05/office-2007-sp1-download-available-dec-11.aspx

Maybe the Live Reports feature of ADP2007 will be corrected in this new
version. However, please do note that I did not preview this SP in any way
and that I know absolutely nothing about what's in there, including anything
about the possibility of Live Reports for ADP or for anything else about ADP
and that there is nothing else to read behind these lines that I'm writing
excerpt my total lack of knowledge about this SP1.

Second, excerpt for the fact that they can use SP as the rows source for the
comboboxes and listboxes and as records source for the forms, subforms,
reports, subreports, etc.; there is nothing different between an ADP project
and a regular Access database with a MDB file or the new format ACCDB. Any
book that will show you how to do this with a regular mdb or accdb file can
be used to learn to do the same exact same thing with ADP. If you want to
learn about how to code an ADP, you should learn first how to do this with a
MDB file and excerpt for some little differences here and there, the exact
same method will be usable for ADP.

If you don't know how to filter a report in ADP 2007 without the Live
Reports feature, it's because you don't know how to do this with a regular
MDB or ACCDB file and there lots and lots of books out there that will show
you how to do this.

The only real difference between ADP and MDB will be that instead of
creating and using a QueryDef as the source, you will have essentially three
methods of creating a row source in ADP:

1- build a query string with a Select statement: "Select * from MyTable
Where Id=4 and ...."

2- build a query string with an EXEC statement to call a stored procedure
with parameters:

sql = "EXEC MySP parm1, parm2, ..."

or 3- use the name of a stored procedure as the row source and use the
InputParameters to pass it the parameters. This method is the harder to
understand but it has been explained many times in this newsgroup.

In the case of a report, the methods 1 and 2 require that the string is
built in the OnOpen event of the report. For the method 3, you don't have
to do this is the parameters in the InputParameters refers to control on a
form but if you want to refer to variables on the report (in the case that
you would want this report to be called from different forms), use a Public
variable and set its value in the OnOpen event of the report:

Public IdRoute As Integer

Private Sub Report_Open(Cancel As Integer)

If (IsNull(Me.OpenArgs)) Then
MsgBox "This report cannot be opened directly."
Cancel = True
Exit Sub
End If

' IdRoute is the name of a control on an external form:
IdRoute = Forms(Me.OpenArgs)!IdRoute

End Sub


and for the InputParameters property:

@IdRoute int = Reports!NameOfYourReport.IdRoute


and for calling this report from a form:

On Error Resume Next
DoCmd.OpenReport "NameOfYourReport", acViewPreview, , , , Me.name
On Error GoTo 0