Results 1 to 4 of 4

Thread: [RESOLVED] SQL including the caption of a label

  1. #1

    Thread Starter
    Lively Member Phantom1's Avatar
    Join Date
    Nov 2011
    Posts
    64

    Resolved [RESOLVED] SQL including the caption of a label

    Code:
    With rptReceipt
        Set .DataSource = rsDetails 'taking data from tblDetails
        .DataMember = ""
        rsDetails.Requery
        Do While Not rsDetails.EOF
            If rsDetails(1) = lblTransactionID.Caption Then
                With .Sections(3).Controls
                    .Item(1).DataField = rsDetails(2).Name
                    .Item(2).DataField = rsDetails(3).Name
                    .Item(3).DataField = rsDetails(4).Name
                    .Item(4).DataField = rsDetails(5).Name
                    Exit Do
                End With
            End If
            rsDetails.MoveNext
        Loop
        .Show vbModal, Me
    End With
    I need to fetch the records where TransactionID = lblTransactionID.Caption. "If rsDetails(1) = lblTransactionID.Caption Then" does not make any difference as all the records are taken, regardless of whether "rsDetails(1) = lblTransactionID.Caption".

    I have tried the following.

    Code:
    rsDetails.Close
    rsDetails.Open "SELECT TransactionID, ProductID, Quantity, Tax, Total FROM tblDetails WHERE TransactionID = lblTransactionID.Caption"
    With rptReceipt
        Set .DataSource = rsDetails
        .DataMember = ""
        rsDetails.Requery
        With .Sections(3).Controls
            .Item(1).DataField = rsDetails(2).Name
            .Item(2).DataField = rsDetails(3).Name
            .Item(3).DataField = rsDetails(4).Name
            .Item(4).DataField = rsDetails(5).Name
        End With
        .Show vbModal, Me
    End With
    It does not work because "WHERE TransactionID = lblTransactionID.Caption" is not a valid SQL statement.

    How do I solve this?
    Last edited by Phantom1; Apr 24th, 2012 at 11:11 AM.
    Learning to Program on Earth until I go into Outer Space...

  2. #2
    PowerPoster
    Join Date
    Jul 2006
    Location
    Maldon, Essex. UK
    Posts
    6,334

    Re: SQL including the caption of a label

    I Think you need:
    Code:
    rsDetails.Open "SELECT TransactionID, ProductID, Quantity, Tax, Total FROM tblDetails WHERE TransactionID = '" &  lblTransactionID.Caption & "'"

  3. #3
    PowerPoster
    Join Date
    Jun 2001
    Location
    Trafalgar, IN
    Posts
    4,141

    Re: SQL including the caption of a label

    Try changing
    If rsDetails(1) = lblTransactionID.Caption Then

    to
    If rsDetails.Fields(1).Value = lblTransactionID.Caption Then

  4. #4

    Thread Starter
    Lively Member Phantom1's Avatar
    Join Date
    Nov 2011
    Posts
    64

    Re: SQL including the caption of a label

    When rsDetails(1) = lblTransactionID.Caption, the IF construct is entered, but all the records are read, not only the one where rsDetails(1) = lblTransactionID.Caption. The IF condition has no effect.

    Plugging the SQL string to the label's caption works.
    Learning to Program on Earth until I go into Outer Space...

Posting Permissions

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



Click Here to Expand Forum to Full Width