Access Reports from a SQL BackEnd

[feets]

OK, I'll tell you what i've got so far. On opening an Access Report I am running a Stored Procedure (a Select Query). Now my Recordset fields have values, but I cannot associate them to the controls I have placed on my Report. What am I doing wrong?? For example

txtFamilyName=r s.Fields(2)

Produces a runtime error 438. Object doesn't support this property or Method. I've tried assigning a String Variable to the field 1st, and then assign the variable to the control, but I get the same error.

Please Help!!! Getting pretty desperate.

[NeoPa]

You are sort of doing it back-to-front.
You need to define and test a recordset (table/query linked query) that produces the fields you expect (If linked to stored procedure check the datatypes of the returned values match what you require).
Next you need to bind the report to this recordset (Not a RecordSet in the code but an Object that returns a recordset (table/query as specified earlier). Put it's name in the reports Record Source property.
Now, the controls on the report that can take field values, can themselves be bound, by entering the name of the relevant field in the controls 'Control Source' property.
HTH.

[feets]

You are sort of doing it back-to-front.
You need to define and test a recordset (table/query linked query) that produces the fields you expect (If linked to stored procedure check the datatypes of the returned values match what you require).
Next you need to bind the report to this recordset (Not a RecordSet in the code but an Object that returns a recordset (table/query as specified earlier). Put it's name in the reports Record Source property.
Now, the controls on the report that can take field values, can themselves be bound, by entering the name of the relevant field in the controls 'Control Source' property.
HTH.

Thanks for getting back to me. I have to admit I'm a bit of a novice to vb, so I'm a bit confused how to bind the report to the recordset. I don't know if you can enlighten me any further.

Hopefully thanks in advance.

[NeoPa]

1. Open the report in Design View.
2. Open the Properties Window (Alt-Enter).
3. Enter the name of the QueryDef (or the SQL string instead if you prefer) into the Record Source property.
Your report is now bound to the dataset specified and you can bind individual controls to the fields in the dataset.

[feets]

I think I need to explain things better. First of all thanks for all your advice so far.
I am connecting to a SQL Backend from an access Front End.mdb file. Now from my reading, binding a report to a recordset isn't available unless it is an Adp file.

I've created an Adp file, imported all my forms and reports from my MDB frontend, and like you say I can statically set the RecordSource to the stored procedure.

However, I want to set the RecordSource through Code

Set Me.RecordSource = Rs

yet it still says that it is an invalid use of property

I must be getting on your nerves but I could really use your advice!!

[NeoPa]

I am connecting to a SQL Backend from an access Front End.mdb file. Now from my reading, binding a report to a recordset isn't available unless it is an Adp file.

I don't know what you're reading - I do it all the time and I only ever use .MDBs. Is there any qualification there that might make it all make sense?

I've created an Adp file, imported all my forms and reports from my MDB frontend, and like you say I can statically set the RecordSource to the stored procedure.

However, I want to set the RecordSource through Code

Set Me.RecordSource = Rs

yet it still says that it is an invalid use of property

I don't know about ADPs but in Access (MDB) you would put that code (assuming RS has already been set to a valid, and matching, recordset) in the form's OnOpen event.

[feets]

I don't know what you're reading - I do it all the time and I only ever use .MDBs. Is there any qualification there that might make it all make sense?

I don't know about ADPs but in Access (MDB) you would put that code (assuming RS has already been set to a valid, and matching, recordset) in the form's OnOpen event.

This is an extract from the Microsoft White Paper

USING ADO RECORDSETS WITH MICROSOFT ACCESS REPORTS
In Microsoft Access 2002, it is now possible to use ADO recordsets with reports in Microsoft Access project files. Unfortunately, the use of the report Recordset property is limited to project files. If you try to set or retrieve a report's Recordset property in a Jet database (.mdb) file, you receive the following error message:

Feature is not available in an MDB

I've attached my code used when opening the report - since I thought that it would make more sense.

Private Sub Report_Open(Cancel As Integer)

' Retrieve family details by entering FamilyID

Dim Rs As ADODB.Recordset ' Recordset produces a virtual table from SELECT query

Dim FolderName, PicName As String
Dim strFamilyName, strAddress, strPostCode As String

Forms!frmWfsFamilyUpdate!txtFosterID.SetFocus

strFamilyID = Forms!frmWfsFamilyUpdate!txtFosterID.text

' Open a SQL Connection, to use a SELECT stored procedure.

sqlConnect

Set cmd = New ADODB.Command
cmd.ActiveConnection = Con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "selectFamilyDetails"

' CarerID variable inputted into
cmd.Parameters.Append cmd.CreateParameter("carerID", adVarChar, adParamInput, 8, strFamilyID)

' Run Stored Procedure
Set Rs = cmd.Execute

' If Recordset is not at end of file. Assign Report Controls to RS fieldNames

If Not Rs.EOF Then
    strFamilyName = Rs.Fields(2)
    strAddress = Rs.Fields(3)
    strAddress = strAddress & "," & Rs.Fields(4)
    strAddress = strAddress & "," & Rs.Fields(5)
    strPostCode = Rs.Fields(6)
    strPostCode = strPostCode & " " & Rs.Fields(7)
    strPhoneNo = Rs.Fields(8)
  
End If

strFamilyName = txtFamilyName
strAddress = txtAddress
strPostCode=txtPostCode
strPhoneNo=txtPhoneNo


End Sub

[MMcCarthy]

This is an extract from the Microsoft White Paper

USING ADO RECORDSETS WITH MICROSOFT ACCESS REPORTS
In Microsoft Access 2002, it is now possible to use ADO recordsets with reports in Microsoft Access project files. Unfortunately, the use of the report Recordset property is limited to project files. If you try to set or retrieve a report's Recordset property in a Jet database (.mdb) file, you receive the following error message:

Feature is not available in an MDB

I've attached my code used when opening the report - since I thought that it would make more sense.


Private Sub Report_Open(Can cel As Integer)

' Retrieve family details by entering FamilyID

Dim Rs As ADODB.Recordset ' Recordset produces a virtual table from SELECT query

Dim FolderName, PicName As String
Dim strFamilyName, strAddress, strPostCode As String

Forms!frmWfsFam ilyUpdate!txtFo sterID.SetFocus

strFamilyID = Forms!frmWfsFam ilyUpdate!txtFo sterID.text

' Open a SQL Connection, to use a SELECT stored procedure.

sqlConnect

Set cmd = New ADODB.Command
cmd.ActiveConne ction = Con
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "selectFamilyDe tails"

' CarerID variable inputted into
cmd.Parameters. Append cmd.CreateParam eter("carerID", adVarChar, adParamInput, 8, strFamilyID)

' Run Stored Procedure
Set Rs = cmd.Execute

' If Recordset is not at end of file. Assign Report Controls to RS fieldNames

If Not Rs.EOF Then
strFamilyName = Rs.Fields(2)
strAddress = Rs.Fields(3)
strAddress = strAddress & "," & Rs.Fields(4)
strAddress = strAddress & "," & Rs.Fields(5)
strPostCode = Rs.Fields(6)
strPostCode = strPostCode & " " & Rs.Fields(7)
strPhoneNo = Rs.Fields(8)

End If

strFamilyName = txtFamilyName
strAddress = txtAddress
strPostCode=txt PostCode
strPhoneNo=txtP honeNo


End Sub

You're using the Report open event when you should be using the report format event.

Mary

[NeoPa]

I am connecting to a SQL Backend from an access Front End.mdb file. Now from my reading, binding a report to a recordset isn't available unless it is an Adp file.

I don't know what you're reading - I do it all the time and I only ever use .MDBs. Is there any qualification there that might make it all make sense?

I've created an Adp file, imported all my forms and reports from my MDB frontend, and like you say I can statically set the RecordSource to the stored procedure.

However, I want to set the RecordSource through Code

Set Me.RecordSource = Rs

yet it still says that it is an invalid use of property

I don't know about ADPs but in Access (MDB) you would put that code (assuming RS has already been set to a valid, and matching, recordset) in the form's OnOpen event.

I don't know what I was thinking to miss a gaping problem here.
You want to assign a value (String) to Me.RecordSource (NOT Me.RecordSet) in the Open event of the report. You cannot assign RS to this as RS is not a string.