problem with query in recordset

[neelesh kumar]

Dim rst As DAO.Recordset
Dim DB As DAO.Database
set DB=currentDb()
Dim testid as string
testid = DLookup("tstid" , "tblfinalex am", "traineeno = '200 '")
up to here its working fine.it is taking testid from table.
But i want to initialise the testid to tstid in opening record set.
Set rst = DB.OpenRecordse t("select subcode from tblt20 where tstid = 'testid'", dbOpenDynaset)
here it is not taking tstid from the table.how to assign tstid=testid in where condition in my code
please help me
thanks in advance.

[ADezii]

Dim rst As DAO.Recordset
Dim DB As DAO.Database
set DB=currentDb()
Dim testid as string
testid = DLookup("tstid" , "tblfinalex am", "traineeno = '200 '")
up to here its working fine.it is taking testid from table.
But i want to initialise the testid to tstid in opening record set.
Set rst = DB.OpenRecordse t("select subcode from tblt20 where tstid = 'testid'", dbOpenDynaset)
here it is not taking tstid from the table.how to assign tstid=testid in where condition in my code
please help me
thanks in advance.

If testid is a Number, then:

Set rst = DB.OpenRecordset("select [subcode] from tblt20 where [tstid] = " & testid, dbOpenDynaset)

[neelesh kumar]

sir,
testid is a string.
what u write is not working bcas testid is a string.
please help me.

[ADezii]

sir,
testid is a string.
what u write is not working bcas testid is a string.
please help me.

Set rst = DB.OpenRecordset("select subcode from tblt20 where tstid = '" & testid & "'", dbOpenDynaset)

[neelesh kumar]

sir,
testid = DLookup("tstid" , "tblfinalex am", "[traineeno] = '" & Forms!frmlogexa m!txtno & " '")
its working fine.
but if a particular traineeno is there in the table,then i am getting invalid use of null.
if testid=null then i want to show message box with out giving error.
how to write the code please help me.
i tried Nz(testid) and isnull(testid) but still eror is coming.

[ADezii]

sir,
testid = DLookup("tstid" , "tblfinalex am", "[traineeno] = '" & Forms!frmlogexa m!txtno & " '")
its working fine.
but if a particular traineeno is there in the table,then i am getting invalid use of null.
if testid=null then i want to show message box with out giving error.
how to write the code please help me.
i tried Nz(testid) and isnull(testid) but still eror is coming.

As you have indicated, testid is a String and Strings cannot accept Null Values, only Variants can. Try this:
testid = Nz(DLookup("tst id", "tblfinalex am", "[traineeno] = '" & Forms!frmlogexa m!txtno & " '"))

If Len(testid) = 0 Then     'testid is an Empty String
   'Message Box for no return value
Else
   'Value returned from DLookup()
End If