error on control source expression

[almoes]

Hi all,

It looks like a have an error on an expression for the property control source of a textbox, but I don't know what fails.

The expression is:

=IIf(IsNull(DLookUp("[ProductName]";"Menu";"[ProductID] =" & [txt1]));"";DLookUp("[ProductName]";"Menu";"[ProductID] =" & [txt1]) & ", ") & IIf(IsNull(DLookUp("[ProductName]";"Menu";"[ProductID] =" & [txt2]));"";DLookUp("[ProductName]";"Menu";"[ProductID] =" & [txt2]) & ", ") & IIf(IsNull(DLookUp("[ProductName]";"Menu";"[ProductID] =" & [txt3]));"";DLookUp("[ProductName]";"Menu";"[ProductID] =" & [txt3]) & ", ") & IIf(IsNull(DLookUp("[ProductName]";"Menu";"[ProductID] =" & [txt4]));"";DLookUp("[ProductName]";"Menu";"[ProductID] =" & [txt4]) & ", ") & IIf(IsNull(DLookUp("[ProductName]";"Menu";"[ProductID] =" & [txt5]));"";DLookUp("[ProductName]";"Menu";"[ProductID] =" & [txt5]) & ", ") & IIf(IsNull(DLookUp("[ProductName]";"Menu";"[ProductID] =" & [txt6]));"";DLookUp("[ProductName]";"Menu";"[ProductID] =" & [txt6]) & ", ") & IIf(IsNull(DLookUp("[ProductName]";"Menu";"[ProductID] =" & [txt7]));"";DLookUp("[ProductName]";"Menu";"[ProductID] =" & [txt7]))

If the values of Dlookup are ALL NOT null then there's no error, but if one of then is null, an error appears. Whats wrong with the if statement?

thanxs!
alej

 

[PHV]

Take a look at the Nz function

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

How are ya almoes . . . . .

Your biggest problem is your using [blue]semicolons ([purple];[/purple]) as arguement seperators.[/blue] You should be using commas ([purple],[/purple]) . . . . So you need to make that change theroughout!

But don't runoff so fast to correct it. I've rendered your code in a [blue]VBA Function[/blue] called [purple]ProductBuild[/purple]. So now your property call would be:

[blue] = ProductBuild()[/blue]

Be aware: For your purpose [blue]VBA is much faster than the IIf statments[/blue], mainly because [purple]both True & False parts of an IIf are evaluated before the correct value is returned![/purple] Add to that, you have the timely DLookUp twice in each IIf and you'll certainly have enoughtime to get a cup of coffee! ;-)

So in the code module of the form, copy/paste the function below and your set. [blue]Notice the readibility of the code[/blue] . . . . how sweet it is. This is a sample of the power of VBA working in more than one way:

[blue]Public Function ProductBuild() As String
   Dim DLK, Build As String

   DLK = DLookup("[ProductName]", "Menu", "[ProductID] = " & [txt1])
   If Not IsNull(DLK) Then Build = DLK & ", "
   
   DLK = DLookup("[ProductName]", "Menu", "[ProductID] = " & [txt2])
   If Not IsNull(DLK) Then Build = Build & DLK & ", "
   
   DLK = DLookup("[ProductName]", "Menu", "[ProductID] = " & [txt3])
   If Not IsNull(DLK) Then Build = Build & DLK & ", "
   
   DLK = DLookup("[ProductName]", "Menu", "[ProductID] = " & [txt4])
   If Not IsNull(DLK) Then Build = Build & DLK & ", "
   
   DLK = DLookup("[ProductName]", "Menu", "[ProductID] = " & [txt5])
   If Not IsNull(DLK) Then Build = Build & DLK & ", "
   
   DLK = DLookup("[ProductName]", "Menu", "[ProductID] = " & [txt6])
   If Not IsNull(DLK) Then Build = Build & DLK & ", "
   
   DLK = DLookup("[ProductName]", "Menu", "[ProductID] = " & [txt7])
   If Not IsNull(DLK) Then Build = Build & DLK
   
End Function[/blue]

If your game . . . give it a whirl and let me know . . . .

See Ya! . . . . . .

 

[almoes]

Hi AceMan,

At the end it was working, the field that Dlookup compared was null sometimes and this gave an error instead of a null, so I checkout for not null field before.

