Query Result in Variable (1 Viewer)

[key]

Hi Access-Friends,

I'm asking myself for 4 hours how can I run a query and save the result in a variable? I'd like to place it in the Form_Open event, because the user shouldn't notice that).
The variable is Boolean.

Could someone give me a small/big hint how to do it?

Many, many thanx in advance.

Key

 

[cogent1]

It would help if you posted the query. You will probably have to express your query as an SQL statement in a function.

 

[key]

The query is pretty simple...

"SELECT ID.Indicator FROM Table1 WHERE ID=" &Me!cboList.Column(0)

Many, many thanx

Key

 

[Pat Hartman]

Assuming that the column you want is in the same table that the combo draws from, change the rowsource of the combo to a query to include the extra field. Then in the AfterUpdate event of the combo and in the Current of the form put:

Me.SomeControlName = Me.cbolist.Column(1)

The columns of a combo's rowsource are a zero based array. So the first column is .Column(0) and the second is .Column(1).

If the value that you are trying to display is in a different table, you can use a join in the combo's rowsource query to pick it up.

You cannot place the output of a query directly into a control because they are differnt object types. You could use a DLookup() based on a query or table since it is a function and functions return a single field.

Another thing for futur reference, you cannot reference the Column property of a combo in a query since that is a VBA object. You can however refer to it using the longer syntax -
Forms!SomeFormName!cboList