#Name? Error When Using SELECT in a Textbox

[bitsnbytes64]

Hi,

I have a test form with:

* textbox txtIXO_NR bound to column IXO_NR through the linked SQL Server 2005 table TXOCTC (link name "dbo_TXOCTC")
* an unbound textbox which should display the description of the value in the bound textbox using SELECT dbo_TXOBJCT.IXO_NA FROM dbo_TXOBJCT where dbo_TXOBJCT.IXO_NR = txtIXO_NR.Value; (I've tried different syntax to reference the value with no luck).
* an unbound textbox to display the value in the bound textbox using =txtIXO_NR.Value

When I type in the value in txtIXO_NR (e.g. 1) I get the #NAME? error in txtIXO_NA.

From what I found, the most common cause of this error is duplicate object names which, from what I can tell, is not an issue.

Any ideas ?

Thanks, Ed.

[Rabbit]

I don't think you can use a SQL statement as the control source or default value for a textbox. Look into the DLookup function.

[bitsnbytes64]

Hi Rabbit,

Here's a couple of the ones I tried. They both generate a #NAME? error:

=DLookUp("IXO_NA","TXOBJCT","IXO_NR=" & [Me].[txtIXO_NR])

=DLookUp("dbo_TXOBJCT.IXO_NA","dbo_TXOBJCT","dbo_T XOBJCT.IXO_NR=" & [txtIXO_NR.Value])

=DLookUp("IXO_NA","dbo_TXOBJCT","IXO_NR=" & Me!txtIXO_NR.Value)

=DLookUp("IXO_NA","TXOBJCT","IXO_NR=" & Me![txtIXO_NR.Value])

This one generates an #ERROR:

=DLookUp("IXO_NA","TXOBJCT","IXO_NR=" & txtIXO_NR.Value)
=DLookUp("IXO_NA","TXOBJCT","IXO_NR=" & [txtIXO_NR].[Value])

Based on what I was able to find, the only thing I can think of is that I'm referencing the textbox's value incorrectly or maybe it has something to do with using a linked table vs. local ?

Access is not my thing and I'm sorry to have to bother you with something that seems so simple yet is frustrating me to no end.....

Thanks !

[Rabbit]

If txtIXO_NR is of data type string then you need to delimit it as such using quotes.

=DLookUp("IXO_NA","dbo_TXOBJCT","IXO_NR='" & Me.txtIXO_NR & "'")

[bitsnbytes64]

Hi Rabbit,

I figured it out. Here's the code I added to the AfterUpdate event of the textbox txtIXO_NR:

Private Sub txtIXO_NR_AfterUpdate()

Dim IXOName As Variant
Me.txtIXO_NA = IXOName

If Not IsNull(Me.txtIXO_NR) Then
IXOName = DLookup("IXO_NA", "dbo_TXOBJCT", "IXO_NR = " & Me.IXO_NR)
Me.txtIXO_NA = IXOName
End If

End Sub

Thanks again for the help !

Ed.

[Rabbit]

Glad you found a solution. The code is a bit inefficient but achieves what you want.

On another note, the DLookup should be:

=DLookUp("IXO_NA","dbo_TXOBJCT","IXO_NR=" & txtIXO_NR)

I keep forgetting you can't use Me outside of VBA code.