Passing value of DSum to text box after combo box update

[Lilfiger19]

Hi,

I have a combo box that has a list of names in it. When I select a name, I am wanting to DSum a column in one of my tables based off of the name in the combobox and pass the value to a specific text box. The below code has worked in another database that I have set up but it is for 2010. i am currently writing this one in 2007. Can anyone please help me understand what I am doing wrong?

Private Sub cbxTM_AfterUpdate()

Me.txtCases = DSum("([Cases LY Var])", "tbl_Productivity", "[TM_and_ID]= '" & Nz([cbxTM]")

End Sub

 

[informer]

Hi

Try with

• "[TM_and_ID]= '" & [cbxTM] & "'" if TM_and_ID is a string i.e. wrap combobox value with single quote
• "[TM_and_ID]= " & [cbxTM] if TM_and_ID is a numeric i.e. without single quote

 

[arnelgp]

change this:

Me.txtCases = DSum("([Cases LY Var])", "tbl_Productivity", "[TM_and_ID]= '" & Nz([cbxTM]")


to:


Me.txtCases = DSum("([Cases LY Var])", "tbl_Productivity", "[TM_and_ID]= " & Nz([cbxTM], 0))

 

[informer]

Hi,


You don't need Nz command see microsoft support : https://msdn.microsoft.com/en-us/library/office/aa172193(v=office.11).aspx

But add a test on cbx selected value

 

[arnelgp]

sorry page not found.
why not, if there is no value on the combo, it returns null and you cannot use null in comparison.

 

[informer]

I find more efficient to inform users that no value is selected in combobox and the process is cancelled

The link here https://msdn.microsoft.com/en-us/library/office/aa172193(v=office.11).aspx