Query results to a form textbox. 1

[jminn0311]

How can I get the results from a sum field of query to display in a textbox on a form that is linked to a separate table. Thanks in advance.

Jeff

 

[THWatson]

Jeff
Have you tried DLookup?

Tom

 

[jminn0311]

Yes dlookup works but I need the query to include the information from the current record also. What is the best way to do this? Thanks for your help.

Jeff

 

[THWatson]

Jeff
I'm not sure I exactly understand what you are trying to do.

From your description, I assume this is the situation...
You have a form based on TableA. Your DLookup control looks up information from a query that is based on TableB. You want the DLookup control to include information from the record currently showing in the form.

Are TableA and TableB somehow related?

What information from your current record needs to show along with the DLookup information?

Tom

 

[jminn0311]

The form is based on tableA. The dlookup control finds information from a query of tableA. It sums the total of a field in tableA. I have a button that calculates a volume from information on the form and inserts that volume into a field on the current record of the form. I need the dlookup control to include the current volume calculation after the button is clicked. Sorry I am not doing a very good job of expaining this.

Jeff

 

[THWatson]

Jeff
I see. So both your form and query are based on TableA. From your original post, I thought that the form was linked to a separate table.

If both the form and query are based on TableA, then you shouldn't have to use DLookup, as it would be an unnecessary use of resources.

If the query is based on TableA, have you tried building your form from that query, which has all the data already there?

But that aside, is this what you are trying to do?
The form calculates a volume for the current record (let's say that calculation is 100). Then it adds that volume to former volume total (let's say that was 400), and you want the text box to show the cumulative value of 500.

Before I suggest further, let me know if this is the correct scenario.

Tom

 

[ponderdj]

TH, I think I'm having the problem you thought jminn was having. I have textboxes that enter data into tableA, but want them to be populated with data from tableB/tableC on open or after record save. I have a query that provides necessary info from tableB/tableC, how do I use DLookup to populate those, and still have them enter into tableA?

 

[THWatson]

The field you want entered into TableA has to have its Control Source set to that field. If you don't leave it as such, it won't populate. So you have to set the value that goes into that field through an AfterUpdate event - generally from another control on the form.

For example...on the AfterUpdate event for control...
Me.FieldThatGoesIntoTableA = Me.ControlThatDerivesTheValue

Say that you have 3 fields in the table - FirstName, LastName and FullName - and the FullName is derived from concatenating the FirstName and the LastName. And the LastName is filled in first, and then the FirstName is filled in second. On the AfterUpdate event for the FirstName control, you put...
Me.FullName = Me.FirstName & " " & Me.LastName

Hope that example helps.

Tom

 

[jminn0311]

The query is based off of tableA but it only queries a couple of fields to get the sum of volume for a given month.
The form cannot use the query as the control source. Your explanation of what I want it to do is dead on. I just need some help on how to do it. Thanks again.

 

[ponderdj]

Kind of get the idea, but that's not what I want to happen.

1. A listbox is populated from a query of tableB/tableC. (This is working)

2. Textboxes are empty by default, enter info into tableA when user types into them, clicks save record.
(This is working)

3. Instead of user manually entering info into the textboxes that feed tableA, I want to take the value from column1 of listbox, put it into textbox1, take value of column2 of listbox, put it into textbox2, and so on when the user clicks on that row in the listbox. Then when user clicks save, the values of the textboxes are entered into tableA.
(Right now, when I click on row in listbox that is populated from tables B and C, it looks for matching record in table A to pull into the textboxes, which doesn't exist. I want to take values in list box, put them into textboxes, then save those into tableA.

So, listbox is populated from query of tableB/tableC, textboxes populate from listbox, then populate tableA.

Thanks for your help.

 

[ponderdj]

Jminn, sorry to jump in on your thread.

 

[THWatson]

ponderdj
I think you should post your issue in a new post. That way we can keep things from getting mixed up here.

Tom

 

[THWatson]

jminn0311
So this is what you are trying to do...
The form calculates a volume for the current record (let's say that calculation is 100). Then it adds that volume to former volume total (let's say that was 400), and you want the text box to show the cumulative value of 500.

I don't know the names of your fields, but say that the control that determines the value for the current record is called txtVolume...and the control that will provide the cumulative total is called txtTotalVolume...see if this works:
On the AfterUpdate event for txtVolume, put

Me.txtTotalVolume = (DLookup"[TotalFieldInQuery]","QueryName") + Me.txtVolume

Let me know.
Tom

 

[THWatson]

...back to Ponderdj's problem

The fact still remains that if you are storing values in TableA the controls in the form have to have their control sources set to the field names from TableA

From what you say, it appears that the controls are populated when you click an item in the list boxes. So try something on the OnClick event for the list boxes. For example...
Me.ControlOne = Me.YourListBoxName.Column(1)

But if you are wanting the result from the first column, remember that Access numbers columns starting at 0, so it might be Me.YourListBoxName.Column(0)

Tom

 

[ponderdj]

yes, I believe that's what I'm looking for Thanks!

 

[jminn0311]

I had tried that already but it won't update the textbox when the calculate button is pressed. If you select a new record for the form then the textbox updates to the new value.

 

[THWatson]

Jeff
Can you post the code for your Calculate button?

Tom

 

[THWatson]

Jeff
The reason I asked you to post the code was to see if something else was happening in the Calculate button.

However, you could also try this as your code for the OnClick event for the Calculate button, or at least include that in the code for the Calculate button, and see if that does it...

DLookup("[TotalFieldInQuery]","QueryName") + Me.txtVolume)

Since that formula works for new records, then I was wondering whether or not that might be the answer.

I am also wondering...would you ever want to use the Calculate button on an old record?

Anyway, if this doesn't do it, please post the code.

Tom

 

[jminn0311]

The code for the calculate button is as follows. The two other textbox controls are calculated in an after event sub. Thanks for all your help on this.

Private Sub Command50_Click()
Dim lbs_gal As Double

Select Case Me.PRODUCT
Case "RP"
lbs_gal = 0.498 * 8.3385
Case "C3"
lbs_gal = 0.499 * 8.3385
Case "IC4"
lbs_gal = 0.563 * 8.3385
Case "IC4/NC4"
lbs_gal = 0.574 * 8.3385
Case "NC4"
lbs_gal = 0.585 * 8.3385
Case "14#"
lbs_gal = 0.649 * 8.3385
Case "Propylene"
lbs_gal = 0.52 * 8.3385
Case "EP"
lbs_gal = 0.386 * 8.3385
End Select

Me.Lbs_Flared = (Me.Volume_Liquid + Me.Volume_Vapor) * (lbs_gal * 42 * 0.01)

End Sub

 

[THWatson]

Jeff
So I assume that what I previously called txtVolume is Me.Lbs_Flared

If so, can you make the next two lines of the code, following the line that begins Me.Lbs_Flared, to be...

Me.TheNameOfYourCumulativeTotalBox = Me.Lbs_Flared + (DLookup("[TotalFieldInQuery]","QueryName"))

Would that do it?

Tom