How use data from a field in an SQL string?

[Carl Philip]

Hello folks;

I would like to know how I can insert the value of a field on my form in an SQL string.
For example:
There is a field on my form with the name txtKey; it contains a number.
I would like to make a query which uses the current number in that field to search in a database:
"SELECT Name, IDNumber FROM People
where IDNumber = txtKey"
This doesn't work, I know, but how can I do this?
How can I use the current value of a field in this Query?

Thanks in advance for any reply;
Carl

[Rob@SYPTE]

try:

strSql = "SELECT Name, IDNumber FROM People WHERE IDNumber = " & txtKey.text

Cheers

Rob

[Carl Philip]

Thanks a lot Rob!

Your Query
strSql = "SELECT Name, IDNumber FROM People WHERE IDNumber = " & txtKey.text
works perfectly.

Now, if anybody can explain me how to put the result from this query in a Datagrid. I Can't find out how to connect a Datagrid to my connection.
I am using the following connection for this query:
Dim cnn2 As New ADODB.Connection
Dim cmd2 As New ADODB.Command
Dim rs2 As New ADODB.Recordset
Once I know how to do this, I can finish my program.

Thanks you many times in advance.
Carl
Brussels, Belgium

[pkmrafeek]

The answer is

"SELECT Name, IDNumber FROM People
where IDNumber =" & val(txtKey)

[gvphubli]

Assuming ( frmMainScreen ) is the name of the form and ( msgFGInvDet ) is msFlexGrid control in that
form and I'am using RDO resultset (make respective changes to resultset only, other things remain same )
ShowRecSet is the name of the Resultset
Execquery is just a command text
glbCON is connection name

Code starts from here below:-

' this string format can set in design time as well here I'am setting it at run time ok.

frmMainScreen.msgFGInvDet.FormatString = "DNP" & _
"|Clear Date |<Date " & _
"|<Club |<Payment Number " & _
"|>Amount |<Bank " & _
"|<Payment Type |<Payment Information|<Currency Code |<Batch No. |<Comment "

With frmMainScreen.msgFGInvDet

.Rows = 1

Execquery = " Use TestDbase SELECT * FROM CuMe_Curr_Pment "

Set ShowRecSet = glbCON.OpenResultset(Name:=Execquery, _
Type:=rdOpenForwardOnly, LockType:=rdConcurReadOnly, _
Options:=rdExecDirect)

Do Until ShowRecSet.EOF
.Rows = .Rows + 1
If ShowRecSet!Pment_Not_Process = 1 Then
.TextMatrix(.Rows - 1, 0) = "X" ' This is if U want to display something else in the grid based on the result of resultset
.row = .Rows - 1: .Col = 0: .CellFontBold = True
.CellForeColor = vbRed
Else
.TextMatrix(.Rows - 1, 0) = "-"
End If

If Trim(ShowRecSet ! Pment_Status) = "PRBLM" Then
.TextMatrix(.Rows - 1, 1) =Trim(ShowRecSet!Pment_Status)
else: .TextMatrix(.Rows - 1, 1) ="X"
.row = .Rows - 1: .Col = 1: .CellFontBold = True
.CellForeColor = vbRed:


.TextMatrix(.Rows - 1, 9) = ShowRecSet ! Pment_authoriz
.TextMatrix(.Rows - 1, 10) = ShowRecSet ! Pment_Curr_Code
.TextMatrix(.Rows - 1, 11) = ShowRecSet ! Pment_Batch_Claim
.TextMatrix(.Rows - 1, 12) = ShowRecSet ! Pment_Info

ShowRecSet.MoveNext
Loop
ShowRecSet.Close

End With


here ends the code...

do this till Ur resultset columns or the grid columns are over.

[Kain]

try :
"SELECT Name, IDNumber FROM People
where IDNumber = " & txtKey.text
i sounds better
Hope this is the answerd