[AccessD] Recordset based on query

 David,

You put the form back in again!

     Dim rsPopulateQtyBoxes As Recordset
     Set rsPopulateQtyBoxes = New ADODB.Recordset
     rsPopulateQtyBoxes.ActiveConnection = CurrentProject.Connection
     Dim sql As String
    'I presume cboSelectMember is a long?
    Dim lSelectMember as long

   lSelectMember =
[Forms]![frmHire_New_Members_Extend]![cboSelectMember]

   'Glad to see you now have spaces between the words
     sql = "SELECT tblHires.HireID, tblHires.title, tblHires.Renew,
tblRentalType.NewRelease " _
     & "FROM tblRentalType INNER JOIN (tblTitles INNER JOIN tblHires ON
tblTitles.tapeNumber = tblHires.title)" _
     & " ON tblRentalType.RentalTypeID = tblTitles.RentalTypeID" _
     & " WHERE (((tblHires.member)=" & lSelectMember & " ) AND " _
     & "((tblHires.Renew)=True) AND ((tblHires.DateReturned) Is Null));"


     Debug.Print sql

     rsPopulateQtyBoxes.Open sql, , adOpenKeyset, adLockOptimistic,
adCmdUnknown

'Always and I do mean always...
'test the value of you 'sql' variable to make sure you have a valid sql
statement.
'Print it out using debug or the immediate window and paste the string
into a query window and
'try running it,  because sometimes you can
'stuff it up in such a way that it works but returns unexpected results.

cheers


Michael M




-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David &
Joanne Gould
Sent: Tuesday, 27 June 2006 2:58 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Recordset based on query

Michael

Thinking that the sql string is too long I have changed the code to the
following:

Dim rsPopulateQtyBoxes As Recordset
     Set rsPopulateQtyBoxes = New ADODB.Recordset
     rsPopulateQtyBoxes.ActiveConnection = CurrentProject.Connection
     Dim sql As String

     sql = "SELECT tblHires.HireID, tblHires.title, tblHires.Renew,
tblRentalType.NewRelease " _
     & "FROM tblRentalType INNER JOIN (tblTitles INNER JOIN tblHires ON
tblTitles.tapeNumber = tblHires.title)" _
     & " ON tblRentalType.RentalTypeID = tblTitles.RentalTypeID" _
     & " WHERE
(((tblHires.member)=[Forms]![frmHire_New_Members_Extend]![cboSelectMembe
r])
AND " _
     & "((tblHires.Renew)=True) AND ((tblHires.DateReturned) Is Null));"


     Debug.Print sql

     rsPopulateQtyBoxes.Open sql, , adOpenKeyset, adLockOptimistic,
adCmdUnknown

This gave me the following error:
         run-time error: '-2147217904 (80040e10)'
         No value given for one or more required parameters.

David


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com