In Access, How set object to SQL Select Statement

[tomcone]

I want to have an On Change event which sets an unbound field in the subform to be the equal to the result of a SQL Select query. What is the VBA code to say:

UnboundCntrol= Select .. from ... where ...;

I keep getting compile errors on my attempts.

Thx

 

[xlbo]

Not overly familar with this but I would suggest that you need to set the SQL as a string and then reference the RECORDSET returned by it

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[mndrlion]

I believe that the DLookup Function is what you are looking for.

UnboundControl = DLookup("FieldName", "TableName", "Criteria")

 

[TonyJollans]

Hi tomcone,

mndrlion is right. DLookup is almost certainly what you're after. You should not, however, normally need to set it in code. If you set the Control's Control Source Property to it that ought to be sufficient

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.