[AccessD] Recordset based on query

Thanks, Michael.

The field type for cboSelectmember is text, so now I just need to figure 
out how to get the sql string to recognise it.

David

At 03:59 PM 27/06/2006, you wrote:
>  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
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com