using variable in docmd.runsql

[Robert]

I want to use an existing value from a form to run a sql
stmt. Not sure how to concatenate...
docmd.runsql "select name from tbl where id =" & me.value
Gets me no where. Please set me straight.

 

[RobFMS]

If I am understanding your question correctly, you will need to create a
text box that contains the ID field as its controlsource.

It is recommended that you keep this field hidden (i.e. Visible=False).

Then you can use "Select Name from Tbl WhereID = " & Me.<name of text box>

HTH


--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

[Bruce M. Thompson]

I want to use an existing value from a form to run a sql

stmt. Not sure how to concatenate...
docmd.runsql "select name from tbl where id =" & me.value
Gets me no where. Please set me straight.

That's because RunSQL is used to execute an action query (update, delete,
append, etc.), not to open a SELECT query. If you are trying to open a query
window to display information, you can set the sql of an existing querydef
(create a "dummy" querydef for this, such as "qrySQL") and set its SQL in code:

'***
'Set the querydef's SQL
CurrentDb.QueryDefs("qrySQL").SQL = _
"select name from tbl where id =" & me.value

'Open the query
DoCmd.OpenQuery "qrySQL"
'***

If you are merely trying to retrieve a value from the table, use the "DLookup()"
function, instead:

Dim strName As String
strName = DLookup("name", "tbl", "id=" & Me.Textbox.Value)