SQL vba ? (1 Viewer)

[RpbertS]

this is not working heres my code:
txtname= SELECT managers.mlastname, managers.MM FROM managers WHERE (((managers.MM)= Forms![intro]!txtmm.value));

what I want is:
txtname to display the name where managers.mm = txtmm on the intro form.

any guidance?

 

[Abby N]

You're encountering a problem because an SQL statement produces a recordset, not text, even if that recordset only contains one record with one field. Something like this should do the trick.

Dim rstA as recordset
Set rstA = CurrentDb.OpenRecordset(SELECT managers.mlastname AS mlastname FROM managers WHERE (((managers.MM)= Forms![intro]!txtmm.value)); )
txtname = rstA.mlastname

This assumes that txtname is the name of a variable. If it's the name of a field on your form you should use Me.txtname in your code.
Alternatively, what I'd recommend is using DLookup instead of an SQL statement. In code it would look something like:

txtname = DLookup"[mlastname]","managers","[MM] = " & Forms!intro!txtmm)

Or, you could use that as the source for an unbound control. Just drop the 'txtname' from the beginning. Good luck.

~Abby


[This message has been edited by Abby N (edited 12-28-2000).]