Using a query to populate an unbound text box on a form

[kcdoell]

Hello:

I have a continous form that displays various forecasting records that an End user can add or edit the records (this feeds off of a query that I created called "ReQryForecast"). On that form, in the top corner, I placed 4 unbound text boxes that displays summary information. On one of them, "TxtBudGWP" I want to display the specific budget number for that particular month. This information is on a separate table I call tblbudget. I created a query called "QryBudget_Sum" that gives me the number I am looking for. Now I just want to populate that value into "TxtBudGWP" when the form opens.

I tried the following code in the before update event of TxtBudGWP:

Private Sub TxtBudGWP_BeforeUpdate(Cancel As Integer)
 
'Set value of the text box to match the query...
 
'Me.RecordSource = "GWP.tblbudget"
'Me.Filter = "QryBudget_Sum"
'Me.FilterOn = True
 
End Sub

But nothing happen when the form loaded. I am not too sure if this is the correct method of doing this.

Any idea?

Thanks,

Keith.

[kcdoell]

Good Morning:

I know this should be a no-brainer but I am stumped. I have done it with a listbox before where I point the row source to a query, but I believe I can do it with a text box as well but not using the same method.

Any ideas would be great..

Thanks,

Keith.

[puppydogbuddy]

Good Morning:

I know this should be a no-brainer but I am stumped. I have done it with a listbox before where I point the row source to a query, but I believe I can do it with a text box as well but not using the same method.

Any ideas would be great..

Thanks,

Keith.

Keith,
You can't directly use a query as a control source of a textbox; but you can accomplish the same thing using a domain aggregate function (DSum, etc). Secondly, you use the filter property the way you used it.,,,the filter property is used to filter records, not a control. Thirdly, the before and after update events are triggered by inputting changes to data, not by updates done in via programmed code. With the above in mind, below is one example of how you can pass the total to your textbox. Replace variable and object names used with their actual names in your application.

Private Sub YourInputTxtbox_AfterUpdate(Cancel As Integer)
Dim budgetDetail As Currency
Dim  budgetSum As Currency
 
'Set value of the text box to match the query...
 
'Me.RecordSource = "GWP.tblbudget"
budgetSum = DSum(budgetDetail, "QryBudget_Sum")
Me![TxtBudGWP] = budgetSum
 
End Sub

[kcdoell]

Puppydogbuddy:

I inputted the following:

Private Sub TxtBudGWP_AfterUpdate()
Dim GWP As Currency
Dim GWPSUM As Currency
 
'Set value of the text box to match the query...
 
    GWPSUM = DSum(GWP, "QryBudget_Sum")
    Me![TxtBudGWP] = GWPSUM
 
End Sub
 

But nothing happens when I open the form in TxtBudGWP. Even when I key in and out of it, it will populate a zero if I plugged in a number.

So the way I understand this, is even if I left out the above VB and just went into the control source of TxtBudGWP and placed in the following code:

DSum(GWP, "QryBudget_Sum")
 

That still would not do the trick... I actually tryed this and got a "#Name?" in the text box field.

My table = tblbudget
Field name on the table and query = GWP
Query Name = QryBudget_Sum
Unbound text box on form = TxtBudGWP

Any additional thoughts on where I am going wrong?

Thanks,

Keith.

[kcdoell]

Puppydogbuddy:

I inputted the following:

Private Sub TxtBudGWP_AfterUpdate()
Dim GWP As Currency
Dim GWPSUM As Currency
 
'Set value of the text box to match the query...
 
    GWPSUM = DSum(GWP, "QryBudget_Sum")
    Me![TxtBudGWP] = GWPSUM
 
End Sub
 

But nothing happens............

I am getting closer I was missing quotes around GWP:

GWPSUM = DSum("GWP", "QryBudget_Sum")

Now when I click in the box and out the correct number populates. But I still want it to load when the form opens or when the form is requery...

Keith.

[puppydogbuddy]

I am getting closer I was missing quotes around GWP:

GWPSUM = DSum("GWP", "QryBudget_Sum")

Now when I click in the box and out the correct number populates. But I still want it to load when the form opens or when the form is requery...

Keith.

Keith,
Call the AfterUpdate code in the form open event as shown:

Private Sub Form_Open(Cancel as Integer)
TxtBudGWP_AfterUpdate
End Sub

As previously stated the update events don't fire if the update is done by code and not by input....so you have to call it directly by invoking the procedure name.

[puppydogbuddy]

PS: If GWP is a variable, remove the quotes.....

[kcdoell]

PS: If GWP is a variable, remove the quotes.....

Puppydogbuddy:

It looks like I solved it with your help and some reading.

I cleared out the vb code of my unbound text box's event and place the following code into the control source:

=DSum("[GWP]","QryBudget_Sum")

In the end a simple solution but this seems to have done the trick.

Thanks for the help and insight.

Keith.