Report Problem

[tpoettker]

Hi,

I am new to Access and have been asked to create a report and, using data from a SQL query and some logic, post the data into the report.

I have created the report, connected to SQL server using ADODB connection and executed the stored procedure. This part works fine.

It's the next part, I can't seem to get past. Nothing I have tried works. I want to take the value a field from the query and place the value in a field on the report.

I have tried
DoCmd.OpenReport, ReportName, acViewDesign.

This returns an error message that says "This action can't be carried out while processing a form or report event.@A macro specified as the OnOpen, OnClose, OnFormat, OnRetreat, OnPage, or OnPrint property setting contains an invalid action for the property.@When you click OK, an Action Failed"

I have changed acViewDesign to acViewPreview only to receive a different error.

If I don't use the openreport command, then I receive an error that I am trying to add a value to a read-only form.

Any ideas?

Thanks
Tina

 

[lewds]

Hi...
As far as I know,someone may correct, Reports a end result.

I think you said..
Sql server - Query - report
Now you want to modify some field on the report?

Try
Sql server - Query - NewQuery - report

Use the NewQuery to Modify or Add Fields then base your report on the NewQuery

 

[tpoettker]

Here's my full code. Let me know what your think.

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Run_Duplicate_Event_Report_Click

Dim stDocName As String
Dim cnn As New ADODB.Connection
Dim cmd1 As New ADODB.Command
Dim Adors As ADODB.Recordset
Dim sqltext As String
Dim strConsumerName As String
Dim ReportName As String

sqltext = InputBox("Enter event number")


Set Adors = New ADODB.Recordset
' Open a connection using an ODBC DSN.
cnn.ConnectionString = "Provider=SQLOLEDB; Data Source=bjcsql04; Initial Catalog=RiskMaster;User Id=rmsysadm; Password=riskm1"
cnn.Open

cmd1.ActiveConnection = cnn
cmd1.CommandType = adCmdStoredProc
cmd1.CommandText = "BehaviorHealthRpt"
cmd1.Parameters(1) = sqltext
cmd1.CommandTimeout = 90
Set Adors = cmd1.Execute

ReportName = "rpt_BehaviorHealth"
DoCmd.OpenReport ReportName, acViewPreview
Reports(ReportName).Visible = False

With Reports(ReportName)
Me.TxtConsumerName = AdoRS(7)
End With

Exit_Run_Duplicate_Event_Report_Click:
Exit Sub

Err_Run_Duplicate_Event_Report_Click:
MsgBox Err.Description
Resume Exit_Run_Duplicate_Event_Report_Click
End Sub

 

[lewds]

Hi..

Tell me if I'm getting this right?

You are....

Connecting to an SQL server
inputing a parameter
running a stored procedure,which returns a report

if the above is true the you should..

connect to the server
link to the tables that contain the info you want
devise your own query to collect the info you want
devise your own report based on your query