Why will this not put a value in the box? Please

[Guest]

I am trying to update a textbox on gotfocus but the query is complex, so it
would be easier to use sql then a DLookup.
Can anyone tell me why no value shows up on focus

Private Sub File_GotFocus()
Dim sqlText As String
sqlText = "SELECT County_TownFileNo.FileNo FROM County_TownFileNo INNER JOIN
LocalEntity ON County_TownFileNo.Town = LocalEntity.LocalEntityName WHERE
(((LocalEntity.County)=Mid([Forms]![OrdersUnbound]![Section],3,2)) AND
((LocalEntity.EntityNum)=Right([Forms]![OrdersUnbound]![Section],2)));"
'check to make sure form is being filled out
If Me!FlagEdited = 1 Then
Me.File.ControlSource = sqlText
Me.File.Requery
End If
End Sub

Any Help is greatly appreciated

 

[tina]

you can't set the ControlSource of a combo box to a SQL statement in order
to get a return value. assuming that the query returns a maximum of one
record, it would be a lot easier to save the SQL as a query, then run a
DLookUp() on the query. if you are determined to use the SQL statement in
VBA, you'll need to go the whole nine yards and open a recordset, as

Dim rst As DAO.Recordset, strSQL As String
strSQL = "SELECT County_TownFileNo.FileNo FROM " _
& "County_TownFileNo INNER JOIN LocalEntity " _
& "ON County_TownFileNo.Town = LocalEntity." _
& "LocalEntityName WHERE LocalEntity.County='" _
& Mid([Forms]![OrdersUnbound]![Section],3,2) _
& "' AND LocalEntity.EntityNum=" _
& Right([Forms]![OrdersUnbound]![Section],2)"

' the above code assumes that County is a Text data type, and
' EntityNum is a Number data type.

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

If Not (rst.BOF and rst.EOF) Then
Me!File = rst("FileNo")
End If

rst.Close
Set rst = Nothing

hth