Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Parameter in Recordset (Access 2000)

    I am trying to use the results of this subquery into a recordset. The problem is that it requires one parameter in the last WHERE clause of the query. The parameter is a value from the form. How can I reference the results from this query in Code?

    My query is:

    SELECT OrderedProducts.ProductID, OrderedProducts.IsMixed, OrderedProducts.InvoiceID, OrderedProducts.QtyOrdered, Products.QtyOnHand
    FROM Invoice INNER JOIN (OrderedProducts INNER JOIN Products ON OrderedProducts.ProductID = Products.ProductID) ON Invoice.InvoiceID = OrderedProducts.InvoiceID
    WHERE ((((Products.QtyOnHand)>[OrderedProducts].[QtyOrdered])
    AND OrderedProducts.DateFilled) IS Null
    AND ((OrderedProducts.ProductStatus)="B")
    AND (OrderedProducts.InvoiceID) IN
    (SELECT Invoice.InvoiceID
    FROM Invoice
    WHERE (([Invoice].[AllProductsScanned])=False))) AND (([Invoice].[ConsultantID])=[Forms]![FRM_FaxInCustomer].[ConsultantID]);

    Thanks

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Parameter in Recordset (Access 2000)

    You need to use a reference to the query via a QueryDef and then use the .parameter property.
    If only we had the Search facility you could just search for .Parameter .

    The following example references a parameter query and supplies values for the parameters:

    Dim dbs As Database, qdf As QueryDef, rst As Recordset
    Dim prm As Parameter, strSQL As String

    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Open QueryDef object.
    Set qdf = dbs.QueryDefs("YourQuery")

    ' Supply values for parameters.
    qdf.Parameters![Beginning OrderDate] = #4/1/95#
    qdf.Parameters![Ending OrderDate] = #4/30/95#
    ' Open recordset on QueryDef object.
    Set rst = qdf.OpenRecordset
    rst.MoveLast
    MsgBox "Query returned " & rst.RecordCount & " records."
    rst.Close
    Set dbs = Nothing

    Hans will probably enlighten you further on this.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Parameter in Recordset (Access 2000)

    COOL!!!!!!!!!!

    Thanks for the help. It's worken perfect now

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •