Access Report from Stored procedure SQL Server or from Access RS

[Avacha]

I have to create Report, witch will be fill out from RS dynamically But I can not assign Data to Text box on Report. My data will came from stored procedure SQL Server,
or from Recordset Access
This is code:
If [Forms]![FrmMenuReport]![FrmReport] = 1 Then
Me.LblReportName.Caption = "General Information"
Me.LblReportSort.Caption = "Sorted By Facility"
Me.lblReportPageHeaderSort.Caption = "SORTED BY FACILITY IN ASCENDING ORDER"
Me.LblFirst.Caption = "Report Date"
Me.Labelsec.Caption = "Last name"
Me.Labelt.Caption = "First name"
Me.LabelFour.Caption = "Book&Case Number"
Me.Labelsix.Caption = "FACILITY"
If RS.EOF And RS.BOF Then
MsgBox "No match for this selection.", vbOKOnly + vbInformation
Exit Sub
Else
Do Until RS.EOF
txtData.ControlSource = RS.Fields"Facility&quot
STRText = STRText & RS.Fields("Facility&quot
RS.MoveNext
Loop
txtData = strdate

ElseIf [Forms]![FrmMenuReport]![FrmReport] = 2 Then
Me.LblReportName.Caption = "Gender"
Me.LblReportSort.Caption = "Sorted By Gender"
Me.lblReportPageHeaderSort.Caption = "SORTED BY GENDER IN ASCENDING ORDER"
Me.LblFirst.Caption = "FACILITY"
Me.Labelsec.Caption = "Last name"
Me.Labelt.Caption = "First name"
Me.LabelFour.Caption = "Book&Case Number"
Me.Labelfif.Caption = "GENDER"
Me.Labelsix.Visible = False
If RS.EOF And RS.BOF Then
MsgBox "No match for this selection.", vbOKOnly + vbInformation
Exit Sub
Else
RS.MoveFirst
RS.MoveLast
'txtData.RowSource = RS.RecordCount - 1
'txtData.Height = RS.RecordCount - 1 * 3
'txtData.with = Len(RS.Fields("FACILITY&quot)
Do Until RS.EOF
'txtData.Caption = txtData.Caption & RS.Fields("FACILITY&quot & vbCrLf
'txtData.ControlSource = "tyttyuqwt" 'RS.Fields("FACILITY&quot
'txtData.Caption = "dgsfjsdgfjsd"
'STRText = STRText & RS.Fields("FACILITY&quot & vbCrLf
RS.MoveNext

Loop
'txtData.Caption = STRText
End If
End If
I tried:

txtData=txtData & RS.Fields"Facility&quot& vbcrlf
txtdata=txtData & RS.Fields("Facility&quot
txtData.value =RS.Fields("Facility&quot

Every time I get msg "You can't assign a value to this object"

 

[jimmythegeek]

Their have been 2 ways I have handled reports in Access from a SQL Server stored procedure:

1) In code, create dynamic SQL that in turn creates a Pass-through query that is the Record Source of the report. (The only downfall to this, is that a ptq requires a DSN, but I create the DSN on the user's machine in the AutoExec macro, so it is seamless. This is the way that works best for me)

2) In code, create dynamic SQL that in turn creates a temporary table that is the Record Source of the report. (Has not always worked as well as option 1).

Both of these have worked, I have never had any success using a recordset as the Record Source of a report. If you find out how, let me know.

If you are interested in the code for the PTQ, let me know. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?