-
Apr 24th, 2012, 02:30 AM
#1
Thread Starter
Lively Member
[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...
-
Apr 24th, 2012, 02:57 AM
#2
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 & "'"
-
Apr 24th, 2012, 06:28 AM
#3
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
-
Apr 24th, 2012, 11:10 AM
#4
Thread Starter
Lively Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|