1

I have a line of code (below) that i would like to break into a new line. I have tried to insert (_) but haven't had any success. Any suggestions?

DoCmd.OpenForm "qrysearch", acNormal, "", "([Firm]=[Forms]![search]![cmb_firm] Or IsNull([Forms]![search]![cmb_firm]))And ([City]=[Forms]![search]![cmb_city] Or IsNull([Forms]![search]![cmb_city]))And ([Region]=[Forms]![search]![cmb_region] Or IsNull([Forms]![search]![cmb_region]))And ([Primary Contact First Name]=[Forms]![search]![cmb_firstname] Or IsNull([Forms]![search]![cmb_firstname]))And ([Major Corridor]=[Forms]![search]![cmb_majorcorridor] Or IsNull([Forms]![search]![cmb_majorcorridor]))And ([Minor Corridor]=[Forms]![search]![cmb_minorcorridor] Or IsNull([Forms]![search]![cmb_minorcorridor]))And ([BayAreCommuterBenefitType]=[Forms]![search]![cmb_commuterbenefits] Or IsNull([Forms]![search]![cmb_commuterbenefits]))And ([Bike Parking]=[Forms]![search]![cmb_bikeparking] Or IsNull([Forms]![search]![cmb_bikeparking]))And ([Green Business Program]=[Forms]![search]![cmb_greenbusiness] Or IsNull([Forms]![search]![cmb_greenbusiness]))", , acNormal
2
  • 2
    Concatenate the string up with a variable - sMyString = sMyString & " more stuff " Commented Dec 19, 2019 at 22:12
  • 1
    Note that the maximum length of a physical line of code can't exceed 1024 characters: that line is 960 characters long! Close call! Commented Dec 19, 2019 at 22:34

1 Answer 1

3

Separate the concerns of building the criteria string and invoking it, and then use _ line continuations and & string-concatenation operators to make the massive string literal somewhat readable/maintainable - Rubberduck's Smart Concat feature can really help with that: all you'd need to do is hit Enter wherever you'd like to split the string, and end up with something like this:

Const criteria As String = "([Firm]=[Forms]![search]![cmb_firm] Or IsNull([Forms]![search]![cmb_firm])) " & _
    "And ([City]=[Forms]![search]![cmb_city] Or IsNull([Forms]![search]![cmb_city])) " & _ 
    "And ([Region]=[Forms]![search]![cmb_region] Or IsNull([Forms]![search]![cmb_region])) " & _
    "And ([Primary Contact First Name]=[Forms]![search]![cmb_firstname] Or IsNull([Forms]![search]![cmb_firstname])) " & _
    "And ([Major Corridor]=[Forms]![search]![cmb_majorcorridor] Or IsNull([Forms]![search]![cmb_majorcorridor])) " & _
    "And ([Minor Corridor]=[Forms]![search]![cmb_minorcorridor] Or IsNull([Forms]![search]![cmb_minorcorridor])) " & _
    "And ([BayAreCommuterBenefitType]=[Forms]![search]![cmb_commuterbenefits] Or IsNull([Forms]![search]![cmb_commuterbenefits])) " & _
    "And ([Bike Parking]=[Forms]![search]![cmb_bikeparking] Or IsNull([Forms]![search]![cmb_bikeparking])) " & _
    "And ([Green Business Program]=[Forms]![search]![cmb_greenbusiness] Or IsNull([Forms]![search]![cmb_greenbusiness]))"

DoCmd.OpenForm "qrysearch", acNormal, "", criteria, , acNormal

That said, consider using named arguments instead of , , skipping optional parameters:

DoCmd.OpenForm "qrysearch", acNormal, "", criteria, WindowMode:=acWindowNormal

Note that the WindowMode parameter is expecting an AcWindowMode value, but you're giving it an AcFormView value - luckily both have an underlying value of 0 and could be simply omitted because they're both the default value, but still... using the wrong enum can lead to... interesting bugs.

So the OpenForm call can be simplified to:

DoCmd.OpenForm "qrysearch", WhereCondition:=criteria
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.