Alex, the simplest solution would be to use code instead of a macro.
You can code long lines, such as:
[SomeControl] = IIf([forms]![NewPartInputfrm]![Ref Part
QTY]<0.0001,0,DLookUp("[FIN
WTLB]","ExcelARefParts","[Part#]=Forms![NewPartInputfrm].RefPart AND [As
Part#]=Forms![NewPartInputfrm].[Ref Part NHL] AND
[Model#]=Forms![NewPartInputfrm].[Model#]"))/([forms]![NewPartInputfrm]![Ref
Part Qty]
But you can also write simpler expressions.
Advantages:
- Use If ... Else ... EndIf instead of cramming it all into one IIf().
- Shorten the form references.
- Avoid the Null errors in conditions, and be more flexible.
- Return the value of DLookup() to a variable if it helps.
- Break it apart for easier debugging (e.g. Debug.Print)
- Use error handling.
Dim strWhere As String
Dim lngLen As Long
If Me.[Ref Part Qty] = 0 Then 'Prevent division by zero.
Me.SomeControl = 0
Else 'Handle all the conditions, including Nulls.
If Not IsNull(Me.RefPart) Then
strWhere = strWhere & "([Part#] = """ & Me.RefPart & """) AND "
End If
If Not IsNull(Me.[Ref Part NHL]) Then
strWhere = strWhere & "([As Part#] = """ & Me.[Ref Part NHL] & """)
AND "
End If
If Not IsNull(Me.[Model#] Then
strWhere = strWhere & "([As Model#] = """ & Me.[Model#] & """) AND "
End If
lngLen = Len(strWhere) - 5 'Chop off the trailing " AND ".
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
End If
Debug.Print strWhere 'Just for debugging.
Me.SomeControl = DLookUp("[FIN WTLB]","ExcelARefParts", strWhere)
End If
Note: Remove the extra quotes where the fields are of type Number. More info
on that:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html
The code is written to make it easy to add more text boxes if needed. If you
are intersted in an example of this. see:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Alex said:
Can anyone tell me how to shorten the following SetValue macro expression?
I'm getting an error msg that it's too long. Changing field or form names
is
not an option; way too late in the game for that. Thanks.
IIf([forms]![NewPartInputfrm]![Ref Part QTY]<0.0001,0,DLookUp("[FIN
WTLB]","ExcelARefParts","[Part#]=Forms![NewPartInputfrm].RefPart AND [As
Part#]=Forms![NewPartInputfrm].[Ref Part NHL] AND
[Model#]=Forms![NewPartInputfrm].[Model#]"))/([forms]![NewPartInputfrm]![Ref
Part Qty]
