.value and SELECT problem

[monfas]

Hi guys, I'm having an anoying problem and I'm asking if anyone knows how to solve it.

I this a simple code:

Me.dID.value = "SELECT [tdonors].[id] FROM tdonors where [tdonors].dname = '" & Me.DName.Value & "'"

Where the text box dID will be updated with the ID value from a table named tdonors, according to a name chosen in the list box me.Dname.value

what is happening, is that instead of writing the ID value (a number) is writing literally SELECT [tdonors].[id] FROM tdonors where [tdonors].dname = 'XXXX'

Being the XXX the name of the donor.

I tried to convert the text box dID to a list box, and re-write the code in the following manner:

Me.dID.RowSource = "SELECT [tdonors].[id] FROM tdonors where [tdonors].dname = '" & Me.DName.Value & "'"

In this case, using rowsoure, it actually fetches the ID number from tDonor.

I can make it work this ways, but is anoying me not being able to understand slightly what is going on.

If anyone can help, that would be great.

Thanks,

 

[pbaldy]

I moved this from the moderated samples forum. The problem is that a textbox can't have SQL as its value. You could use a DLookup() instead, or open a recordset.

 

[missinglinq]

Given the Field/Table names in your SQL statement, this should do it:

Me.dID.RowSource = DLookup("id", "tdonors", "dname = '" & Me.DName & "'")

Linq ;0)>