Bull,
I have 2 things for you:
1) Re. your last post - the problem with importing Access 2000 database object from one mdb to another is a known bug. The "Network Error" that occurrs is caused by importing queries, forms or modules and then closing the mdb without compiling it. However, once you get this error, all is not lost. If you get the error and you know which object(s) was imported without being compiled, simply delete that object and then compile the mdb (or adp). This should fix the problem. Once you have done this, you should then be able to re-import the object (and then compile again).
2) Regarding your original post - your best option in the ADP/SQl world is probably to use a SQL stored procedure that passes in variables as the Record Source of your report.
First, create some global variables in your ADP that are assigned values by the (button's) OnClick event in your form.
rough example 1:
Sub Button_OnClick()
gRptVar1 = Me.txtVal1
gRptVar2 = Me.cmbVal2
gRptVal3 = Me.chkVal3
DoCmd.OpenReport "rptName1"
End Sub
Now that you have assigned your variables, you will need to pass them into your stored procedure. This is a 2-part process in an ADP. First, you will need to create some functions that can be called to pass the variables into the report.
rough example 2:
Function GetRptVal1() as String
If ISNull(gRptVal1) = False Then
GetRptVal1 = gRptVal1
Else:
GetRptVal1 = "%"
End If
End Function
note: You will want to assign the SQL wildcard (%) as the default for any non-values.
Once you have created the functions to pull your report variables, you need to set up you report's Input Parameters. To do this, open your report in design view and open the report properties window. If you scroll all the way down to the last report property, you should see "Input Parameters". This line is where you will enter the variables that you are passing into your stored procedure. In SQL Server, variables are designated using the "@" symbol and you also have to designate the data type so, for the purpose of this example, the proper format for this property will be something like:
@RptVar1 nvarchar=GetRptVar1(), @RptVar2 nvarchar=GetRptVar2(), @RptVar3 int=GetRptVar3(), etc...
Or, if you want to skip using the functions altogether, you can pull your variable values directly from your form using:
@RptVar1 nvarchar=[Forms]![frmRptCriteria].txtVal1, @RptVar2 int=[Forms]...
Finally, you need to write your stored procedure in such a way that it will pull your variables in. Again, you will use the same naming convention and data types that you assigned in your report Input Parameters. So, your stored procedure should be written something like:
rough example3:
Alter Procedure spReport1
@RptVal1 NVARCHAR(15), @RptVal2 NVARCHAR(15), @RptVal3 INT
AS
SELECT * FROM tblReportData
WHERE [Val1]= @RptVal1 AND [Val2] = @RptVal2 AND [Val3] = @RptVal3
ORDER BY [RecordID]
This should be all you have to do. I know it seems like a lot at first but you will get used to it and you will also find that, if you learn to do all your data processing on the SQL side rather than on the Access side, your ADP applications will really cruise (especially if your processing large amounts of data).
I hope this has been of some help to you. If you have any additional questions, feel free to e-mail me at: morgan_n_coke@hotmail.com
Best of luck!
Nate
