Updating Multiple Text Boxes under one Event

[Lilfiger19]

Hi,

I am trying to figure out how to trigger the below VBA Code under one Change() Event once a selection is made from the only combobox on my form. Can anyone help me with this?

Private Sub cbxAssociate_Change()

Me.txtFIRJuly14.Value = DAvg("FIR_Perc", "tblFIRStats", "[Associate]= '" & Nz([cbxAssociate]) & "'AND [Month] = 'Jul-14'")
Me.txtFIRAugust14.Value = DAvg("FIR_Perc", "tblFIRStats", "[Associate]= '" & Nz([cbxAssociate]) & "'AND [Month] = 'Aug-14'")
Me.txtFIRSeptember14.Value = DAvg("FIR_Perc", "tblFIRStats", "[Associate]= '" & Nz([cbxAssociate]) & "'AND [Month] = 'Sep-14'")
Me.txtFIROctober14.Value = DAvg("FIR_Perc", "tblFIRStats", "[Associate]= '" & Nz([cbxAssociate]) & "'AND [Month] = 'Oct-14'")
Me.txtFIRNovember14.Value = DAvg("FIR_Perc", "tblFIRStats", "[Associate]= '" & Nz([cbxAssociate]) & "'AND [Month] = 'Nov-14'")
Me.txtFIRDecember14.Value = DAvg("FIR_Perc", "tblFIRStats", "[Associate]= '" & Nz([cbxAssociate]) & "'AND [Month] = 'Dec-14'")
                    
End Sub

 

[JHB]

And what problem do you've with this, (error message?)?

 

[Lilfiger19]

The thing is, I'm not getting one. However, when I load the form and then make a selection via the combo box, it isn't producing any output to the text boxes.

 

[Lilfiger19]

Also,

I was successful in getting the first text box to update. It is just the remaining ones that aren't producing the desired results

 

[JHB]

Then I think you do have any data in you table that fulfilled the criteria!
"[Associate]= '" & Nz([cbxAssociate]) & "'AND [Month] = 'Aug-14'"

 

[Lilfiger19]

Actually I thought the same thing at first but I went back and reviewed the data and I have data that should pull for each of those conditions. Am I wrong for putting all of those conditions under the one Change Event?

 

[JHB]

.. Am I wrong for putting all of those conditions under the one Change Event?

You can put as many as you want - it wouldn't cause any problem.
Could you post your database with some sample data, zip it?

 

[spikepl]

Change event is triggered for each character you input in the combo. Use AfterUpdate.

You also need a space before AND in all your criteria

 

[Galaxiom]

You also need a space before AND in all your criteria

Not really. SQL needs far fewer spaces than most developers presume.

If the statement is unambiguous without the space then it isn't required.

In the OP's case, the parenthesis following the Nz function arguments does the job of separating from the AND.

You really just need to keep the string-like sequences apart.

For example, when I write queries in SQL I prefer this format:

WHERE somefield=1 AND anotherfield=2

to

WHERE somefield = 1 AND anotherfield = 2

because the individual components of the conditions hang together better.