[RESOLVED] SQL including the caption of a label

[Phantom1]

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.

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?

[Doogle]

I Think you need:

rsDetails.Open "SELECT TransactionID, ProductID, Quantity, Tax, Total FROM tblDetails WHERE TransactionID = '" &  lblTransactionID.Caption & "'"

[MarkT]

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

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

[Phantom1]

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.