Setting a Textbox Control Source with VBA - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Setting a Textbox Control Source with VBA

Gary Becker

11 months ago

I have a textbox called LaborJTD on a form. It's controlsource is a Dsum. I added a button on the form and would like to use the Click event to change the ControlSource of that textbox. Here is the code that I thought should work:

Private Sub AllLaborBtn_Click()

LaborJTD.ControlSource = DSum("Amount", "JCTransactions", "(Transaction_Type= ""PR Cost"" OR Transaction_Type= ""JC Cost"") AND CJobNumber = """ & [JobNumber] & """ AND Category = [Type] ")

End Sub

I get a #Name? error as if it doesn't know what or where the textbox is.

	Kevin Robertson @Reply 11 months ago
The Control Source (when set in VBA) should be in quotes and start with an equals sign.

	Gary Becker OP @Reply 11 months ago
Like how? This doesn't work: LaborJTD.ControlSource = "=DSum("Amount", "JCTransa ...." It gives a compile error 'Expected: end of statement' I can understand the logic--actually just trying to place a text string in there for the control source. Just can't figure out exactly how to make it work.

	Gary Becker OP @Reply 11 months ago
I figured it out. I has to be like this: LaborJTD.ControlSource = "=" & DSum("Amount", "JCTransactions", "(Transact ...... Thanks for your help. So many different ways to put the Legos together. Urrrr

	Kevin Robertson @Reply 11 months ago
Here is an example: `OrderTotal.ControlSource = "=Nz(DSum(""ExtPrice"", ""OrderDetailQ"", ""CustomerID="" & CustomerID),0)"` This gives the same result: `OrderTotal = Nz(DSum("ExtPrice", "OrderDetailQ", "CustomerID=" & CustomerID), 0)`

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View