access reports from 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

strFamilyName=rs.Fields(2)


Here is the code when the Report opens, any help would be much appreciated


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

 

[pbaldy]

Where is it failing, since this looks backwards:

strFamilyName = txtFamilyName

 

[feets]

Don't know why I typed it liked that! I did have them the other way. Anyway the variable takes the value from recordset field but then when I try to use the variable with the txtboxCtrl it produces an error saying it can't associate

 

[feets]

Don't know why I typed it liked that! I did have them the other way. Anyway the variable takes the value from recordset field but then when I try to use the variable with the txtboxCtrl it produces an error saying it can't associate

Runtime error 438 Object doesnt support Property or Method

 

[pbaldy]

To be honest, I've never tried to set all of the controls like that. Are they dynamic (where the control would have a different source each time)? When I'm basing a report on a stored procedure, I create a pass-through query and set my report's source to that. Then in design view, I can bind each of the controls to the appropriate field, and avoid all the code in the report. Is that a direction you can go in? If not, one thought I had was that you may not be able to set the value of a control in the open event. I typically use the format event of the section containing the control to set its value.

 

[feets]

Thanks for your help so far. Ok I've setup the Pass through query running my stored procedure, how do get the requested parameters from my form controls??

 

[allan57]

Hi Feets, below is some examples of how I use pass through queries:

Public Function TestSqlPassThrough_SELECT()

Dim dbs As DAO.Database
Dim qd As QueryDef
Dim rstGetData As Recordset
Dim strSQL As String

On Error Resume Next

'DELETE EXISTING QUERY SO THAT IT CAN BE REBUILT USING UPDATED PARAMETERS
DoCmd.DeleteObject acQuery, "1_Pass_Through_Queries_with_Parameters_SELECT"

'For the example I have hard coded the criteria but you can use text boxs etc
strSQL = "SELECT AS400_BPCS_IIM.IPROD, AS400_BPCS_IIM.IDESC, AS400_BPCS_IIM.ICLAS " & _
"FROM AS400_BPCS_IIM " & _
"WHERE AS400_BPCS_IIM.ICLAS='BF';"

Set dbs = CurrentDb()
Set qd = dbs.CreateQueryDef("1_Pass_Through_Queries_with_Parameters_SELECT", strSQL)

qd.SQL = strSQL
qd.Connect = "ODBC;DSN=sensdata;UID=TAYLORAL;DATABASE=SensData;Network=DBMSSOCN;Trusted_Connection=Yes;Regional=Yes"
qd.ODBCTimeout = 0
qd.ReturnsRecords = True

Set rstGetData = qd.OpenRecordset()

rstGetData.MoveLast
rstGetData.MoveFirst

MsgBox rstGetData.RecordCount

dbs.Close
qd.Close
rstGetData.Close

Set dbs = Nothing
Set qd = Nothing
Set rstGetData = Nothing

End Function

Public Function TestSqlPassThrough_UPDATE()

Dim dbs As DAO.Database
Dim qd As QueryDef
Dim strSQL As String

On Error Resume Next

'DELETE EXISTING QUERY SO THAT IT CAN BE REBUILT USING UPDATED PARAMETERS
DoCmd.DeleteObject acQuery, "1_Pass_Through_Queries_with_Parameters_UPDATE"

'For the example I have hard coded the criteria but you can use text boxs etc
strSQL = "UPDATE AS400_BPCS_IIM SET AS400_BPCS_IIM.ICLAS = 'ZZ' WHERE AS400_BPCS_IIM.ICLAS='6A';"

Set dbs = CurrentDb()
Set qd = dbs.CreateQueryDef("1_Pass_Through_Queries_with_Parameters_UPDATE", strSQL)

qd.SQL = strSQL
qd.Connect = "ODBC;DSN=sensdata;UID=TAYLORAL;DATABASE=SensData;Network=DBMSSOCN;Trusted_Connection=Yes;Regional=Yes"
qd.ODBCTimeout = 0
qd.ReturnsRecords = True

dbs.Close
qd.Close

Set dbs = Nothing
Set qd = Nothing

End Function

Public Function TestSqlPassThrough_DELETE()

Dim dbs As DAO.Database
Dim qd As QueryDef

Dim strSQL As String

On Error Resume Next

'DELETE EXISTING QUERY SO THAT IT CAN BE REBUILT USING UPDATED PARAMETERS
DoCmd.DeleteObject acQuery, "1_Pass_Through_Queries_with_Parameters_DELETE"

'For the example I have hard coded the criteria but you can use text boxs etc
strSQL = "DELETE AS400_BPCS_IIM FROM AS400_BPCS_IIM WHERE AS400_BPCS_IIM.ICLAS='ZZ'"

Set dbs = CurrentDb()
Set qd = dbs.CreateQueryDef("1_Pass_Through_Queries_with_Parameters_DELETE", strSQL)

qd.SQL = strSQL
qd.Connect = "ODBC;DSN=sensdata;UID=TAYLORAL;DATABASE=SensData;Network=DBMSSOCN;Trusted_Connection=Yes;Regional=Yes"
qd.ODBCTimeout = 0
qd.ReturnsRecords = False

dbs.Close
qd.Close

Set dbs = Nothing
Set qd = Nothing

DoCmd.OpenQuery "1_Pass_Through_Queries_with_Parameters_DELETE", acViewNormal

DoCmd.Close acForm, Me.FormName, acSaveNo

End Function

 

[RoyVidar]

Feets,

so you have set the recordsource of the report to the pass-through query, and bound the fields in the report to the respective fields?

I don't know how your pass-through-query looks, but working with an SP, it could look like this

EXEC dbo.selectFamilyDetails '12345678'

Then, prior to calling your openreport routines, alter it to include the parameters from the form. The "rule" is to provide them in the same order as they have in the SP (air code).

dim db                        as dao.database
dim qd                        as dao.querydef

If len(Forms!frmWfsFamilyUpdate!txtFosterID) then
    set db = DBengine(0)(0)
    set qd = db.querydefs("NameOfPassThrough")

    qd.sql = "EXEC dbo.selectFamilyDetails '" & _
              Forms!frmWfsFamilyUpdate!txtFosterID & "'"

    db.querydefs.refresh

    set qd = nothing
    set db = nothing
    docmd.openreport "YourReport", acViewPreview, ....
else
    msgbox "no selection..."
end if

To check out how to get "code formatting", check out the "vB code" link at the bottom left of the entire page, or select your code, and hit the octothorpe (#) button in the reply window. This makes code easier to read...

 

[pbaldy]

I use a public function that does basically the same thing Roy posted.