SteveL13
asked on
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 frmSelectDateRangeAndMembe rs 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!frmSelectDateRangeAn dMembers!t xtMemberNa me & " And [GoalMonth] = " & Forms!frmSelectDateRangeAn dMembers!t xtMonth & " And [GoalYear] = " & Forms!frmSelectDateRangeAn dMembers!t xtYear), 0)
Me.txtListingsSold = Nz(DLookup("[ListingsSold] ", "tblMemberGoals", "[TeamMember] = " & Forms!frmSelectDateRangeAn dMembers!t xtMemberNa me & " And [GoalMonth] = " & Forms!frmSelectDateRangeAn dMembers!t xtMonth & " And [GoalYear] = " & Forms!frmSelectDateRangeAn dMembers!t xtYear), 0)
Me.txtBuyersSold = Nz(DLookup("[BuyersSold]", "tblMemberGoals", "[TeamMember] = " & Forms!frmSelectDateRangeAn dMembers!t xtMemberNa me & " And [GoalMonth] = " & Forms!frmSelectDateRangeAn dMembers!t xtMonth & " And [GoalYear] = " & Forms!frmSelectDateRangeAn dMembers!t xtYear), 0)
'End of column 1 (Goal) code
'Column 2 (Listings) code...
Me.txtCountListings = Nz(DCount("[ID]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAn dMembers!t xtMemberNa me & " And [ListMonth] = " & Forms!frmSelectDateRangeAn dMembers!t xtMonth & " And [ListYear] = " & Forms!frmSelectDateRangeAn dMembers!t xtYear), 0)
'End of column 2 (Listings) code
'Column 3 (Pending) code...
Me.txtListingsSoldPending = Nz(DCount("[ID]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAn dMembers!t xtMemberNa me & " And [PendingMonth] = " & Forms!frmSelectDateRangeAn dMembers!t xtMonth & " And [PendingYear] = " & Forms!frmSelectDateRangeAn dMembers!t xtYear & " And [Pending] = True"), 0)
Me.txtBuyersSoldPending = Nz(DCount("[ID]", "tblTransactions", "[SellingAgent] = " & Forms!frmSelectDateRangeAn dMembers!t xtMemberNa me & " And [PendingMonth] = " & Forms!frmSelectDateRangeAn dMembers!t xtMonth & " And [PendingYear] = " & Forms!frmSelectDateRangeAn dMembers!t xtYear & " And [Pending] = True"), 0)
'End of column 3 (Pending) code
'Column 4 (Closed) code...
Me.txtListingsSoldClosed = Nz(DCount("[ID]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAn dMembers!t xtMemberNa me & " And [PendingMonth] = " & Forms!frmSelectDateRangeAn dMembers!t xtMonth & " And [PendingYear] = " & Forms!frmSelectDateRangeAn dMembers!t xtYear & " And [Closed] = True"), 0)
Me.txtBuyersSoldClosed = Nz(DCount("[ID]", "tblTransactions", "[SellingAgent] = " & Forms!frmSelectDateRangeAn dMembers!t xtMemberNa me & " And [PendingMonth] = " & Forms!frmSelectDateRangeAn dMembers!t xtMonth & " And [PendingYear] = " & Forms!frmSelectDateRangeAn dMembers!t xtYear & " 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("[CommissionBuy ersSold]", "tblMemberGoals", "[TeamMember] = " & Forms!frmSelectDateRangeAn dMembers!t xtMemberNa me & " And [GoalMonth] = " & Forms!frmSelectDateRangeAn dMembers!t xtMonth & " And [GoalYear] = " & Forms!frmSelectDateRangeAn dMembers!t xtYear), 0)
Me.txtAgntLead = Nz(DLookup("[AgentLead]", "tblMemberGoals", "[TeamMember] = " & Forms!frmSelectDateRangeAn dMembers!t xtMemberNa me & " And [GoalMonth] = " & Forms!frmSelectDateRangeAn dMembers!t xtMonth & " And [GoalYear] = " & Forms!frmSelectDateRangeAn dMembers!t xtYear), 0)
Me.txtCommSoldListingGoal = Nz(DLookup("[CommissionSol dListing]" , "tblMemberGoals", "[TeamMember] = " & Forms!frmSelectDateRangeAn dMembers!t xtMemberNa me & " And [GoalMonth] = " & Forms!frmSelectDateRangeAn dMembers!t xtMonth & " And [GoalYear] = " & Forms!frmSelectDateRangeAn dMembers!t xtYear), 0)
'End of column 1 (Goal) code
'Column 3 (Pending) code...
'Me.txtLeadFromEdPend = Nz(DSum("[Agent Sale $ Commission]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAn dMembers!t xtMemberNa me & " And [PendingMonth] = " & Forms!frmSelectDateRangeAn dMembers!t xtMonth & " And [PendingYear] = " & Forms!frmSelectDateRangeAn dMembers!t xtYear & " And [Pending] = True" And [LeadFrom] = "Ed"), 0)
Me.txtLeadFromEdPend = Nz(DSum("[Agent Sale $ Commission]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAn dMembers!t xtMemberNa me & " And [PendingMonth] = " & Forms!frmSelectDateRangeAn dMembers!t xtMonth & " And [PendingYear] = " & Forms!frmSelectDateRangeAn dMembers!t xtYear & " And [Pending] = True And [LeadFrom] = 'Ed'"), 0)
Me.txtAgentLeadPending = Nz(DSum("[Agent Sale $ Commission]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAn dMembers!t xtMemberNa me & " And [PendingMonth] = " & Forms!frmSelectDateRangeAn dMembers!t xtMonth & " And [PendingYear] = " & Forms!frmSelectDateRangeAn dMembers!t xtYear & " And [Pending] = True And [LeadFrom] = 'Agent'"), 0)
Me.txtCommSoldListingPendi ng = Nz(DSum("[Agent Sale $ Commission]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAn dMembers!t xtMemberNa me & " And [PendingMonth] = " & Forms!frmSelectDateRangeAn dMembers!t xtMonth & " And [PendingYear] = " & Forms!frmSelectDateRangeAn dMembers!t xtYear & " And [Pending] = True"), 0)
'End of column 3 (Pending) code
'Column 4 (Closed) code...
Me.txtLeadFromEdClsd = Nz(DSum("[Agent Sale $ Commission]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAn dMembers!t xtMemberNa me & " And [PendingMonth] = " & Forms!frmSelectDateRangeAn dMembers!t xtMonth & " And [PendingYear] = " & Forms!frmSelectDateRangeAn dMembers!t xtYear & " And [Closed] = True And [LeadFrom] = 'Ed'"), 0)
Me.txtLeadEdClosed = Nz(DSum("[Agent Sale $ Commission]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAn dMembers!t xtMemberNa me & " And [PendingMonth] = " & Forms!frmSelectDateRangeAn dMembers!t xtMonth & " And [PendingYear] = " & Forms!frmSelectDateRangeAn dMembers!t xtYear & " And [Closed] = True And [LeadFrom] = 'Agent'"), 0)
Me.txtCommSoldListingClose d = Nz(DSum("[Agent Sale $ Commission]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAn dMembers!t xtMemberNa me & " And [PendingMonth] = " & Forms!frmSelectDateRangeAn dMembers!t xtMonth & " And [PendingYear] = " & Forms!frmSelectDateRangeAn dMembers!t xtYear & " And [Closed] = True"), 0)
'End of column 4 (Closed) code
'End of Group 1 Reporting
End Sub
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
Me.txtListingsSold = Nz(DLookup("[ListingsSold]
Me.txtBuyersSold = Nz(DLookup("[BuyersSold]",
'End of column 1 (Goal) code
'Column 2 (Listings) code...
Me.txtCountListings = Nz(DCount("[ID]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAn
'End of column 2 (Listings) code
'Column 3 (Pending) code...
Me.txtListingsSoldPending = Nz(DCount("[ID]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAn
Me.txtBuyersSoldPending = Nz(DCount("[ID]", "tblTransactions", "[SellingAgent] = " & Forms!frmSelectDateRangeAn
'End of column 3 (Pending) code
'Column 4 (Closed) code...
Me.txtListingsSoldClosed = Nz(DCount("[ID]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAn
Me.txtBuyersSoldClosed = Nz(DCount("[ID]", "tblTransactions", "[SellingAgent] = " & Forms!frmSelectDateRangeAn
'End of column 4 (Closed) code
'End of Group 1 Reporting
'Group 2 Reporting Starts Here...
'Column 1 (Goal) code...
Me.txtCommBuyersSold = Nz(DLookup("[CommissionBuy
Me.txtAgntLead = Nz(DLookup("[AgentLead]", "tblMemberGoals", "[TeamMember] = " & Forms!frmSelectDateRangeAn
Me.txtCommSoldListingGoal = Nz(DLookup("[CommissionSol
'End of column 1 (Goal) code
'Column 3 (Pending) code...
'Me.txtLeadFromEdPend = Nz(DSum("[Agent Sale $ Commission]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAn
Me.txtLeadFromEdPend = Nz(DSum("[Agent Sale $ Commission]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAn
Me.txtAgentLeadPending = Nz(DSum("[Agent Sale $ Commission]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAn
Me.txtCommSoldListingPendi
'End of column 3 (Pending) code
'Column 4 (Closed) code...
Me.txtLeadFromEdClsd = Nz(DSum("[Agent Sale $ Commission]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAn
Me.txtLeadEdClosed = Nz(DSum("[Agent Sale $ Commission]", "tblTransactions", "[ListingAgent] = " & Forms!frmSelectDateRangeAn
Me.txtCommSoldListingClose
'End of column 4 (Closed) code
'End of Group 1 Reporting
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.