By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,494 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

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

kcdoell
100+
P: 230
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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub TxtBudGWP_BeforeUpdate(Cancel As Integer)
  2.  
  3. 'Set value of the text box to match the query...
  4.  
  5. 'Me.RecordSource = "GWP.tblbudget"
  6. 'Me.Filter = "QryBudget_Sum"
  7. 'Me.FilterOn = True
  8.  
  9. 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.
Apr 21 '08 #1
Share this Question
Share on Google+
7 Replies


kcdoell
100+
P: 230
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.
Apr 22 '08 #2

puppydogbuddy
Expert 100+
P: 1,923
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub YourInputTxtbox_AfterUpdate(Cancel As Integer)
  2. Dim budgetDetail As Currency
  3. Dim  budgetSum As Currency
  4.  
  5. 'Set value of the text box to match the query...
  6.  
  7. 'Me.RecordSource = "GWP.tblbudget"
  8. budgetSum = DSum(budgetDetail, "QryBudget_Sum")
  9. Me![TxtBudGWP] = budgetSum
  10.  
  11. End Sub
Apr 23 '08 #3

kcdoell
100+
P: 230
Puppydogbuddy:

I inputted the following:
Expand|Select|Wrap|Line Numbers
  1. Private Sub TxtBudGWP_AfterUpdate()
  2. Dim GWP As Currency
  3. Dim GWPSUM As Currency
  4.  
  5. 'Set value of the text box to match the query...
  6.  
  7.     GWPSUM = DSum(GWP, "QryBudget_Sum")
  8.     Me![TxtBudGWP] = GWPSUM
  9.  
  10. End Sub
  11.  
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:

Expand|Select|Wrap|Line Numbers
  1. DSum(GWP, "QryBudget_Sum")
  2.  
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.
Apr 23 '08 #4

kcdoell
100+
P: 230
Puppydogbuddy:

I inputted the following:
Expand|Select|Wrap|Line Numbers
  1. Private Sub TxtBudGWP_AfterUpdate()
  2. Dim GWP As Currency
  3. Dim GWPSUM As Currency
  4.  
  5. 'Set value of the text box to match the query...
  6.  
  7.     GWPSUM = DSum(GWP, "QryBudget_Sum")
  8.     Me![TxtBudGWP] = GWPSUM
  9.  
  10. End Sub
  11.  
But nothing happens............
I am getting closer I was missing quotes around GWP:

Expand|Select|Wrap|Line Numbers
  1. 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.
Apr 23 '08 #5

puppydogbuddy
Expert 100+
P: 1,923
I am getting closer I was missing quotes around GWP:

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Open(Cancel as Integer)
  2. TxtBudGWP_AfterUpdate
  3. 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.
Apr 23 '08 #6

puppydogbuddy
Expert 100+
P: 1,923
PS: If GWP is a variable, remove the quotes.....
Apr 23 '08 #7

kcdoell
100+
P: 230
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:

Expand|Select|Wrap|Line Numbers
  1. =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.
Apr 23 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.