Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Query results to a form textbox. 1
- Thread starter jminn0311
- Start date
-
1
- #2
- Thread starter
- #3
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
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
- Thread starter
- #5
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
Jeff
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
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
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?
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
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
- Thread starter
- #9
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.
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.
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.
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.
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
Let me know.
Tom
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
Code:
Me.txtTotalVolume = (DLookup"[TotalFieldInQuery]","QueryName") + Me.txtVolumeLet me know.
Tom
...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
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
- Thread starter
- #16
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...
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
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...
Code:
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
- Thread starter
- #19
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
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
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...
Would that do it?
Tom
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...
Code:
Me.TheNameOfYourCumulativeTotalBox = Me.Lbs_Flared + (DLookup("[TotalFieldInQuery]","QueryName"))Would that do it?
Tom
- Status
Similar threads
