CODEWITHSUNDEEP
vbams-access
Robert Furger
Robert Furger

Reputation: 53

How to break vba code into multiple lines?

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

Upvotes: 1

Views: 2223

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71157

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

Upvotes: 3

Related Questions