Thanks for the tip of performance, I will write it as a function.

Regarding the ';', if you write it on the expression editor you need semicolons instead of commas, dont ask me why! :-S

thanxs!
alej

 

[almoes]

ups...one last thing! how can I use this same function for another form? thanxs!

alej

 

[TheAceMan1]

almoes . . . . .

I'm glad you asked. I forgot to assign the result to the function. So it should be:

[blue]Public Function ProductBuild() As String
   Dim DLK, Build As String

   DLK = DLookup("[ProductName]", "Menu", "[ProductID] = " & [txt1])
   If Not IsNull(DLK) Then Build = DLK & ", "
   
   DLK = DLookup("[ProductName]", "Menu", "[ProductID] = " & [txt2])
   If Not IsNull(DLK) Then Build = Build & DLK & ", "
   
   DLK = DLookup("[ProductName]", "Menu", "[ProductID] = " & [txt3])
   If Not IsNull(DLK) Then Build = Build & DLK & ", "
   
   DLK = DLookup("[ProductName]", "Menu", "[ProductID] = " & [txt4])
   If Not IsNull(DLK) Then Build = Build & DLK & ", "
   
   DLK = DLookup("[ProductName]", "Menu", "[ProductID] = " & [txt5])
   If Not IsNull(DLK) Then Build = Build & DLK & ", "
   
   DLK = DLookup("[ProductName]", "Menu", "[ProductID] = " & [txt6])
   If Not IsNull(DLK) Then Build = Build & DLK & ", "
   
   DLK = DLookup("[ProductName]", "Menu", "[ProductID] = " & [txt7])
   If Not IsNull(DLK) Then Build = Build & DLK

   [purple][b]ProductBuild = Build[/b][/purple]
   
End Function[/blue]

To use it in VBA:

[blue]StringVariable = ProductBuild()[/blue]

In the ControlSource of a TextBox:

[blue] = productBuild()[/blue]

See Ya! . . . . . .

 

[almoes]

Ah yes, the syntax error I got it, however if the function is associated to the module of the form even I set it as public I can't use it on another form, I do something wrong?

cheers,
alej

 

[TheAceMan1]

almoes . . . . .

No . . . your not doing anything wrong. [blue]The form where the code resides has to be open in order to use the code from another source![/blue] From what I gather, you need a [purple]Global Routine[/purple].

Are you saying this alternate form has the same control & table Names involved?

If so, then [blue]add the following Global Code (crunched)[/blue] to a module in the modules window:

[blue]Public Function ProductBuild(frmName As String) As String
   Dim DLK, Build As String, n As Integer, txtN
   
   For n = 1 To 7
      txtN = Forms!frmName("txt" & LTrim(Str(n)))
      DLK = DLookup("[ProductName]", "Menu", "[ProductID] = " & txtN)
      If Not IsNull(DLK) Then Build = Build & DLK & ", "
   Next
   
   ProductBuild = Left(Build, Len(Build) - 2)
   
End Function[/blue]

[blue]To call the function[/blue] from any form (not subform . . . subform requires additional coding) us the following:

[blue]StringVariable = ProductBuild("[purple][b]CallingFormName[/b][/purple]")

[purple]or[/purple]

 = ProductBuild("[purple][b]CallingFormName[/b][/purple]")[/blue]

[purple]Be aware: The code does not check if the form is open![/purple]

See Ya! . . . . . .

 

[almoes]

Aha, ok thats why it was not working....yep both have the same names for the controls, in fact its a report and a form which need the same functionality. I just thought why write the same function twice if i can reuse it. Thanxs, thats a cool idea!

alej

 

[almoes]

AcaMan, and is it possible to assign to a ControlSource a column of a recordset?

 

[TheAceMan1]

almoes . . . . . .

So sorry! . . . . somehow your latest post got burried in my mail.

The answer is [purple]No![/purple] What ever you put in the [blue]Control Source[/blue] [purple]has to return a single value![/purple]

See Ya! . . . . . .

 

[almoes]

No problem....ok. I have another question, I posted it in the queries forum but maybe you can help me, is it possible to combine two select statements? thanxs!

 

[TheAceMan1]

almoes . . . . . .

Yes! . . . . search for [purple]Union Queries[/purple] in Access help . . . .

See Ya! . . . . . .