calling a query that requires input from code

[TJones8]

I have a query that asks the user for the name of a computer workstation and returns the room it in in. I want to convert it so when the user selects the computer name from a drop-down combobox on a form, it runs the query with the selected name and returns the name to a static-text caption.
I already have the form set up and the combo box works fine (and i use the onChange event to update the caption of the static text).
The only thing i cannot do is call the same query, passing to it the selected station name and capture the room name.
I guess the syntax would be something like :-
<StaticText>.Caption = CurrentDb.*call query command*(&quot;<QueryName>!<StationName>&quot
But this is my first time in VB (I'm more of a C++ guy myself ;-) ) and i just can't figure it out.

TIA

T Jones

 

[JoanneM]

By static text do you mean the label at the side of the combo box or the text in the status bar at the bottom of the screen


regards

Jo

 

[KyleS]

You could just use a built-in Aggregate function DLookup.

DLookup has 3 arguements
1)Field Name you want returned
2)Table Name that the field resides in
3)Condition statements (basically a SQL WHERE statement without WHERE)

So if I wanted the field &quot;Name&quot; from table &quot;tblEmployees&quot; where EmpID = 7 my DLookup would look like this:

Me!txtName = DLookup(&quot;[Name]&quot;,&quot;[tblEmployees]&quot;,&quot;[tblEmployees].[EmpID] = 7&quot

Your syntax would look somehting like this:


YourLabelName.Caption = DLookup(&quot;[FieldName]&quot;,&quot;[TableName]&quot;,&quot;[TableName].[PCName] = '&quot; & Me!YourComboBoxName & &quot;'&quot

To use the query to get one field would be more work than it's worth... Kyle

 

[TrojanRabbit]

There are different ways to do it. Like KyleS said, if it's just in a table, all you have to do is find the record and the field. It sounds like you already have the query made, so you can use the combo box value there. For criteria, use the following:

[Forms]![<form name>]![<name of combo box>]

You can use a recordset object either way. If it's in a table, use FindFirst to get it. If you have to use the query, use MoveFirst.

Dim rst As Recordset

Set rst = Currentdb.OpenRecordset(&quot;<table name or query name>&quot;, dbOpenDynaset)

(if table)
rst.FindFirst &quot;<computer name field> = '&quot; & <combo box> & &quot;'&quot;
(assuming a text name)
<label>.Caption = rst!<room field>

(if query)
rst.MoveFirst
<label>.Caption = rst!<room field>

 

[TJones8]

Thx alot !!!
I just used 2 DLookup funtions, since the station had room IDs and the room names (with id) were held in another table.
Much thx to all again - i'll be back soon i'm sure