Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 899
  • Last Modified:

How select all records using VBA code if field are empty in the criteria form

I have the following very lengthy VBA code in an on print event of a report.  But there are two fields on the form frmSelectDateRangeAndMembers that if left blank by the user are to get all records for txtMonth and txtMemberName.

How would I change the code to accommodate this?

Here's the code:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
   
    'Group 1 Reporting Starts Here...
    'Column 1 (Goal) code...
    Me.txtListingsTakenGoal = Nz(DLookup("[ListingsCount]", "tblMemberGoals", "[TeamMember] = " & Forms!frmSelectDateRangeAndMembers!txtMemberName & " And [GoalMonth] = " & Forms!frmSelectDateRangeAndMembers!txtMonth & " And [GoalYear] = " & Forms!frmSelectDateRangeAndMembers!txtYear), 0)
    Me.txtListingsSold = Nz(DLookup("[ListingsSold]", "tblMemberGoals", "[TeamMember] = " & Forms!frmSelectDateRangeAndMembers!txtMemberName & " And [GoalMonth] = " & Forms!frmSelectDateRangeAndMembers!txtMonth & " And [GoalYear] = " & Forms!frmSelectDateRangeAndMembers!txtYear), 0)
    Me.txtBuyersSold = Nz(DLookup("[BuyersSold]", "tblMemberGoals", "[TeamMember] = " & Forms!frmSelectDateRangeAndMembers!txtMemberName & " And [GoalMonth] = " & Forms!frmSelectDateRangeAndMembers!txtMonth & " And [GoalYear] = " & Forms!frmSelectDateRangeAndMembers!txtYear), 0)
    'End of column 1 (Goal) code

    'Column 2 (Listings) code...
    Me.txtCountListings = Nz(DCount("[ID]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAndMembers!txtMemberName & " And [ListMonth] = " & Forms!frmSelectDateRangeAndMembers!txtMonth & " And [ListYear] = " & Forms!frmSelectDateRangeAndMembers!txtYear), 0)
    'End of column 2 (Listings) code

    'Column 3 (Pending) code...
    Me.txtListingsSoldPending = Nz(DCount("[ID]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAndMembers!txtMemberName & " And [PendingMonth] = " & Forms!frmSelectDateRangeAndMembers!txtMonth & " And [PendingYear] = " & Forms!frmSelectDateRangeAndMembers!txtYear & " And [Pending] = True"), 0)
    Me.txtBuyersSoldPending = Nz(DCount("[ID]", "tblTransactions", "[SellingAgent] = " & Forms!frmSelectDateRangeAndMembers!txtMemberName & " And [PendingMonth] = " & Forms!frmSelectDateRangeAndMembers!txtMonth & " And [PendingYear] = " & Forms!frmSelectDateRangeAndMembers!txtYear & " And [Pending] = True"), 0)
    'End of column 3 (Pending) code

    'Column 4 (Closed) code...
    Me.txtListingsSoldClosed = Nz(DCount("[ID]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAndMembers!txtMemberName & " And [PendingMonth] = " & Forms!frmSelectDateRangeAndMembers!txtMonth & " And [PendingYear] = " & Forms!frmSelectDateRangeAndMembers!txtYear & " And [Closed] = True"), 0)
    Me.txtBuyersSoldClosed = Nz(DCount("[ID]", "tblTransactions", "[SellingAgent] = " & Forms!frmSelectDateRangeAndMembers!txtMemberName & " And [PendingMonth] = " & Forms!frmSelectDateRangeAndMembers!txtMonth & " And [PendingYear] = " & Forms!frmSelectDateRangeAndMembers!txtYear & " And [Closed] = True"), 0)
    'End of column 4 (Closed) code
    'End of Group 1 Reporting


    'Group 2 Reporting Starts Here...
    'Column 1 (Goal) code...
    Me.txtCommBuyersSold = Nz(DLookup("[CommissionBuyersSold]", "tblMemberGoals", "[TeamMember] = " & Forms!frmSelectDateRangeAndMembers!txtMemberName & " And [GoalMonth] = " & Forms!frmSelectDateRangeAndMembers!txtMonth & " And [GoalYear] = " & Forms!frmSelectDateRangeAndMembers!txtYear), 0)
    Me.txtAgntLead = Nz(DLookup("[AgentLead]", "tblMemberGoals", "[TeamMember] = " & Forms!frmSelectDateRangeAndMembers!txtMemberName & " And [GoalMonth] = " & Forms!frmSelectDateRangeAndMembers!txtMonth & " And [GoalYear] = " & Forms!frmSelectDateRangeAndMembers!txtYear), 0)
    Me.txtCommSoldListingGoal = Nz(DLookup("[CommissionSoldListing]", "tblMemberGoals", "[TeamMember] = " & Forms!frmSelectDateRangeAndMembers!txtMemberName & " And [GoalMonth] = " & Forms!frmSelectDateRangeAndMembers!txtMonth & " And [GoalYear] = " & Forms!frmSelectDateRangeAndMembers!txtYear), 0)
    'End of column 1 (Goal) code


    'Column 3 (Pending) code...
    'Me.txtLeadFromEdPend = Nz(DSum("[Agent Sale $ Commission]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAndMembers!txtMemberName & " And [PendingMonth] = " & Forms!frmSelectDateRangeAndMembers!txtMonth & " And [PendingYear] = " & Forms!frmSelectDateRangeAndMembers!txtYear & " And [Pending] = True" And [LeadFrom] = "Ed"), 0)
    Me.txtLeadFromEdPend = Nz(DSum("[Agent Sale $ Commission]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAndMembers!txtMemberName & " And [PendingMonth] = " & Forms!frmSelectDateRangeAndMembers!txtMonth & " And [PendingYear] = " & Forms!frmSelectDateRangeAndMembers!txtYear & " And [Pending] = True And [LeadFrom] = 'Ed'"), 0)
    Me.txtAgentLeadPending = Nz(DSum("[Agent Sale $ Commission]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAndMembers!txtMemberName & " And [PendingMonth] = " & Forms!frmSelectDateRangeAndMembers!txtMonth & " And [PendingYear] = " & Forms!frmSelectDateRangeAndMembers!txtYear & " And [Pending] = True And [LeadFrom] = 'Agent'"), 0)
    Me.txtCommSoldListingPending = Nz(DSum("[Agent Sale $ Commission]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAndMembers!txtMemberName & " And [PendingMonth] = " & Forms!frmSelectDateRangeAndMembers!txtMonth & " And [PendingYear] = " & Forms!frmSelectDateRangeAndMembers!txtYear & " And [Pending] = True"), 0)
    'End of column 3 (Pending) code


    'Column 4 (Closed) code...
    Me.txtLeadFromEdClsd = Nz(DSum("[Agent Sale $ Commission]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAndMembers!txtMemberName & " And [PendingMonth] = " & Forms!frmSelectDateRangeAndMembers!txtMonth & " And [PendingYear] = " & Forms!frmSelectDateRangeAndMembers!txtYear & " And [Closed] = True And [LeadFrom] = 'Ed'"), 0)
    Me.txtLeadEdClosed = Nz(DSum("[Agent Sale $ Commission]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAndMembers!txtMemberName & " And [PendingMonth] = " & Forms!frmSelectDateRangeAndMembers!txtMonth & " And [PendingYear] = " & Forms!frmSelectDateRangeAndMembers!txtYear & " And [Closed] = True And [LeadFrom] = 'Agent'"), 0)
    Me.txtCommSoldListingClosed = Nz(DSum("[Agent Sale $ Commission]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAndMembers!txtMemberName & " And [PendingMonth] = " & Forms!frmSelectDateRangeAndMembers!txtMonth & " And [PendingYear] = " & Forms!frmSelectDateRangeAndMembers!txtYear & " And [Closed] = True"), 0)
    'End of column 4 (Closed) code
    'End of Group 1 Reporting

End Sub
0
SteveL13
Asked:
SteveL13
1 Solution
 
PatHartmanCommented:
Selection criteria is applied BEFORE the report opens.  It needs to be in your query.  See my response in this post for a solution.
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28307698.html

It is hard to read the code since it is not formatted well.  In the future, please use the Code tags to retain formatting.

If you want to modify this code, find the place where the argument is used and add an OR "Is Null" part to the expression.  I'll do one to get you going.

Me.txtListingsTakenGoal = Nz(DLookup("[ListingsCount]", "tblMemberGoals", "[TeamMember] = (" & Forms!frmSelectDateRangeAndMembers!txtMemberName & " OR frmSelectDateRangeAndMembers!txtMemberName Is Null) And [GoalMonth] = " & Forms!frmSelectDateRangeAndMembers!txtMonth & " And [GoalYear] = " & Forms!frmSelectDateRangeAndMembers!txtYear), 0)

Open in new window


The other option is to add If statements and use different criteria for the DLookup()'s based on presence or absence in the search fields.

If IsNull(frmSelectDateRangeAndMembers!txtMemberName) Then
    Me.txtListingsTakenGoal = Nz(DLookup("[ListingsCount]", "tblMemberGoals", "[TeamMember] = " & Forms!frmSelectDateRangeAndMembers!txtMemberName & " And [GoalMonth] = " & Forms!frmSelectDateRangeAndMembers!txtMonth & " And [GoalYear] = " & Forms!frmSelectDateRangeAndMembers!txtYear), 0)
Else
    Me.txtListingsTakenGoal = Nz(DLookup("[ListingsCount]", "tblMemberGoals", "[GoalMonth] = " & Forms!frmSelectDateRangeAndMembers!txtMonth & " And [GoalYear] = " & Forms!frmSelectDateRangeAndMembers!txtYear), 0)
End If

Open in new window

0
 
Helen FeddemaCommented:
If at all possible, it is better to do filtering and creation of calculated fields in a record source query, rather than in code on a report event.  It is much, much easier to test and debug.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now