By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,183 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Access Reports from a SQL BackEnd

P: 8
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=rs.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.
Jan 4 '07 #1
Share this Question
Share on Google+
8 Replies


NeoPa
Expert Mod 15k+
P: 31,308
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.
Jan 4 '07 #2

P: 8
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.
Jan 4 '07 #3

NeoPa
Expert Mod 15k+
P: 31,308
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.
Jan 4 '07 #4

P: 8
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!!
Jan 4 '07 #5

NeoPa
Expert Mod 15k+
P: 31,308
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

Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 4 '07 #6

P: 8
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.


Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2.  
  3. ' Retrieve family details by entering FamilyID
  4.  
  5. Dim Rs As ADODB.Recordset ' Recordset produces a virtual table from SELECT query
  6.  
  7. Dim FolderName, PicName As String
  8. Dim strFamilyName, strAddress, strPostCode As String
  9.  
  10. Forms!frmWfsFamilyUpdate!txtFosterID.SetFocus
  11.  
  12. strFamilyID = Forms!frmWfsFamilyUpdate!txtFosterID.text
  13.  
  14. ' Open a SQL Connection, to use a SELECT stored procedure.
  15.  
  16. sqlConnect
  17.  
  18. Set cmd = New ADODB.Command
  19. cmd.ActiveConnection = Con
  20. cmd.CommandType = adCmdStoredProc
  21. cmd.CommandText = "selectFamilyDetails"
  22.  
  23. ' CarerID variable inputted into
  24. cmd.Parameters.Append cmd.CreateParameter("carerID", adVarChar, adParamInput, 8, strFamilyID)
  25.  
  26. ' Run Stored Procedure
  27. Set Rs = cmd.Execute
  28.  
  29. ' If Recordset is not at end of file. Assign Report Controls to RS fieldNames
  30.  
  31. If Not Rs.EOF Then
  32.     strFamilyName = Rs.Fields(2)
  33.     strAddress = Rs.Fields(3)
  34.     strAddress = strAddress & "," & Rs.Fields(4)
  35.     strAddress = strAddress & "," & Rs.Fields(5)
  36.     strPostCode = Rs.Fields(6)
  37.     strPostCode = strPostCode & " " & Rs.Fields(7)
  38.     strPhoneNo = Rs.Fields(8)
  39.  
  40. End If
  41.  
  42. strFamilyName = txtFamilyName
  43. strAddress = txtAddress
  44. strPostCode=txtPostCode
  45. strPhoneNo=txtPhoneNo
  46.  
  47.  
  48. End Sub
Jan 4 '07 #7

MMcCarthy
Expert Mod 10K+
P: 14,534
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
You're using the Report open event when you should be using the report format event.

Mary
Jan 5 '07 #8

NeoPa
Expert Mod 15k+
P: 31,308
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

Expand|Select|Wrap|Line Numbers
  1. 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.
Jan 5 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.