Compile/Syntax error in notation of VBcode

[Tep]

Hi,

I am trying to set a value (vor_id) by using a select-statement:

Form_22BeherenResidunormen.vor_id = select dbo_verontreiniging.vor_id FROM dbo_verontreiniging WHERE dbo_verontreiniging.sto_id = " & CurrentStof & "

I get the following message: "Compile error. Expected: expression".
I think it has something to do with " " or ( ) or ' ' I have to use for the select statement. But I can not find the correct notation.
Debugging highlights above statement and states "syntax error".

In case the remainder of the code is also necessary in this problem here it is:
---
Private Sub buttonToevoegenResidunorm_Click()

On Error GoTo Err_Command0_Click

Dim stDocName As String
Dim stLinkCriteria As String

If ((CurrentProd <> 0) And (CurrentStof <> 0)) Then
stDocName = "22BeherenResidunormen"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd

Form_22BeherenResidunormen.pro_id = CurrentProd
Form_22BeherenResidunormen.sto_id = CurrentStof
Form_22BeherenResidunormen.vor_id = select dbo_verontreiniging.vor_id FROM dbo_verontreiniging WHERE dbo_verontreiniging.sto_id = " & CurrentStof & "
Form_22BeherenResidunormen.ProduktText.Value = DLookup("[pro_name]", "dbo_produkt", "[pro_id] = " & CurrentProd)
Form_22BeherenResidunormen.StofText.Value = DLookup("[sto_name]", "dbo_stof", "[sto_id] = " & CurrentStof)

Else
MsgBox ("Er moet eerst zowel een produkt als een stof geselecteerd worden.")
End If
Exit_Command0_Click:
Exit Sub
Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub
---

I hope someone can help me!
Thanks in advance.

 

[namliam]

You cannot use a select statement like this, a select statement must be used to create a recordset....

Instead in this case the short cut solution would be to use a DLookup like your doing for ProduktText and StofText

MVG uit amsterdam

 

[Tep]

Of course... Great! Thanks.
Greetings from Utrecht