Update recordsource of one field on a form

[TinaCa]

I have a field called Amount to Bill on my form where the user creates the bi-monthly invoices. When the user selects the 1 or 15 (the bill date of the company) it should update a field on the form called amount to be billed. On open the recordsource of the field is null. When the user selects either 1 or 15 from a combo box on the form the system is to set the recordsource to a sql statement and then refresh the field. The refresh and sql statement are working - sort of. the date field from the recordsource is showing in the field and not the sum field. (also (this date is just so I can get a sum this statement will be "is null" in the final code) Can you help get the sum field to display on the form and not the the date .



Here's the code.

Dim TtlBl As String
Dim BillDt As Integer

BillDt = Me.cboPrntInv


TtlBl = "SELECT tblCompanyExtras.DateBilled, CompanyInformation.CompanyBillingDate, " _
& "Sum(([ExtraCost]*[ExtrasQty])+[CompanyContractAmt]) AS ExtraTotal " _
& "FROM CompanyInformation LEFT JOIN tblCompanyExtras ON " _
& "CompanyInformation.CompanyAccountNo = tblCompanyExtras.CompanyId " _
& "GROUP BY tblCompanyExtras.DateBilled, CompanyInformation.CompanyBillingDate " _
& "HAVING (((tblCompanyExtras.DateBilled)=#9/14/2011#) AND " _
& "((CompanyInformation.CompanyBillingDate)= " & BillDt & "));"

Me.txtTotBld.RowSource = TtlBl
DoCmd.RefreshRecord

[June7]

Fields and controls don't have a RecordSource. RecordSource is a property of forms and reports.

Controls have a ControlSource. However, your code shows setting the RowSource property of a combo or list box.

The combobox is probably showing the date because it is the first field in the RowSource and the BoundColumn is probably set as 1. If you want the Sum to show set BoundColumn to 3 or rearrange the fields in the SQL or remove the two first fields.

Is the ControlSource set or is this combobox unbound?