Autopopulating Text Boxes (Help!)

[cms2012]

Do I put this code in when a certain event happens?

 

[jzwp22]

It should work in your existing code (after update event of the sales person combo box).

 

[cms2012]

It is giving me a syntax error and hilighting this: Private Sub cmbsalesperson_AfterUpdate() in yellow

 

[jzwp22]

Please provide your actual code.

 

[cms2012]

Sorry, I found the error but nothing is coming up in the textbox. Here is the code:

Me.txtwinstotal_pym = DLookup("TOTAL_WINS", "METRICS", "SALES_PERSON="" &[me.cmbsalesperson] & "" AND METRICS.MONTH=" & Month(Me.cmbMonth) & " AND METRICS.YEAR=" & DateAdd("yyyy", -1, Me.cmbYear))

 

[jzwp22]

Me.txtwinstotal_pym = DLookup("TOTAL_WINS", "METRICS", "SALES_PERSON="" &[me.cmbsalesperson] & "" AND METRICS.[MONTH]=" & Month(Me.cmbMonth) & " AND METRICS.[YEAR] =" & DateAdd("yyyy", -1, Me.cmbYear))

You have to have a single quote to delimit text values. Also month are year are reserved words in Access (and should not be used as field or table names) so you have to enclose those field names in square brackets (shown in purple).

Could you also provide the SQL text of the METRICS query?

 

[cms2012]

Ok, I fixed the code like you said:

Me.txtwinstotal_pym = DLookup("TOTAL_WINS", "METRICS", "SALES_PERSON='" & [me.cmbsalesperson] & "' AND METRICS.[MONTH]=" & Month(Me.cmbMonth) & " AND METRICS.[YEAR]=" & DateAdd("yyyy", -1, Me.cmbMonth))

But I got a run-time error '2465'

Microsoft cannont find the field 'l" referred to in your expression.

Idk what that means?

 

[cms2012]

Here is the SQL for the METRICS query:

SELECT [TOTAL QUOTES].SALES_PERSON, Sum([TOTAL WINS].TOTAL_WINS) AS TOTAL_WINS, Sum([TOTAL QUOTES].TOTAL_QUOTES) AS TOTAL_QUOTES, Sum([TOTAL WINS].MONEY_TOTAL_WINS) AS SumOfMONEY_TOTAL_WINS, Sum([TOTAL QUOTES].MONEY_TOTAL_QUOTES) AS SumOfMONEY_TOTAL_QUOTES, [TOTAL QUOTES].Month, [TOTAL QUOTES].Year
FROM [TOTAL QUOTES] LEFT JOIN [TOTAL WINS] ON ([TOTAL QUOTES].Year=[TOTAL WINS].Year) AND ([TOTAL QUOTES].Month=[TOTAL WINS].Month) AND ([TOTAL QUOTES].SALES_PERSON=[TOTAL WINS].SALES_PERSON)
GROUP BY [TOTAL QUOTES].SALES_PERSON, [TOTAL QUOTES].Month, [TOTAL QUOTES].Year;

 

[jzwp22]

Me.txtwinstotal_pym = DLookup("TOTAL_WINS", "METRICS", "SALES_PERSON='" & [me.cmbsalesperson] & "' AND METRICS.[MONTH]=" & Month(Me.cmbMonth) & " AND METRICS.[YEAR]=" & DateAdd("yyyy", -1, Me.cmbMonth))


What type of data is in the combo box called cmbMonth? Can you provide an example of that data?

 

[cms2012]

I had to alter the metrics query because I had the Created field withe the dates like 01/2009. So I broke up the fields to where it is Year like 2009 and month 01, 02.
I then I got rid of the Created combobox and put a year and month combobox and used the year to get the months only in the year I want so the form is a little neater. I didn't think it would affect the code that much.

 

[jzwp22]

You have to reference the Year combo box and since it is not a date, the dateadd() function is no longer applicable.


Me.txtwinstotal_pym = DLookup("TOTAL_WINS", "METRICS", "SALES_PERSON='" & [me.cmbsalesperson] & "' AND METRICS.[MONTH]=" & Month(Me.cmbMonth) & " AND METRICS.[YEAR]=" & me.cmbYear-1)

 

[cms2012]

Its giving me a syntax error for some reason

Me.txtwinstotal_pym = DLookup("TOTAL_WINS", "METRICS", "SALES_PERSON='" & [me.cmbsalesperson] & "' AND METRICS.[MONTH]=" & Month(Me.cmbMonth) & " AND METRICS.[YEAR]=" & Me.cmbYear - 1))

 

[jzwp22]

You have to get rid of the month() function:

Me.txtwinstotal_pym = DLookup("TOTAL_WINS", "METRICS", "SALES_PERSON='" & [me.cmbsalesperson] & "' AND METRICS.[MONTH]=" & Me.cmbMonth & " AND METRICS.[YEAR]=" & Me.cmbYear - 1))

 

[cms2012]

It is giving me the run-time error '2465' again

Me.txtwinstotal_pym = DLookup("TOTAL_WINS", "METRICS", "SALES_PERSON='" & [me.cmbsalesperson] & "' AND METRICS.[MONTH]=" & Me.cmbMonth & " AND METRICS.[YEAR]=" & Me.cmbYear - 1)

 

[jzwp22]

How does the month appear in the query? 01, 02 or 1, 2 etc.? How does it appear in the month combo box?

 

[cms2012]

In both the query and the combo box it appears 01, 02, ... etc.

 

[jzwp22]

That implies a text value not numeric, so you have to delimit the value with single quotes.

 

[cms2012]

Idk if I did this right cause it is giving me a the same error:

Me.txtwinstotal_pym = DLookup("TOTAL_WINS", "METRICS", "SALES_PERSON='" & [me.cmbsalesperson] & "' AND METRICS.[MONTH]='" & Me.cmbMonth & "' AND METRICS.[YEAR]=" & Me.cmbYear - 1)

 

[jzwp22]

It looks OK. Can you zip & post a stripped down copy of the database with just some sample data (all sensitive data removed/altered)?

 

[cms2012]

Notice anything that can be done to it?