Results 1 to 3 of 3

Thread: Parameter query

  1. #1
    Join Date
    Mar 2004
    Posts
    79

    Unanswered: Parameter query

    I am trying to allow the user to enter an invoice number and system to display in combo box invoice plus all invoices after. I'm new to this and when this runs, I get a window that says 'Enter Parameter Value' for Me.InvoiceNbr. I have a text field on form (Me.InvoiceNbr) that I have already typed the invoice number into so the parameter window is redundant but the query must not know that. Thanks.


    Private Sub InvoiceNbr_AfterUpdate()
    With Me.lstBySearchType
    .RowSource = ""
    .RowSource = "SELECT tblInvoiceHdr.InvoiceNumber, tblCustomers.CustName," & _
    "tblInvoiceHdr.Date FROM tblCustomers RIGHT JOIN tblInvoiceHdr ON " & _
    "tblCustomers.CustNumber = tblInvoiceHdr.BillToNumber " & _
    "WHERE tblInvoiceHdr.Posted Is Null and tblInvoiceHdr.InvoiceNumber" & _
    ">= Me.InvoiceNbr ORDER BY tblInvoiceHdr.InvoiceNumber;"
    End With
    End Sub

  2. #2
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Smile

    Quote Originally Posted by brmk
    I am trying to allow the user to enter an invoice number and system to display in combo box invoice plus all invoices after. I'm new to this and when this runs, I get a window that says 'Enter Parameter Value' for Me.InvoiceNbr. I have a text field on form (Me.InvoiceNbr) that I have already typed the invoice number into so the parameter window is redundant but the query must not know that. Thanks.


    Private Sub InvoiceNbr_AfterUpdate()
    With Me.lstBySearchType
    .RowSource = ""
    .RowSource = "SELECT tblInvoiceHdr.InvoiceNumber, tblCustomers.CustName," & _
    "tblInvoiceHdr.Date FROM tblCustomers RIGHT JOIN tblInvoiceHdr ON " & _
    "tblCustomers.CustNumber = tblInvoiceHdr.BillToNumber " & _
    "WHERE tblInvoiceHdr.Posted Is Null and tblInvoiceHdr.InvoiceNumber" & _
    ">= Me.InvoiceNbr ORDER BY tblInvoiceHdr.InvoiceNumber;"
    End With
    End Sub
    Hi,

    Though I am not extremely knowledgeable of SQL, would it have something to do with the section like this:

    ">= Me.InvoiceNbr ORDER BY tblInvoiceHdr.InvoiceNumber;"??? That's the only area where I even see it reference the Me part. I am so used to working with the QBE grid but trying to learn the real SQL way. Just someone trying to help.

    Bud

  3. #3
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi

    What i think you need to do is instead of this
    Me.InvoiceNbr put in forms![frmname]!InvoiceNbr
    with frmname being the name of your form

    also instead of putting your rowsource through the code just put the SQL into the rowsource properties

    and in your
    Private Sub InvoiceNbr_AfterUpdate()
    do this
    Code:
    Private Sub InvoiceNbr_AfterUpdate()
       lstBySearchType.Requery
    End Sub
    there's probably a better way to do this but this is how i know how.

    Dave

    have this instead

Posting Permissions

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