Setting ControlSource in VBA (Syntax Problem)

[doma23]

I have an If-Else condition in VBA. I want to do next:

If (Not IsNull(Me.cmbSubstantive)) Then   
    Me!txtSub_Artikel.ControlSource = DLookup("[Artikel]", "tblSubstantive", "[Substantiv] = '" & [Forms]![frmMain]![cmbSubstantive] & "'")
 
Else
    Me.txtSub_Artikel.ControlSource = ""
End If

However, I'm having a problem with the syntax of DLookup when being assigned through VBA.
The problem is the same as if I try to put this Dlookup code into a string variable.

The error I'm getting is "Run-Time Error 13: Type mismatch".

Thanks!

[doma23]

Ok, I've changed the syntax to this:

Me.txtSub_Artikel.ControlSource = "=DLookup(""[Artikel]""; ""tblSubstantive""; ""[Substantiv] = '"" & [Forms]![frmMain]![cmbSubstantive] & ""'"")"

Now it gives me "run-time error 2438: the expression you entered contains invalid syntax".

[doma23]

Ok, I've solved it by using DLookup function directly from the VBA:

    Me.txtSub_Artikel.Value = DLookup("[Artikel]", "tblSubstantive", "[Substantiv] = '" & [Forms]![frmMain]![cmbSubstantive] & "'")

Although, still be interested to know how to assign the DLookup formula to ControlSource property of a text box.

[nico5038]

In general I try to minimize the use of DLookup() and the other D...() functions as they are slow.
For getting related data from a combobox selection into an unbound text field it's easier to add the field to the combo's ControlSource and suppress the column by giving the rowwidth the value 0 (zero).
Now the AfterUpdate event can be used to fill the textfield like:

Me.txtSub_Artikel = Me.cmbSubstantive.Column(1)
 

This assumes that the combo's rowsource query looks like:

select ArticleCode, Article from tblArticle
 

The column number is starting with 0 (zero) so the (1) will give the Article column.

Nic;o)

[NeoPa]

This is absolutely not an Insight. I will move it to the Answers forum.

[doma23]

@nico5038 - tnx, seems like a good idea.
@NeoPa - sorry, I don't know how it ended up in insights.

[NeoPa]

Doma:
sorry, I don't know how it ended up in insights.

No worries this time Doma.

Next time you see "Share an insight, write an article" in big bold letters at the top of the page though, you should realise you're not in the right place to post a question ;-)

I'm pleased you found a solution to your problem anyway :-)