Autopopulating Text Boxes (Help!)

[cms2012]

Ok here is what I am doing. I have two comboboxes: one is cmbDate and the other is cmbsalesperson. When I choose a date from cmbDate it gives me the sales people who sold in that month of that year in cmbsalesperson. When I choose the saleperson in cmbsalesperson it autopopulates two text boxes with their total number of quotes and the amount of money they made (with me so far?)

Now, I have two more text boxes that I need to fill with salesperson's data from that month in the previous year. If there is anyone that can help me with this I would be greatly appreciative.

Thanks!

 

[jzwp22]

For the current month/year quotes/sales boxes, how are they being populated now?


You can use a domain aggregate function such as DSum() or DLookup() to bring in the prior month/year data or you could modify the query used for the combo box and bring it in there and reference the applicable field/columns in the previous month/year textboxes

=comboboxname.column(x)

 

[cms2012]

This is the code that I am using to populate the text boxes:
Private Sub cmbsalesperson_AfterUpdate()
Me.txtwinstotal_month = Me.cmbsalesperson.Column(1)
Me.txtwins_month = Me.cmbsalesperson.Column(2)
End Sub

 

[jzwp22]

OK, what is the row source of the combo box?

You could probably modify it to include the previous year's info and then just do the same thing with the code.

In order to modify the comb box's row source you will probably need a subquery or two.

Could you provide more info on how the pertinent tables are structured and what type of data is captured (i.e. is the data already summarized or do you have to add up data from records of individual transactions)?

 

[cms2012]

The data is already summarized in a query with the number of quotes that the person had for a certain month in all the years that I pulled. What kind of subquery do you think would work.

Sorry if this is not a lot of information. I have some knowledge of access but not a lot.

 

[jzwp22]

OK, without any more information this is all a guess

I assume the combo box's row source looks something like this

SELECT salesperson, quotecount, salessum
FROM sumquery

Assuming that all of the data is in your summing query & you have the data summarized by year and month and your combo box is filterd such that it only has the current year data, you would alter the row source along these lines:

SELECT salesperson, quotecount, salessum, salesyear, salesmonth, (SELECT Q1.quotecount FROM sumquery as Q1 WHERE Q1.salesperson=sumquery.salesperson AND Q1.salesmonth=sumquery.salesmonth AND Q1.salesyear=dateadd("yyyy",-1,date()) as LastYearPeriodQuoteCount
FROM sumquery

 

[cms2012]

Here is what the query looks like for the cmbsalesperson combobox:

SELECT METRICS.SALES_PERSON, Sum(METRICS.TOTAL_WINS) AS SumOfTOTAL_WINS, Format(Sum(METRICS.MONEY_TOTAL_WINS),"Currency") AS SumOfMONEY_TOTAL_WINS, Sum(METRICS.TOTAL_QUOTES) AS SumOfTOTAL_QUOTES, Format(Sum(METRICS.MONEY_TOTAL_QUOTES),"Currency") AS SumOfMONEY_TOTAL_QUOTES
FROM METRICS
WHERE (((METRICS.Created)=[Forms]![Sales Metrics].[cmbdate]))
GROUP BY METRICS.SALES_PERSON, METRICS.SALES_PERSON
ORDER BY METRICS.SALES_PERSON;

 

[jzwp22]

The query you show is an aggregate query, so the subquery will cause problems. Is that aggregate query saved under a query name in the queries section in the navigation pane?

 

[cms2012]

No, when I created the combo box I used the query that I had saved on the navigation pane as the values that I wanted and modified the query to do what I wanted. Do I need to save it on navigation pane?

 

[jzwp22]

I'm not sure I understand how the query works correctly since the metrics.created field is not part of the GROUP BY clause, nor is it in a HAVING clause which is typical of aggregate queries.

Is metrics.created a date field? or does it designate just the month & year?

 

[cms2012]

The only reason that it is in this query is so that when I choose the date in cmbDate, only the people who sold in that particluar month of that year are showing in cmbsalesperson

 

[jzwp22]

I understand that, but to do the subqueries or to even use the domain aggregate functions, we need to use that date to get the previous year/month value as well. So how is that field structured? Is it a date/time field or does it just represent the year & month in some fashion?

 

[cms2012]

It is a Date/Time field

 

[jzwp22]

Is the value in the date field always the last day of the month or can it be any date?

 

[cms2012]

It can be any day because the date is when the quote is created

 

[jzwp22]

cmbDate it gives me the sales people who sold in that month of that year in cmbsalesperson. When I choose the saleperson in cmbsalesperson it autopopulates two text boxes with their total number of quotes and the amount of money they made (with me so far?)

Now, I have two more text boxes that I need to fill with salesperson's data from that month in the previous year

I'm missing something here. You say above that the query gives you what the person sold in that month but based on the SQL text of your query, it is not summing the entire month but only the date you select. Do you want the additional two textboxes to show the same date (not month) from the previous year? Please explain in more detail.

 

[cms2012]

I probably should have explained this earlier but this query is the combination of two other queries. One query pulls the data on the total number of quotes a salesperson made and the other is the total number of wins the person has. The two queries are left joined on the salesperson and created fields. When I created the combo box, I took this information and applied it as the values that I wanted and then modified the query to do what I wanted.

I'm sorry for all the trouble this is.

 

[jzwp22]

Rather than trying to mess with the query on which the combo box is based, it might be less hassle to go the domain aggregate approach. If you have the sum from the previous period some where in another query and that information is summarized by month & year, you can just go out and get it with the DLookup() function

If doing so in code:

me.textboxname=DLookup("sumfieldname","queryname", "salesperson='" & [me.salespersoncombobox] & "' AND queryname.monthfield= " & month(me.datecombo) & " AND queryname.yearfield=" & dateadd("yyyy",-1,me.datecombo)

You can also do it within the control source of the text box itself. You typically just reference the control names. The expression builder should help you on that.

 

[cms2012]

when referencing the comboboxes do i need to do Forms![Formname].comboboxname or can i just do the combobox?

 

[jzwp22]

If the control from which the code executes is on the same form as the controls you are populating then you can use the shorthand notation me.controlname