Cascading ?

[dlburkins]

I am building form that will serve as somewhat of a search freature for the end users. First they will be selecting the Managers name, they they will be presented with a drop down list of all of the agents that reported to the manager. The problem is that since this list is accumulating, you might have employee "x" name show up 30 or more times underneath the agent name drop down list.

Here is the VBA code that I used:

Private Sub SectionManagerName_AfterUpdate()


On Error Resume Next
AgentName.RowSource = "Select ConversionMasterTBL.AgentName " & _
"FROM ConversionMasterTBL " & _
"WHERE ConversionMasterTBL.SectionManagerName = '" & SectionManagerName.Value & "' " & _
"ORDER BY ConversionMasterTBL.AgentName;"
End Sub

Also Attached is a screen shot of the issue at hand. As you can see, it shows the agents name repeadtly, but I only want it to display the name once.
-------------------------
Another question that I have is if you look at image2 you will see that there is a field that says PeriodEndDate. I would like for this field to read off of what is selected from the AgentName field. Is this possible? If someone could show me how to write the script i would appreciate it. I think the tricky part will be trying to figure out the calculated sum ($) of the agents and have that figure displayed in the AmountPaid field, since there will be multiple records for the agents for any given period end date.


Thanks

[dcrake]

Your combo box rowsource neds to be grouped by agent and sum the amount, but still including the where statement.

David

[dlburkins]

Your combo box rowsource neds to be grouped by agent and sum the amount, but still including the where statement.

David

Thanks for the reply. I am rather new to this. Would you be able to provide me with an example of what the code should look like?

[dlburkins]

Nevermind I just found out how to display only the agents name only once.

[bkelly]

I am a novice also. Please show us how you did it.
Then consider marking it solved so anyone can tell there is a solution included in the thread.

[dlburkins]

Can anyone help me with my second part of the question?

- I have been able to create combo boxes for that act as a mini search field/filter for the fields. I have it built where you select the SM and then you can select the desired agent under that SM. From then you then can select the Period End date for that particular agent. I wanted to know how can I make the Amount Paid field dynamic for it to display the values from the above criteria. The problem is that some agents have multiple "amountpaid" records for any given period so I would like it to display the sum from the period end dates even in the event there are multiple entries for periodenddate.

For example:

Record 1
AgentName: John Smith
AgentID:12345
PeriodEndDate: 3/31/2009
AmountPaid $25

Record 2
AgentName: John Smith
AgentID:12345
PeriodEndDate: 3/31/2009
AmountPaid $50

If I select:
SM Name: Manager A........John Smiths Manager
Agent Name: John Smith
PeriodEndDate: 3/31/09
AmountPaid: $75 (should be able to select this from the filter or have it auto display)..........This is when I am stuck. I want want for it to display the total sum of both records if I select PeriodEndDate 3/31/09...

The current VBA script that I have does not display any values in the amount paid field.

Private Sub PeriodEndDate_AfterUpdate()
On Error Resume Next
AmountPaid.RowSource = "Select ConversionMasterTBL.AmountPaid " & _
"FROM ConversionMasterTBL " & _
"WHERE ConversionMasterTBL.PeriodEndDate = '" & PeriodEndDate.Value & "' " & _
"ORDER BY ConversionMasterTBL.AmountPaid;"
End Sub


Any suggestions would be great.